By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,496 Members | 1,517 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,496 IT Pros & Developers. It's quick & easy.

Microsoft VBScript compilation error '800a0401' in ASP

100+
P: 115
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.
Apr 23 '07 #1
Share this Question
Share on Google+
11 Replies


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

Expand|Select|Wrap|Line Numbers
  1. 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"
Apr 23 '07 #2

100+
P: 115
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
Apr 23 '07 #3

jhardman
Expert 2.5K+
P: 3,405

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:
Expand|Select|Wrap|Line Numbers
  1. ..." 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
Apr 23 '07 #4

100+
P: 115
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
Apr 24 '07 #5

P: 36
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"
Apr 24 '07 #6

100+
P: 115
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
Apr 24 '07 #7

jhardman
Expert 2.5K+
P: 3,405
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:
Expand|Select|Wrap|Line Numbers
  1. response.write vbNewLine & strSQL & "<br>" & vbNewLine
compare the output to the date in your db. Are they in the same format?

Jared
Apr 24 '07 #8

100+
P: 115
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
Apr 24 '07 #9

jhardman
Expert 2.5K+
P: 3,405
OK, take out the single quotes, and change "date()" both times you use it to "now()". Tell me what comes up.

Jared
Apr 24 '07 #10

P: 36
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.
Apr 24 '07 #11

100+
P: 115
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.
Apr 25 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.