Will not filter on criteria

[gpurger]

Hi,
I have one real puzzler for you, (well it is for me)

I have a date field on a query that I sort using the following criteria:

Like "*02/2005" This works.

When I use:
Like "*03/2005" it does not work.

There are data items related to this date. If I use the <=Date() it finds them.

It also works using:
Like "*03*" but not if I use
Like "*03/*"

If I copy and past the date from the actual data it still does not work.

Does anyone have an ideas please

 

[Mile-O]

I have a date field on a query that I sort using the following criteria:

Like "*02/2005" This works.

When I use:
Like "*03/2005" it does not work.:

Very interesting.

The * character in a query is used as a wildcard - which you seem to know. What you don't realise though is that the wildcard is only used for text fields and values. You say, however, that your field is a date field therefore it is not text and you can't use the wildcard character with it as criteria.

What you are best to do is create a calculated field in your query that uses the Format() function on your date field

i.e.

SELECT Format([DateField], "mm/yyyy") AS NewField
FROM MyTable
WHERE Format([DateField], "mm/yyyy") = "03/2005";

Incidentally, when using dates, the correct delimiter is the # and not " (i.e. #01/01/2005#)

 

[Brianwarnock]

I also found this interesting as I have never been able to get the use of Like to fail with dates, I am on 2002 which may explain why the Microsoft knowledge base article KB199163 doesn't apply.
Your problem would be explained for a text field by having a space between the 3 and / but that's not possible in a date field.

"*03*" would bring in any days and months 03 plus year 2003 and "*03/*" just days and months.

Very puzzling

Brian

PS Thought that I had better add that as Microsoft don't recommend the use of Like with Dates then I don't I would have used Between...And... but curiosity did get the better of me one idle day

 

[gpurger]

Thanks for the help.

Am getting to grips with the SELECT function but slowly

TA

Gordon