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.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.

[Arnold Schuur]

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

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"

[remya1000]

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

[jhardman]

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"
---------------^

The two functions date() and dateAdd() need to be outside the quotes used to make the query string. Try:

..." AND RFCInfo.Deleted = FALSE And RFCInfo.Date Between " & Date() & " And " & DateAdd("m",-1,Date()) & " Order By  RFCInfo.Date"

Let me know if this helps.

Jared

[remya1000]

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

[Arnold Schuur]

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"

[remya1000]

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

[jhardman]

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:

response.write vbNewLine & strSQL & "<br>" & vbNewLine

compare the output to the date in your db. Are they in the same format?

Jared

[remya1000]

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

[jhardman]

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

Jared

[Arnold Schuur]

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

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.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.