Criteria problem

[rbrule]

Hello, I have a query that I did not create, but I am now responsible for. My problem is that when I type a date into the criteria field to call up records I get no records. I know the records are there, because they come up when I take the date out of the criteria field, and open all records. I have typed them exactly as they have been entered with no results. This query is on a network and other staff in the area type the criteria the same way I do and get results. I am sure the problem must be with a setting on my computer but I don't know what it could be. Has anyone else had this problem?

 

[pdx_man]

I'm guessing the data in your date field also contain the time. Therefore, if you have a criteria of 07/10/06, it will not return a record of 07/10/06 09:39.34, as it is not an exact match. You can get these records by doing one of two things. Create a calculated field where you lop off the time and place your criteria on it, or change your criteria for the entire day:

>= [YourCriteriaDate] and < DateAdd('d',1,[YourCriteriaDate])

 

[DGagnon819]

Also ensure that the date format that your database uses and your computer date format is the same. For example, if the short date format for your database is dd/mm/yyyy then it needs to be the same for your computer system settings. This will negatively impact your queries if they aren't.

In a Windows based system you will find the settings in 'Control Panel'. One way to quickly diagnose the problem would be to compare date settings with a computer known to have no problems with the queries.

Good luck,
D.Gagnon

 

[rbrule]

Thank you for your replies. We did what you both suggested, I reset my date format and created a calculated field to isolate only the date. However I still had the problem. It turns out that the date field was set as text instead of date and time. I was able to call up the data by adding either quotation marks or pound signs to the criteria, it normally does this automatically, which was messing me up. I guess it was because access was confused because of the data type that was set. In case you are wondering why a date was set as text, it is because the database was created using "Teleforms", a software package that creates scannable forms and a database for the data to go to. Apparently the person who created the form, used the wrong text box setting. Thanks again.