Microsoft VBScript compilation error '800a0401' in ASP

[remya1000]

While running the program, i'm getting this error

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/admin/currentmonth.as p, line 26

strSQL = "SELECT RFCInfo.Name, RFCInfo.Date, RFCInfo.RFCNo, RFCInfo.InvNo, RFCInfo.Deleted , InvoiceDetail.Q uantity, InvoiceDetail.D escription, InvoiceDetail.E xtendedPrice, InvoiceInfo.GST , InvoiceInfo.PST , InvoiceInfo.Tot al FROM RFCInfo INNER JOIN (InvoiceDetail INNER JOIN InvoiceInfo ON InvoiceDetail.I nvNo = InvoiceInfo.Inv No) ON RFCInfo.InvNo = InvoiceInfo.Inv No where RFCInfo.DealerI D =" & strDealerID & " AND RFCInfo.Deleted = FALSE And RFCInfo.Date Between Date() And DateAdd("m",-1,Date()) Order By RFCInfo.Date"
---------------^

i need to display last one months records,3 months records, 6 month records and 1 yrs records seperatly. now i'm tring to display 1 month records. but while running this codes, in this sql statement under the "m" (Between Date() And DateAdd("m",-1,Date()) ) error occurs.

and i'm using ASP with Ms Access as database.

anyone have any idea why this error occurs. is this the way we can display the last 1 month records... like wise if we change the -1 to -3,-6 and all the last 3 months, last 6 months will be found out right.

Thanks in advance.

[Arnold Schuur]

i tried this and now its comming like

SELECT RFCInfo.Name, RFCInfo.Date, RFCInfo.RFCNo, RFCInfo.InvNo, RFCInfo.Deleted , InvoiceDetail.Q uantity, InvoiceDetail.D escription, InvoiceDetail.E xtendedPrice, InvoiceInfo.GST , InvoiceInfo.PST , InvoiceInfo.Tot al FROM RFCInfo INNER JOIN (InvoiceDetail INNER JOIN InvoiceInfo ON InvoiceDetail.I nvNo = InvoiceInfo.Inv No) ON RFCInfo.InvNo = InvoiceInfo.Inv No where RFCInfo.DealerI D =17 AND RFCInfo.Deleted = FALSE And RFCInfo.Date Between '4/24/2007' And '3/24/2007' Order By RFCInfo.Date

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/admin/currentmonth.as p, line 134

and in database the date is saved as 3/24/2007 12:16:21 PM format

so how can i check the date here. in sql only the date is comparing. so please help me if you have any idea.

and thanks for your help....

and thanks in advance

Ah, you are using MS Access as database. In SQL Server or Oracle you would have to use the single quote but when using datetime fields in MS Access, you have to put a # around the date. For example:
Between #4/24/2007# And #3/24/2007# Order By RFCInfo.D

Another cause could be that the datetime format in your query (DD/MM/YYYY) does not match with your local settings. In that case try MM/DD/YYYY or YYYY-MM-DD.

[remya1000]

Thanks for your help. This Sql statement is working.

strSQL = "SELECT RFCInfo.Name, RFCInfo.Date, RFCInfo.RFCNo, RFCInfo.InvNo, RFCInfo.Deleted , InvoiceDetail.Q uantity, InvoiceDetail.D escription, InvoiceDetail.E xtendedPrice, InvoiceInfo.GST , InvoiceInfo.PST , InvoiceInfo.Tot al FROM RFCInfo INNER JOIN (InvoiceDetail INNER JOIN InvoiceInfo ON InvoiceDetail.I nvNo = InvoiceInfo.Inv No) ON RFCInfo.InvNo = InvoiceInfo.Inv No where RFCInfo.DealerI D =" & strDealerID & " AND RFCInfo.Deleted = FALSE And (RFCInfo.Date >= DATEVALUE('" & DateAdd("m",-1,Date()) & "')) Order By RFCInfo.Date"

And Once again Thanks for the help.