dates (1 Viewer)

[k209310]

I have a problem searching dates. I have created a proceedure in excel to search a database using a date inputted by the user.

I can carry out a search using the format(DD/MM/YYYY) however i cannot get the seach to carryout the search format(MM/YYYY).

I think the problem is down to the way the data is set in the database. If in access i set the date field to "DATE" then the search will not work if i set it to "TEXT" it does work

Does anybody have any ideas

 

[Fizzio]

It maybe how you are searching for the date. Post the code snippet where you pass the date inputted value for your search criteria.

 

[k209310]

is this what your after?

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

 

[Fizzio]

Are you inputting the date into a cell in excel or into an input box and can the user type in 1/1/02,1/01/02 and 01/02 etc. If that is so, you need to tell access that is is looking for a date. How you will need to do this is by doing 2 things (I think). Initially you will need to put # around your dates ie

Set rst = Db.OpenRecordset("SELECT * FROM tblESTIMATE WHERE tblEstimate.DATE='#" & txtDate & "#'")
This tells access to use txtDate as a date.

But if the users can type in mm/yy then it will not return any records as your dates are more than likely stored as dd/mm/yy.

Also, I'm not sure if you need to format the date into mm/dd/yyyy as I seem to recall having problems like this myself with dates in code-generated SQL. if so, try this

Set rst = Db.OpenRecordset("SELECT * FROM tblESTIMATE WHERE tblEstimate.DATE='#" & format(txtDate,"mm/dd/yyyy") & "#'")

Hope this helps and can anyone give clarification on that last formatting point?