Dates

[TashaSpicer]

I have a query where one of the searchable fields is the date. The issue I am having is that if the date is not entered in the query exactly as it was entered in the table, it won't pull back the information. Is there a way to set up the criteria in the query to accept the date in any format?

Ex.

Date is entered in table as 6/5/10

Search is entered as 06/05/2010

Thanks!

 

[pbaldy]

It sounds like your field is Text rather than Date/Time. If so, I would change it to a proper Date/Time field. Failing that, you can probably use CDate() to force the issue.

 

[MSAccessRookie]

I have a query where one of the searchable fields is the date. The issue I am having is that if the date is not entered in the query exactly as it was entered in the table, it won't pull back the information. Is there a way to set up the criteria in the query to accept the date in any format?

Ex.

Date is entered in table as 6/5/10

Search is entered as 06/05/2010

Thanks!

There appears to be a little information missing, and there are two questions that I need an answer to before I can be sure how to help you.

1. What is the data type of the Column that is being searched?
2. What is the data type of the Field where you are entering the data?

I suspect that they are not both Date type, since the example that you provide should create matching dates if they were. If it turns out that either (or both) of the items is of Text type, then since both examples are properly formatted as dates, you should be able to use the cDate() Function to convert any Text type to Date type, so that they can be compared properly.

Get back to us if there are any further questions.

Note:

I see that pbaldy beat me to the post with a similar answer. Two votes means that you now have a good place to begin looking for your solution.

 

[TashaSpicer]

I double checked the table and it is set as a Date/Time field.

This is the criteria I currently have in place:

Like [Enter Date:]

I have tried a couple of different things and can't get it to work properly!

 

[MSAccessRookie]

I double checked the table and it is set as a Date/Time field.

This is the criteria I currently have in place:

Like [Enter Date:]

I have tried a couple of different things and can't get it to work properly!

The Entry data is of type Text, and by default, your Column in the table will be changed to Text so that it can be compared to the Entry. You should be able to override this by using the cDate() Function.

Like cDate([Enter Date:])
​