Microsoft VBScript compilation error '800a0401' in ASP

While running the program, i'm getting this error

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/admin/currentmonth.asp, line 26

strSQL = "SELECT RFCInfo.Name, RFCInfo.Date, RFCInfo.RFCNo, RFCInfo.InvNo, RFCInfo.Deleted, InvoiceDetail.Quantity, InvoiceDetail.Description, InvoiceDetail.ExtendedPrice, InvoiceInfo.GST, InvoiceInfo.PST, InvoiceInfo.Total FROM RFCInfo INNER JOIN (InvoiceDetail INNER JOIN InvoiceInfo ON InvoiceDetail.InvNo = InvoiceInfo.InvNo) ON RFCInfo.InvNo = InvoiceInfo.InvNo where RFCInfo.DealerID =" & 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.

re: Microsoft VBScript compilation error '800a0401' in ASP

Caused by the quotes (") in the datepart function. If you want to use quotes within a string you will have to use double quotes ("")

re: Microsoft VBScript compilation error '800a0401' in ASP

I just tried giving ""m"" and run the program.

strSQL = "SELECT RFCInfo.Name, RFCInfo.Date, RFCInfo.RFCNo, RFCInfo.InvNo, RFCInfo.Deleted, InvoiceDetail.Quantity, InvoiceDetail.Description, InvoiceDetail.ExtendedPrice, InvoiceInfo.GST, InvoiceInfo.PST, InvoiceInfo.Total FROM RFCInfo INNER JOIN (InvoiceDetail INNER JOIN InvoiceInfo ON InvoiceDetail.InvNo = InvoiceInfo.InvNo) ON RFCInfo.InvNo = InvoiceInfo.InvNo where RFCInfo.DealerID =" & strDealerID & " AND RFCInfo.Deleted = FALSE And RFCInfo.Date Between Date() And DateAdd(""m"",-1,Date()) Order By RFCInfo.Date"

and the error occured is

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/admin/last3months.asp, line 132
and line 132 is... Set objRS = objConn.Execute(strSQL)
----------------------------------------------------------------------------------------------------------
Set objRS = objConn.Execute(strSQL)

If not objRS.EOF then
objRS.MoveFirst
currentinvno=objRS(3)
currentrfcno=objRs(2)
Do While Not objRS.EOF

<tr>
Display everything inside table
</tr>



<%
objRS.MoveNext
Loop
%>
<% end if %>

----------------------------------------------------------------------------------------------------------------


and i tried this code too.

strSQL = "SELECT RFCInfo.Name, RFCInfo.Date, RFCInfo.RFCNo, RFCInfo.InvNo, RFCInfo.Deleted, InvoiceDetail.Quantity, InvoiceDetail.Description, InvoiceDetail.ExtendedPrice, InvoiceInfo.GST, InvoiceInfo.PST, InvoiceInfo.Total FROM RFCInfo INNER JOIN (InvoiceDetail INNER JOIN InvoiceInfo ON InvoiceDetail.InvNo = InvoiceInfo.InvNo) ON RFCInfo.InvNo = InvoiceInfo.InvNo where RFCInfo.DealerID =" & strDealerID & " AND RFCInfo.Deleted = FALSE And RFCInfo.Date Between DateAdd('d',-Day(Date())+1,Date()) And DateAdd('m',1,DateAdd('d',-Day(Date())-1,Date())) Order By RFCInfo.Date"


error occurs is

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/admin/currentmonth.asp, line 132

If anyone have any idea what's the problem, just help me. any other way to display current month,last 3 months,6 months,1 yrs records.

Thanks in advance

re: Microsoft VBScript compilation error '800a0401' in ASP

The two functions date() and dateAdd() need to be outside the quotes used to make the query string. Try:
Let me know if this helps.

Jared

re: Microsoft VBScript compilation error '800a0401' in ASP

I just tried this codes now.

strSQL = "SELECT RFCInfo.Name, RFCInfo.Date, RFCInfo.RFCNo, RFCInfo.InvNo, RFCInfo.Deleted, InvoiceDetail.Quantity, InvoiceDetail.Description, InvoiceDetail.ExtendedPrice, InvoiceInfo.GST, InvoiceInfo.PST, InvoiceInfo.Total FROM RFCInfo INNER JOIN (InvoiceDetail INNER JOIN InvoiceInfo ON InvoiceDetail.InvNo = InvoiceInfo.InvNo) ON RFCInfo.InvNo = InvoiceInfo.InvNo where RFCInfo.DealerID =" & strDealerID & " AND RFCInfo.Deleted = FALSE And RFCInfo.Date Between " & Date() & " And " & DateAdd("m",-1,Date()) & " Order By RFCInfo.Date"

Now no error is there, but no records were displaying eventhough records were there in current months in database. The dealer who have current months records were not getting displayed. always the table is blank.

if you have anyidea how to do this or what the error, please help me.

thanks in advance

re: Microsoft VBScript compilation error '800a0401' in ASP

Try to put a single quote (') around each columnname that is of type 'datetime':

Date Between '" & Date() & "' And '" & DateAdd("m",-1,Date()) & "' Order By RFCInfo.Date"

re: Microsoft VBScript compilation error '800a0401' in ASP

I just tried that now.

strSQL = "SELECT RFCInfo.Name, RFCInfo.Date, RFCInfo.RFCNo, RFCInfo.InvNo, RFCInfo.Deleted, InvoiceDetail.Quantity, InvoiceDetail.Description, InvoiceDetail.ExtendedPrice, InvoiceInfo.GST, InvoiceInfo.PST, InvoiceInfo.Total FROM RFCInfo INNER JOIN (InvoiceDetail INNER JOIN InvoiceInfo ON InvoiceDetail.InvNo = InvoiceInfo.InvNo) ON RFCInfo.InvNo = InvoiceInfo.InvNo where RFCInfo.DealerID =" & strDealerID & " AND RFCInfo.Deleted = FALSE And RFCInfo.Date Between '" & Date() & "' And '" & DateAdd("m",-1,Date()) & "' Order By RFCInfo.Date"

And the error occured is

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/admin/currentmonth.asp, line 132

and line 132 is Set objRS = objConn.Execute(strSQL)

If you have anyidea how to do this or what's the error please let me know...

Thanks in advance

re: Microsoft VBScript compilation error '800a0401' in ASP

The problem is that the date could be stored in several formats. I was going to suggest the single quotes too, like Arnold, but I didn't have a lot of hope for it. for the purposes of trouble shooting, add this line immediately after:
compare the output to the date in your db. Are they in the same format?

Jared

re: Microsoft VBScript compilation error '800a0401' in ASP

i tried this and now its comming like

SELECT RFCInfo.Name, RFCInfo.Date, RFCInfo.RFCNo, RFCInfo.InvNo, RFCInfo.Deleted, InvoiceDetail.Quantity, InvoiceDetail.Description, InvoiceDetail.ExtendedPrice, InvoiceInfo.GST, InvoiceInfo.PST, InvoiceInfo.Total FROM RFCInfo INNER JOIN (InvoiceDetail INNER JOIN InvoiceInfo ON InvoiceDetail.InvNo = InvoiceInfo.InvNo) ON RFCInfo.InvNo = InvoiceInfo.InvNo where RFCInfo.DealerID =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.asp, 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

re: Microsoft VBScript compilation error '800a0401' in ASP

OK, take out the single quotes, and change "date()" both times you use it to "now()". Tell me what comes up.

Jared

re: Microsoft VBScript compilation error '800a0401' in ASP

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.

re: Microsoft VBScript compilation error '800a0401' in ASP

Thanks for your help. This Sql statement is working.

strSQL = "SELECT RFCInfo.Name, RFCInfo.Date, RFCInfo.RFCNo, RFCInfo.InvNo, RFCInfo.Deleted, InvoiceDetail.Quantity, InvoiceDetail.Description, InvoiceDetail.ExtendedPrice, InvoiceInfo.GST, InvoiceInfo.PST, InvoiceInfo.Total FROM RFCInfo INNER JOIN (InvoiceDetail INNER JOIN InvoiceInfo ON InvoiceDetail.InvNo = InvoiceInfo.InvNo) ON RFCInfo.InvNo = InvoiceInfo.InvNo where RFCInfo.DealerID =" & strDealerID & " AND RFCInfo.Deleted = FALSE And (RFCInfo.Date >= DATEVALUE('" & DateAdd("m",-1,Date()) & "')) Order By RFCInfo.Date"

And Once again Thanks for the help.

re: Microsoft VBScript compilation error '800a0401' in ASP

And one more help i need right now. i have a feild were datenad time is saved like 10/25/2006 1:28:07 PM . but in table i need to display only date. i need to ommit the time while displaying in table.

how can i seperate the time from date in ASP.

If you have anyidea, please let me know about that.

Thanks in advance.

re: Microsoft VBScript compilation error '800a0401' in ASP

i got it worked by using this.

<td width="60" style="border-style: solid; border-width: 3" height="19"><%=FormatDateTime(strDate, vbShortDate)%>&nbsp;</td>

see this link if you need more information
http://www.ronshardwebapps.com/tips/jantutorialtimedate.asp

Anyway thanks.