Problem with date format in Access 2007

Question

Dear Team,

We migrated from Windows XP to Windows 7. We had Office 2007 before and after migration too. We have the following issue: a query that was created before in the same Access version does not function properly after migration. Without any change in it, it runs without problem, but if the criteria field is changed, which contains an interval (specified using dates), it gives an error message: "The expression you entered has an invalid date value."

I tried to search all forums, but I did not find any clues.

Plese help.

Answer 1

I don't know that this is a problem, but I would change

Between #2010. 10. 17.# And #2010. 10. 31.#

to Remove the spaces and definitely remover the trailing period.

Between #2010.10.17# And #2010.10.31#

Or even better to

Between #2010-10-17# And #2010-10-31#

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County

Answer 2

Hi

MAKE A COPY OF YOUR DATABASE AND WORK ON THAT

Try this - just to see if this is the problem (date form on the import)

SELECT DateSerial(Left([TableName]![DateField],4),Mid([TableName]![DateField],6,2),Right([TableName]![DateField],2)) AS DateFormated

FROM TableName;

Of course this will not update and it's just so you can look at the data and see if it's what you're looking for

If this is the problem (you never know) then you can add a sub query to the import to convert that date for you and that should fix things

Answer 3

QUERY

The query is ran on table on an SQL database. One of the fields, which is a date is filtered in the query with the following expression:

Between #2010. 10. 17.# And #2010. 10. 31.#

If it is left left unchanged, the query runs perfectly. If it is changed on a computer that runs XP, then saved, opened on Win 7, it runs perrfectly. If you change it on a computer that has Win 7 on it, then it crashes with the error message mentioned above.

We tried every date format in the query, if you entered it, it converted it to this format and then displayed the error message.

SHORT DATE FORMAT

The Short Date format in the Regional Settings is yyyy.MM.dd

Answer 4

In addition to Wayne's request for the SQL of the query, what are your regional settings (specifically is your Short Date format something other than mm/dd/yyyy), and what dates are you entering when you get the error?

---

Doug Steele, Microsoft Access MVP

http://www.AccessMVP.com/djsteele (no e-mails, please!)

Answer 5

Can you post the sql of the query so people can have a look at it