asked on

Using dates in SELECT with Microsoft Access 97

I am very new to dreamweaver MX. Developing a small system using Microsoft Access 97 as the database. I created a search form and I am passing search paramters to an action page to perform the search. Below is the select statement in my action page that I am using to perform the search.

SELECT * FROM tblContent WHERE Author_ID = #FORM.cboAuthor_ID# AND tblContent.Content_Date
= '#FORM.txtDate#' ORDER BY Content_Date ASC

I am receiving the below error message when the action page runs. I feel that the problem has something to do with the way the date is being handled.

[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

This is the SQL statement being generated.

SELECT * FROM tblContent WHERE Author_ID = 3 AND tblContent.Content_Date = '01/01/2003' ORDER BY Content_Date ASC

sevenfoot

if you look you are missing your #'s on each side of the generated SQL statement.

sevenfoot

You also don't need the quotation marks around you date. SELECT * FROM tblContent WHERE Author_ID = #FORM.cboAuthor_ID# AND tblContent.Content_Date
= #FORM.txtDate# ORDER BY Content_Date ASC

sevenfoot

You also don't need the quotation marks around your date. SELECT * FROM tblContent WHERE Author_ID = #FORM.cboAuthor_ID# AND tblContent.Content_Date
= #FORM.txtDate# ORDER BY Content_Date ASC

jsprenk55

ASKER

I used the select as shown in your last comment and I am still receiving an error message.

Error Executing Database Query.
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I changed the CFML code as shown below:

SELECT * FROM tblContent WHERE Author_ID = #FORM.cboAuthor_ID# AND tblContent.Content_Date
= #FORM.txtDate# ORDER BY Content_Date ASC

Generated SQL statement

SELECT * FROM tblContent WHERE Author_ID = 3 AND tblContent.Content_Date = 01/01/2003 ORDER BY Content_Date ASC

I'm suspecting that the date needs #'s around it in the generated select statement, but I am not sure how to code this. You had mentioned #'s in one of your comments.

Your continued help is greatly appreciated.

Thanks

jsprenk55

ASKER

Thanks for your suggestion, but this is what finally worked for me.

SELECT * FROM tblContent WHERE Author_ID = #FORM.cboAuthor_ID# AND tblContent.Content_Date
= ###FORM.txtDate### ORDER BY Content_Date ASC

Notice I added two #'s on each side of #FORM.txtDate#

Thanks for your help.

zenlion420

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ with points refunded

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

zenlion420
EE Page Editor

ASKER CERTIFIED SOLUTION

GhostMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial