| Author |
 Topic  |
|
kolucoms6
Average Member
845 Posts |
 Posted - 07 May 2007 : 05:02:02
|
I have a field in my Access database as AddDate and Text type.
Below line give me an error :
Code :
RS.Filter="DateDiff(d, CDate(Date), CDate(RS(AddDate))) <= 1"
Error :
error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Any help ?
|
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
|
|
kolucoms6
Average Member
845 Posts |
Posted - 07 May 2007 : 06:21:05
|
"SELECT SCat_ID,Sponsored,Approved,[Hit],SCat_CommisionType,SCat_PayFreq,AddDate FROM SubCategory"
I also tried
RS.Filter="DateDiff(d, formatdatetime(CDate(AddDate),2),CDate(Now))<="
but same error. |
Edited by - kolucoms6 on 07 May 2007 08:41:08 |
 |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 07 May 2007 : 07:59:44
|
RS.Filter="DateDiff(d, CDate(Date), CDate(RS(AddDate))) <= 1"
Imbalanced parentheses?
|
|
|
|
kolucoms6
Average Member
845 Posts |
Posted - 07 May 2007 : 08:17:20
|
RS.Filter="DateDiff(d, CDate(Date), CDate(RS(AddDate))) <= 1"
Four open and four Close ...
I even Tried
RS.Filter="DateDiff(""d"", CDate(Date), CDate(RS(AddDate))) <= 1"
but same error.
Also,
SELECT SCat_ID,Sponsored,Approved,[Hit],SCat_CommisionType,SCat_PayFreq,AddDate FROM SubCategory where DateDiff("d", formatdatetime(CDate(AddDate),2),formatdatetime(CDate(Now),2))<=1
Works PERFECT But not with Filter property.
|
Edited by - kolucoms6 on 07 May 2007 08:51:50 |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 07 May 2007 : 09:04:28
|
Maybe re-work the expression?
RS.Filter = "AddDate BETWEEN #" & CDate(Date) & "# AND #" & DateAdd("d",1,CDate(Date)) & "#"
(A little off the cuff, so there may be a syntax error or two but I think you get the idea) |
|
|
|
kolucoms6
Average Member
845 Posts |
Posted - 07 May 2007 : 09:40:51
|
AddDate is a TEXT field, not datetime
Also,
DateFilterQry="formatdatetime(CDATE(AddDate),2) BETWEEN '" & formatdatetime(CDate(Date),2) & "' AND '" & formatdatetime(DateAdd("d",1,CDate(Date)),2) & "'"
response.write DateFilterQry
Gives :
formatdatetime(CDATE(AddDate),2) BETWEEN '5/7/2007' AND '5/8/2007'
But gets Error as
ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/Right.inc, line 33
Line 33 is
RS.Filter=DateFilterQry |
Edited by - kolucoms6 on 07 May 2007 09:45:32 |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 07 May 2007 : 11:18:00
|
quote:
Originally posted by kolucoms6
AddDate is a TEXT field, not datetime
Well, that would make a difference now wouldn't it. 
You're probably better off trying to type cast it as a date just for the query. I think there's a way to do it, but I'm not 100% certain. I'll have to dig around in one of my books. |
|
|
|
kolucoms6
Average Member
845 Posts |
Posted - 07 May 2007 : 11:58:10
|
Or my way of doing it is WRONG ? |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 07 May 2007 : 12:13:19
|
Maybe give this a try:
RS.Filter = "CAST(AddDate AS DATETIME) BETWEEN #" & CDate(Date) & "# AND #" & DateAdd("d",1,CDate(Date)) & "#"
You may or may not need the "#" around your dates, it depends on the database engine you're using.... also you may need to set the format before you can use CAST().
Basically, you need to give it a field, and you need to make sure that you're working with the same data types across the expression. Since you are trying to work with it as a DATE, it would be better to make all your variables DATEs.
Of course, I could be wrong too.  Databases and SQL aren't exactly my strong points.... yet. |
|
|
|
kolucoms6
Average Member
845 Posts |
Posted - 07 May 2007 : 12:37:05
|
Sorry, but this line also gives me an error :
ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/Right.inc, line 36
Line 36 :
RS.Filter = "CAST(AddDate AS DATETIME) BETWEEN #" & CDate(Date) & "# AND #" & DateAdd("d",1,CDate(Date)) & "#"
Would you like to have a look at the asp file ?
|
Edited by - kolucoms6 on 07 May 2007 12:38:09 |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 07 May 2007 : 12:58:14
|
What database type are you using? SQL Server? Access? MySQL?
As for the file, I think just the SQL statement you're using to populate the recordset would be enough. |
|
|
|
kolucoms6
Average Member
845 Posts |
Posted - 07 May 2007 : 13:18:34
|
MS-ACCESS.
Here is the Code from the File :
1) sql = "SELECT SCat_ID,Sponsored,Approved,[Hit],SCat_CommisionType,SCat_PayFreq,AddDate FROM SubCategory "
2) RS.Open sql, session("cn"),3,3
3)RS.Filter = "CAST(AddDate AS DATETIME) BETWEEN #" & CDate(Date) & "# AND #" & DateAdd("d",1,CDate(Date)) & "#"
4)ListingCount24h=RS.Recordcount
Line 3 is really causing a pain for me. :-( |
Edited by - kolucoms6 on 07 May 2007 13:27:02 |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 07 May 2007 : 13:36:49
|
| Hmmm. It seems Access doesn't support CAST... let me see if I can find an alternate function. |
|
|
|
kolucoms6
Average Member
845 Posts |
Posted - 07 May 2007 : 16:04:38
|
SELECT * FROM SubCategory where DateDiff("d", formatdatetime(CDate(AddDate),2),formatdatetime(CDate(Now),2))<=1
Works PERFECT But not with Filter property.
Any Specific reason for not working ?
Also, Why CDate doesnt work ?Any idea ? |
Edited by - kolucoms6 on 07 May 2007 16:10:33 |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 07 May 2007 : 16:20:31
|
| If you're getting the results in a single query, I'd stick with it. Honestly I don't use the filters. I try to get just what I need out of the initial query. |
|
|
|
Topic |
|
Topic Locked
