Defaulting a Query to the Current Fiscal Year's Start and End Date

[Guest]

I have a simple Prompt which allows the users to enter a Start Date and End
Date. Although there will be times when the users will want to enter various
dates, they will mostly be entering the current Fiscal Year Start and End
Dates.

In my Query Criteria, I have the following statement:
Between [Enter Start Date] And [Enter End Date].

How can I modify this statement so that the Prompt defaults to the current
Fiscal Year Start and End dates?

 

[[MVP] S.Clark]

The parameter prompts do not have defaults. They're simply text boxes.

Create your own form, with text boxes (or date picker controls), and default
the values.

To calculate the current FY, do something like:

Function GetFY(pdtmDate, pintStartMonth) as Single

If Month(pdtmDate) >= pintStartMonth then
GetFY = Year(pdtmDate)
Else
GetFY = Year(pdtmDate) + 1
End if

End Function


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Amy Blankenship]

You can't, but you can have it that if they enter nothing, it defaults to
the current fiscal year

Between IIF([Enter a start date] <> "", [Enter a start date], Your starting
expression here) AND IIF([Enter an end date] <> "", [Enter an end date],
Your ending expression here)

HTH;

Amy

 

[Guest]

Looks like the Form option will be the way to go...thank you both!

 

[John Spencer]

You can't display a default, but you can for a value if the user leaves the
input blank

Between NZ([Enter Start Date:],DateSerial(Year(Date()),6,1)) and NZ([Enter
End Date:],DateSerial(Year(Date())+1,5,30))

 

[John Vinson]

I have a simple Prompt which allows the users to enter a Start Date and End
Date. Although there will be times when the users will want to enter various
dates, they will mostly be entering the current Fiscal Year Start and End
Dates.

In my Query Criteria, I have the following statement:
Between [Enter Start Date] And [Enter End Date].

How can I modify this statement so that the Prompt defaults to the current
Fiscal Year Start and End dates?

Try

BETWEEN NZ([Enter Start Date], DateSerial(Year(Date()) +
IIF(Month(Date()) >= 7, 1, 0), 7, 1)) AND NZ([Enter End Date],
DateSerial(Year(Date()) + IIF(Month(Date()) >= 7, 2, 1), 7, 1))

assuming your fiscal year starts July 1 - change the 7 to the month
that the FY starts (and the 1 to the day of the month that it starts,
if that's not the first).

John W. Vinson[MVP]

 

[Maarkr]

great, been looking for this...that actually gave me next FY...for This Fy in
a query i used (FY starts 1 Oct):

Between DateSerial(Year(Date())+IIf(Month(Date())>=10,0,-1),10,1) And
DateSerial(Year(Date())+IIf(Month(Date())>=10,1,0),10,1)

I have a simple Prompt which allows the users to enter a Start Date and End
Date. Although there will be times when the users will want to enter various
dates, they will mostly be entering the current Fiscal Year Start and End
Dates.

In my Query Criteria, I have the following statement:
Between [Enter Start Date] And [Enter End Date].

How can I modify this statement so that the Prompt defaults to the current
Fiscal Year Start and End dates?

Try

BETWEEN NZ([Enter Start Date], DateSerial(Year(Date()) +
IIF(Month(Date()) >= 7, 1, 0), 7, 1)) AND NZ([Enter End Date],
DateSerial(Year(Date()) + IIF(Month(Date()) >= 7, 2, 1), 7, 1))

assuming your fiscal year starts July 1 - change the 7 to the month
that the FY starts (and the 1 to the day of the month that it starts,
if that's not the first).

John W. Vinson[MVP]