Criteria not working for dates

[MICHELE]

I have made a query that one of my criteria is a date field. When I run my query that criteria doesn't work right. If I put "Is Not Null" it gets all of them, but if I put a specific date as the criteria or Now() then it pulls up nothing when I know that date is there.

That field's date is not actually entered manually. I have code setup to update the date in the field when another field is changed in a form. So I went into the table to this date field and manually reentered some of them using the same date and these work using my querie's date criteria! What's up with that?

Does that date that is entered by VBA have some kind of different property than a manually entered date? How can I get around this so that I can use it as criteria in my query? That was the whole purpose of making this date field.

 

[e2cheng]

idea?

This is only an idea, I may be wrong as I've only been at this for a short while.

from playing around on VB, I think that the Now() function returns a short date + time stamp. so, if you're looking for a specific date with a date + time stamp you won't find it. unless you search at the right second -literally

do get around a different date input, although i think that it is the same, i used an input mask for one of my applications.

i hope that helps.

 

[MICHELE]

Do you mean setting up the field with a mask at the table?

 

[Rich]

It's either Date() as the criteria, or you need to enclose specific dates ie. #01/01/03#

 

[Bert666]

Date Problem

I have had this problem,

If you are entering the criteria in dd/mm/yy format it won't work - you will have to use the mm/dd/yy format.

Bert.

 

[neileg]

Bert666

You seem to have a date format problem. Access inherits its date format from the PC. So if you have a US format, mm/dd/yy on your PC this is how dates are interpreted in the database. If you have routines in the coding that are based on a different format, then things can go wrong. Where you have a multi-user db with users on different settings, things can go wrong.

I've experienced both of these problesm before. In theory, it shouldn't cause a problem, but in practice it does. We find that occasionally, PCs on our network will swap from region specific settings (UK in our case) to the default for the language (US in our case). Why this happens is not clear, but the first thing I check when Access date problems arise is the regional settings in Windows Control Panel.

 

[Bert666]

Date Problems

Well you are right - that should happen - but in practice it does not i have tried this on 3 separate computers, each having their system dates set to dd/mm/yy format, but when i pass a criteria in that format nothing happens - Yet if i pass a criteria in mm/dd/yy format even though the system date is in the former format - it works like a charm.

Funny isn't it.

Bert.

 

[MICHELE]

I will try these things. Thank you very much!

Michele