Stupid Query!

[492JQS]

I have a simple query that I use to list Engineering Changes raised in any particular month, and until last month it was working fine. When I try to list changes raised in March, however, the query returns no results, yet I know a large number of changes have been raised.
The query contains two fields, the Change Number and Date Raised (both from the same source Table). In the 'Criteria' for Date Raised I enter something like "Like */02/2005" and run it, giving all the Changes raised in February 05. Last month I entered "Like */03/2005" and got nothing. I even tried entering a 'Dummy' Change with an earlier date (in February), entered the Criteria "Like */02/2005" and the 'Dummy' didn't appear in the results, so it seems that anything I enter just isn't picked up, regardless of the date entered.

Anyone got any ideas?

 

[ColinEssex]

Well, using a short date format (dd/mm/yy)

 Like "*/02/05"

works ok.

Col

 

[Brianwarnock]

quite correct Col no matter what format the date is to use the like operator you must code the Like string in short date format, microsoft amongst many others state that you should not use like on date fields but as long as I follow the above rule I cannot get it to fail not even when examining KB199163

Brian

 

[ColinEssex]

Hi Brian, the other method is to use the DatePart as a field expression and then have [Enter month number] as the criteria in the query.

DatePart("m",[YourDateField])

Col

 

[Brianwarnock]

Yes that is the recommended way of handling date fields ie use date functions

If Microsoft say don't use Like then one day it may not work, I only examined it as a challenge one slow day

Brian

 

[ColinEssex]

the other fly in the ointment is the fact that using the DatePart, the query will bring up all dates in the selected month irrespective of the year.

The method I would prefer is the old standby

Between [Enter Start Date] and [Enter End Date]

then you can have any range you want and any year you want.

(it must be a slow day for me too Brian - time for a cuppa now )

Col

 

[Brianwarnock]

Milk no sugar please, hey we are starting to hijack threads in the tech area SJ will ban us
But to be serious that old standby is precisely what the KB article recommended.

Brian

 

[ColinEssex]

Sorry Mile-O

Yes Brian, I didn't want to leave that option out.

Col