Fiscal Date default value

[Guest]

Greetings all.
My body is at work, but my brain must be on vacation because I can't seem to
get this right. I have a form used to hold the beginning and ending dates for
a date range query. I need to use fiscal dating (22nd thru the 21st) I am
having an issue getting the begin date correct. If the current date is the
21st or less, I need the begin default value to be the 22nd of the PREVIOUS
month. On the 22nd or greater, I need the begin date to be the 22nd of the
CURRENT month. My logic is failing as this should be relatively easy.
My current default date string looks like this and sets the begin date to
the 1st of the current month:
=IIF(FORMAT(DATE(),"DD"=1,DATEADD("M",-1,DATE()),(DATE()-DAY(DATE())+1))
Format for value mm/dd/yy
HELP!

 

[Dirk Goldgar]

Greetings all.
My body is at work, but my brain must be on vacation because I can't
seem to get this right. I have a form used to hold the beginning and
ending dates for a date range query. I need to use fiscal dating
(22nd thru the 21st) I am having an issue getting the begin date
correct. If the current date is the 21st or less, I need the begin
default value to be the 22nd of the PREVIOUS month. On the 22nd or
greater, I need the begin date to be the 22nd of the CURRENT month.
My logic is failing as this should be relatively easy. My current
default date string looks like this and sets the begin date to the
1st of the current month:
=IIF(FORMAT(DATE(),"DD"=1,DATEADD("M",-1,DATE()),(DATE()-DAY(DATE())+1))
Format for value mm/dd/yy
HELP!

There may be a better way, but I think this does what you want:

=IIf(Day(Date())< 22,
DateSerial(Year(Date()), Month(Date()) - 1, 22),
DateSerial(Year(Date()), Month(Date()), 22))

 

[John Vinson]

Greetings all.
My body is at work, but my brain must be on vacation because I can't seem to
get this right. I have a form used to hold the beginning and ending dates for
a date range query. I need to use fiscal dating (22nd thru the 21st) I am
having an issue getting the begin date correct. If the current date is the
21st or less, I need the begin default value to be the 22nd of the PREVIOUS
month. On the 22nd or greater, I need the begin date to be the 22nd of the
CURRENT month. My logic is failing as this should be relatively easy.
My current default date string looks like this and sets the begin date to
the 1st of the current month:
=IIF(FORMAT(DATE(),"DD"=1,DATEADD("M",-1,DATE()),(DATE()-DAY(DATE())+1))
Format for value mm/dd/yy
HELP!

You're doing this the HARD way!

DateSerial(Year(Date()), Month(Date()) - IIF(Day(Date() < 22, 1, 0),
22)


John W. Vinson[MVP]

 

[Dirk Goldgar]

DateSerial(Year(Date()), Month(Date()) - IIF(Day(Date() < 22, 1, 0),
22)

Yours is better than mine.

 

[Guest]

Thanks for the prompt response!

Yours is better than mine.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)