Date issue

[FlyGuyTray]

having an issue which is making me feel very incompetent.. My table has a creation date field (field type set to date/time).. I'm building a query that will show a count of how many records where created before a certain date. So in my query of course i put the CreationDate field and its set to: "where" "< #10/26/2010# ".. assuming that means where the creation date is less than 10/26/2010.. i get a Data type mismatch in criteria expression error.. but i dont understand why..

 

[boblarson]

Don't include the "where" part.

 

[FlyGuyTray]

Don't include the "where" part.

Still a no go... i attached a pic of what i have..

 

[boblarson]

Still a no go... i attached a pic of what i have..

I didn't see the quotes around your "< #10/26/2010# " at first. You don't use quotes with a date.

Simply:

< #10/26/2010#

 

[FlyGuyTray]

Unfortunately access keeps adding those quotes and i have no idea why!!!!!!

 

[boblarson]

Unfortunately access keeps adding those quotes and i have no idea why!!!!!!

That would usually indicate that Access thinks your field is a text field and not a date. Can you post a screenshot (just upload it here) that shows the query in design view and the table in design view with that field selected in the table design view so its properties can show?

Or just upload a copy of the database with bogus data.

 

[FlyGuyTray]

That would usually indicate that Access thinks your field is a text field and not a date. Can you post a screenshot (just upload it here) that shows the query in design view and the table in design view with that field selected in the table design view so its properties can show?

Or just upload a copy of the database with bogus data.

dont have enough posts.. yet lol

 

[boblarson]

I don't see any reason why it should put quotes around it then. Discard that query and try again with a new one.

 

[FlyGuyTray]

Thanks Bob! New query worked instantly..

 

[FlyGuyTray]

But its not giving me the right information! arrrgh!

 

[boblarson]

But its not giving me the right information! arrrgh!

What should it be giving you? Right now, if you are using

< #10/26/2010#

as criteria, it should be giving you all dates BEFORE that date and NOT INCLUDING that date.

 

[FlyGuyTray]

its returning every record.. im so confused..

 

[boblarson]

its returning every record.. im so confused..

So, even those AFTER or EQUAL to 10/26/2010? Hmmm, mysterious.

 

[FlyGuyTray]

i figured it out.. smh.. long day.. it was returning the right data.. just forgot to throw count back in there... Thanks for all your help..

 

[boblarson]

Glad to hear.