CDate and gloabal date settings

[Sierras]

I'm having a little dificulty using CDate in a calculation between the
start of the current fiscal year and now.
The query I'm using below works fine for someone using DD/MM/YY as their
global date setting and produces a correct fiscal year starting Aug 1st of
the current year. But in the US, it doesn't work as the date is usually
MM/DD/YY and produces an incorrect fiscal start date of Jan 08.

So how can I write this same query so that it is independant of the user's
regional date settings.

Between CDate("01/08/" &
IIf(Month(Now())>=8,Year(Now()),CStr(CInt(Year(Now())-1)))) And Now()

 

[Michel Walsh]

Use DateSerial, in your VBA code, rather than using format representation of
a date:

BETWEEN DateSerial( iif( ...), 01, 08 ) AND now()


Vanderghast, Access MVP



I'm having a little dificulty using CDate in a calculation between the
start of the current fiscal year and now.
The query I'm using below works fine for someone using DD/MM/YY as their
global date setting and produces a correct fiscal year starting Aug 1st of
the current year. But in the US, it doesn't work as the date is usually
MM/DD/YY and produces an incorrect fiscal start date of Jan 08.

So how can I write this same query so that it is independant of the user's
regional date settings.

Between CDate("01/08/" &
IIf(Month(Now())>=8,Year(Now()),CStr(CInt(Year(Now())-1)))) And Now()

 

[Sierras]

This produces the same result as my original - it is still dependant on
the user's global date settings.
I'm looking for a solution that will make the query work regardless of the
user's regional date settings.

 

[Michel Walsh]

The field you use is a date_time field? DateSerial is a function, and so,
is NOT dependant of the regional setting in any way, since its first
argument is always the YEAR, the second argument the MONTH and the third
argument, the DAY. That is definitively independant of the regional setting.

If the field you use to compare to (the one on the left of BETWEEN) is NOT a
date_time field, but a STRING representation of a date_time, that creates
many problems, and regional setting is the least of them: strings are
compared differently than numbers. "a" is less than "aa" which is less than
"z", so "1" is less than "11" which is less than "2" (while, numerically,
11 > 2 ).

Note that there is NO

"01/08/" &

what so ever in the solution I proposed. Maybe I should have typed it in
full:

BETWEEN DateSerial( iif( Month(Now())>=8,Year(Now()),Year(Now())-1 ),
01, 08 ) AND now()



Vanderghast, Access MVP



This produces the same result as my original - it is still dependant on
the user's global date settings.
I'm looking for a solution that will make the query work regardless of the
user's regional date settings.

 

[Sierras]

Thanks for your help. The field I'm using is a date field.
Correct me if I'm not using this incorrectly. But if I just create a
field on a report and make the control source of that field

=DateSerial(iif( Month(Now())>=8,Year(Now()),Year(Now())-1 ),01, 08)

Then I get the start to be Jan 8th.
But if I flip the last two arguments like this:

=DateSerial(iif( Month(Now())>=8,Year(Now()),Year(Now())-1 ),08, 01)

then I get the start to be Aug 1st.
I thought I was getting this because my regional settings were mm/dd/yy.
And that anyone with dd/mm/yy would get Jan 8th as the start date.

Is this not so?

 

[Michel Walsh]

Nope. Whatever is the regional setting,

? DateSerial( 2007, 01, 08)

will return, in the Immediate Debug Window, or elsewhere, the 8th of
January, 2007. You can temporary change your regional setting and perform
the tests, in the immediate debug window as example, to convince yourself.

Vanderghast, Access MVP



Thanks for your help. The field I'm using is a date field.
Correct me if I'm not using this incorrectly. But if I just create a
field on a report and make the control source of that field

=DateSerial(iif( Month(Now())>=8,Year(Now()),Year(Now())-1 ),01, 08)

Then I get the start to be Jan 8th.
But if I flip the last two arguments like this:

=DateSerial(iif( Month(Now())>=8,Year(Now()),Year(Now())-1 ),08, 01)

then I get the start to be Aug 1st.
I thought I was getting this because my regional settings were mm/dd/yy.
And that anyone with dd/mm/yy would get Jan 8th as the start date.

Is this not so?

 

[John Spencer]

DateSerial requires three arguments and in the specific order
DateSerial(YEARNumber,MONTHnumber,DAYnumber)

DateSerial is not sensitive to regional settings. It always wants three
arguments and it always wants them in the specified order.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for your help. The field I'm using is a date field.
Correct me if I'm not using this incorrectly. But if I just create a
field on a report and make the control source of that field

=DateSerial(iif( Month(Now())>=8,Year(Now()),Year(Now())-1 ),01, 08)

Then I get the start to be Jan 8th.
But if I flip the last two arguments like this:

=DateSerial(iif( Month(Now())>=8,Year(Now()),Year(Now())-1 ),08, 01)

then I get the start to be Aug 1st.
I thought I was getting this because my regional settings were mm/dd/yy.
And that anyone with dd/mm/yy would get Jan 8th as the start date.

Is this not so?

 

[Sierras]

OK - I just tried it by changing my regional settings and it's working
fine.
That is, using the 08,01 makes the Fiscal year start at Aug 1st regardless
of regional settings.

Thanks for your help!!