Access 2007 and Dates

By Wolflingg ·
We recently got a new set of servers and upgraded our Office 2003 to 2007. I have a proprietary database written in Access that is used to keep track of clients. One of the reports that used to run correctly in Access 2003 calculates when a client needs a 90 day update. It's based on the last update date in their record. When I check to see if the last update date + 90 days is less than today's date + 30 days (to get the list a little shorter), it doesn't calculate out correctly any more. I was wondering if they did something to change the way dates are handled in Access 2007? The Update date is in a query and I had the expression <= Date()+30 to check it. I get some of the records, and miss a whole lot more and am not sure why this has changed. Any help is appreciated. Thanks.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

One more thing...

by Wolflingg In reply to Access 2007 and Dates

I found a post of someone having a similar problem and they were told to take off the double quotes that Access puts around the date in the query expression (apparently it's looking at it as a text field now even though it's a date data type) and I can't get it to stop doing that. I try to change the properties of the column in the query to make it short date, but it won't allow it. Is there some other place to look to make the query expression a date data type? It looks like it's only comparing the first number in the date field which is why it's not coming up with all the right records. eg. 1/28/2011 is showing as a date that's less than 11/7/2010. Thanks.

Collapse -

Figured it out

by Wolflingg In reply to Access 2007 and Dates

Just wanted to post to say I figured out what the problem was. In a previous query to the one I was comparing dates to, I was checking to see if a date actually existed. If it did, it put it into the "UpdateDate" field. If it didn't, it put "Unknown". When that was checked against another date field, it would have been replaced with the field that had a valid date in it. Apparently Access doesn't like text in the comparison and automatically converts the query column to a Text data type and will not allow you to change it even though the former fields were of Date data type. Once I got rid of the "Unknown" designation in the former query, it worked. Just wanted to let anyone else who comes across this to know in case they have a similar problem--if you have a query based on another query that may be where the problem lies.

Related Discussions

Related Forums