Now() format different on different computers

[shaggy]

I've written a query that compares a date field in a database to the current date. It works fine on one computer but not on another. The problem is that one computer returns the date with a 4 digit year and the other 2.

The date field in the database is YYYYMMDD

I've written this to convert the Now() value to the same format.
Date: Val(Mid(Now(),7,4) & Left(Now(),2) & Mid(Now(),4,2))
This works on computer 1.

Now() Returns:
Computer 1 - Windows 2000 + Access 2000
12/26/2003 2:11:51 PM

Computer 2 - Windows NT4 + Access 2000
12/26/03 2:11:51 PM

Is there any way to change the date format returned so I don't have to have a query for each machine?

If I can't change the date format, is there a way to write the query to work on both machines?

Does this have anything to do with the OS since both machines are running the same version of Access?

 

[WayneRyan]

Shaggy,

From the Help files:

"Microsoft Access can display a wide range of international formats. To help ensure consistency among applications, Microsoft Access uses the regional settings specified by double-clicking Regional Settings in the Windows Control Panel for the predefined Number and Date/Time formats listed in the following table."

It is dependant upon the particular machine. There might be
an API call through which you could set it via software.

The dates are stored internally the same. This just affects
the way they are displayed. You could use the Format
function to control that in a consistent manner.

There are examples in Help and in the Search facility here.

Wayne

 

[shaggy]

Thanks, that worked. I thought it was probably easy. I looked under Date/Time in the Control Panel, but didn't think about the Regional Settings.

 

[Jon K]

I've written a query that compares a date field in a database to the current date......

The date field in the database is YYYYMMDD

I've written this to convert the Now() value to the same format.
Date: Val(Mid(Now(),7,4) & Left(Now(),2) & Mid(Now(),4,2))

There is a built-in Date() function, so you don't need to extract the current date from the Now() function. Besides your expression returns a number, not a date.

You can make the Date() field display YYYYMMDD in your query by entering the format yyyymmdd in the Field Properties as shown in the attached jpg image:-