Syntax error (1 Viewer)

[k209310]

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?

 

[Fizzio]

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.

 

[Rich]

You also need to format date in strSQL using the US format to return the correct results

 

[k209310]

thanks for the replies. Could someone please expand on the strSQL format Date part please. Im not too sure what this means.

 

[Rich]

Something like
& "And [Issued]= #" & Format(Me!List23.Column(1), "mm dd yyyy") & "#"

 

[k209310]

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

 

[Fizzio]

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.

 

[Pat Hartman]

Text format dates need to be surrounded by #'s. Your post shows some stray single quotes also. Remove them.