Syntax error

k209310

Registered User.
Local time
Today, 15:19
Joined
Aug 14, 2002
Messages
185
I have this line of code that is part of a search function. It searches dates in the database

Set rst = Db.OpenRecordset("SELECT * FROM tblESTIMATE WHERE tblEstimate.DATE='#" & txtDate & "'#")

whene the ode is run i get a syntax error telling me that i have a syntax errror in the query expression. Does any body have any idea why? Am i right in assuming the # let VB know that it is seaching for a date?
 
The first thing to do is change the name of your field from DATE. Date is a function in Access so if you use this as a fieldname, Access can get a bit grouchy. Change it to something like EstimateDate instead and see what that does.
 
You also need to format date in strSQL using the US format to return the correct results
 
thanks for the replies. Could someone please expand on the strSQL format Date part please. Im not too sure what this means.
 
Something like
& "And [Issued]= #" & Format(Me!List23.Column(1), "mm dd yyyy") & "#"
 
thanks for that.

Does I now have a date search for the full date (dd/MM/YYYY) is there anyway way to maipulte the search to search for MM/YYYY even if the data is stored in the database as DD/MM/YYYY
 
I sort of addressed this here

http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=126456#post126456

but it really depends how you want to search for the date. If you want to search for multiple formats of date ie 1/1/02, 01/01/02, 01/2002 then you will need to use code to create the correct SQL string. You can search for mm/yyyy simply by changing the format of the sql statement to

Set rst = Db.OpenRecordset("SELECT * FROM tblESTIMATE WHERE tblEstimate.EstimateDate='#" & Format(txtDate,"mm/yyyy") & "#'") but I'm not sure what results it would throw up.
 
Text format dates need to be surrounded by #'s. Your post shows some stray single quotes also. Remove them.
 

Users who are viewing this thread

Back
Top Bottom