Between Date()-90 And Date() Query Help

[Gaffi1]

I have a query that pulls all employee attendance occurrences during the last 90 days. for the criteria in the query, I utilize Between Date()-90 And Date(). This worked all last year, and now that we have gone into the new year, it doesn't.

For example, I have an individual who I gave an occurrence to, today. So, the DateOccur field recorded in shortdate format 01/22/2005. I go and pull the report, and it can see all her occurences during november and december, but nothing from this month. I tried converting everything to long date, but it made no difference. Tried using Now() instead of Date() and it made no difference.

Unfortunatly, at this point in time I am not in a position to make any design overhauls to bypass this problem with code, so hoping someone knows a different criteria expression that might fix this. Thanks!

PS, I doublechecked the dates to make sure I wasn't crazy.

 

[GingerR]

just troubleshooting: if you run the query and substitute Date() for the criteria instead of the Between.... criteria, does it return this one new record?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Gaffi1]

Wow, no I don't. What the heck would cause that and how do I fix it?

 

[GingerR]

is your date field TEXT or a DATE (I don't mean "FORMAT", I mean data type)? Look in table design to the right of the Date field.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Gaffi1]

I just double checked and it is in date format. Here's hoping you got a few more ideas cause my bag of tricks is empty, lol.

 

[GingerR]

What if you put in 1/22/04 as the criteria? does that work? are you sure there's a date in the table of 1/22/04?

if still a problem please post your sql here.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Gaffi1]

OMG, i can't believe the problem I have! In a massive overhaul we did at the beginning of the year, we changed the linkchild/master properties from Employee name to Id number. So, what we were getting was empty fields of data in the table, outside of the date criteria. I'm working on fixing it now and running some update queries to repopulate the data. Thanks for your help Ginger!