arendt73
asked on
Need to show full results from a query
I have a data type field (date/time) "SubDate" that contains information in the following format mm/dd/yyyy time or for example: 5/7/2008 1:42:33 PM. My search page pulls out the years (yyyy) from the SubDate field.
Now I want to pull up all data that contains the specific year selected in the search page. For example, if I selected 2010, then the results page will display all of SubDate's data that contain the year 2010 in it.
As of right now, here is my Select statement:
SELECT * FROM district WHERE SubDate = #"&Request.QueryString("Su bDateYear" )&"# ORDER BY SubDate DESC
And I receive the following error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'SubDate = ##'.
Obviously, it is looking only for the year when the field contains 5/7/2008 1:42:33 PM in it.
Any suggestions or recommendations is greatly appreciated.
Thank you.
Now I want to pull up all data that contains the specific year selected in the search page. For example, if I selected 2010, then the results page will display all of SubDate's data that contain the year 2010 in it.
As of right now, here is my Select statement:
SELECT * FROM district WHERE SubDate = #"&Request.QueryString("Su
And I receive the following error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'SubDate = ##'.
Obviously, it is looking only for the year when the field contains 5/7/2008 1:42:33 PM in it.
Any suggestions or recommendations is greatly appreciated.
Thank you.
ASKER
I get the following error when I applied the suggestion:
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'Year(SubDate) = ##'.
/cmecf/review=ecf_district _past.asp, line 14
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'Year(SubDate) = ##'.
/cmecf/review=ecf_district
Try this:
"SELECT * FROM district WHERE DATEPART(year, 'SubDate') = #" & Request.QueryString("SubDateYear") & "# ORDER BY SubDate DESC"
ASKER
The second recommendation was applied and I gt the following error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'DATEPART(year, 'SubDate') = ##'.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'DATEPART(year, 'SubDate') = ##'.
Then try this:
"SELECT * FROM district WHERE DATEPART(year, SubDate) = '" & Request.QueryString("SubDateYear") & "' ORDER BY SubDate DESC"
ASKER
Sorry again. I get the following error:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/cmecf/review=ecf_district _past.asp, line 14
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/cmecf/review=ecf_district
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My apologizes, but I receive this error message when I applied the last recommendation.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'year(SubDate) ='.
The search page contain the POST method so I changed Request.QueryString to Request.Form. Regardless, I continue to get errors. And yes, the information resides in Access.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'year(SubDate) ='.
The search page contain the POST method so I changed Request.QueryString to Request.Form. Regardless, I continue to get errors. And yes, the information resides in Access.
ASKER
I ended up using
"SELECT * FROM district WHERE Year(SubDate) = '"&Request.Form("SubYearDa te")&"' ORDER BY SubDate DESC"
Which did the trick and gave me the results I looked for. Thank you.
"SELECT * FROM district WHERE Year(SubDate) = '"&Request.Form("SubYearDa
Which did the trick and gave me the results I looked for. Thank you.
hi,
while it can be done with Access as back-end for small sites when the data access is carefully designed, I would really recommend that you use at least SQL Server 2008 R2 Express as database back-end.
mfG
--> stefan <--
while it can be done with Access as back-end for small sites when the data access is carefully designed, I would really recommend that you use at least SQL Server 2008 R2 Express as database back-end.
mfG
--> stefan <--
ASKER
Used someone else's assistance.
ASKER
Used some of suggestions to resolve my issue.
Open in new window