Problem with converting Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've been thinking the problem is woith my computer's regional settings,but
it's not.
I got this excpression
:IIf([Health_Production].[EffMonth]=#07/31/2003#),Sum([Health_Production].[APIMvmnt]),0).

When i run the query it returns no value becuase the date changes itself to
31/07/2003.

what can i do to make sure that the date doesn't change when i run my query??
 
Benton said:
I've been thinking the problem is woith my computer's regional
settings,but it's not.
I got this excpression IIf([Health_Production].[EffMonth]=#07/31/2003#),Sum([Health_Production].[AP
IMvmnt]),0).

When i run the query it returns no value becuase the date changes
itself to 31/07/2003.

what can i do to make sure that the date doesn't change when i run my
query??
Click to expand...

What do you mean the "date changes itself"? Date constants in queries must
use US format (as yours does, or a format that is not dependent on regional
settings (YYYY-MM-DD, or MMM-DD-YYYY, etc.). I see no reason why your query
should not work since it is using US format.

How your data appears when you look at the tables _is_ dependent on your
regional settings, but that has nothing to do with how you write your query.
 
Oh what i was doing is,on my query, i was typing in the date using South
Africa's format which is (dd/mm/yyyy) and as soon as i press enter it changes
itselft to (mm/dd/yyyy).Is it mandatory for us who are outisde the US to use
,their format???
Thanks
 
Benton said:
Oh what i was doing is,on my query, i was typing in the date using
South Africa's format which is (dd/mm/yyyy) and as soon as i press
enter it changes itselft to (mm/dd/yyyy).Is it mandatory for us who
are outisde the US to use ,their format???
Click to expand...

When hard-coded into the query's SQL Access has to do something that is
consistent with that value otherwise you have a query that does something
different depending on the users regiaonal settings (not good). Since they
had to pick a format to use they picked the one for their largest user-base.
 
Is it mandatory for us who are outisde the US to use
,their format???
Click to expand...

Yes, it is; the programmers who wrote Access were based in the US and
used US format.

Alternatively you can use an UNAMBIGUOUS format such as yyyy-mm-dd.


John W. Vinson[MVP]
 
Back
Top