date range in Select Query

[Guest]

I have several reports that prompt for a date range. One report that is run
each day, begins at the start of the fiscal year (Nov 1) and ends on the
current day. Rather than prompting for the date range each day, I'd like to
just click "run" and it figure the date range automatically. What is the
best way to do this?

 

[Graham R Seach]

You can use the BETWEEN operator:
WHERE [someddate]
BETWEEN DateSerial(Year(Date())) + (Format(Date(), "mmdd") > "1101"),
11, 1)
AND Date()

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Ed Warren]

There are several ways to do this depending on experience and ability.


1. Really easy
build a table with one column and one row
column [StartFiscalYear] --> date/time
then enter your fiscal year in that table

Now put that table on the query designer and use the value
[startfiscalyear] in a between statement
e.g. [date] between [startfiscalyear] and now()

2. Easy--
build a form with a text field say StartFiscalYear, set the default to
11/1/2004 (change this next year)
put in another textbox on the form Today, set the default to =date

build the query feeding the report to use these two fields
e.g

select * from table where [date] between forms![myform].[startFiscalYear]
and forms![myform].[today]
note you can use the build functionality in the query designer to build all
the arcane references to the form.

3. harder -- but then will work without having to update it once a year.
build code in the code module to return the start of the fiscal year

e.g.
Module1
Function FiscalYear() As Date
Dim dtFiscalYear As Date
Dim strFiscalYear As String
Dim intMonth As Integer
intMonth = Month(Date)
'this will tell you the current month
If intMonth < 11 Then
strFiscalYear = "11/1/" & Year(Date) - 1
Else
strFiscalYear = "11/1/" & Year(Date)
End If
dtFiscalYear = CDate(strFiscalYear)
FiscalYear = dtFiscalYear
End Function

then your query would look like

SELECT FinalSchedule.Date
FROM FinalSchedule
WHERE (((FinalSchedule.Date) Between fiscalyear() And Date()));

Hope this helps

Ed Warren

 

[Guest]

Thanks guys! This is a great forum to learn the "tricks of the trade".... I
appreciate your help!

There are several ways to do this depending on experience and ability.


1. Really easy
build a table with one column and one row
column [StartFiscalYear] --> date/time
then enter your fiscal year in that table

Now put that table on the query designer and use the value
[startfiscalyear] in a between statement
e.g. [date] between [startfiscalyear] and now()

2. Easy--
build a form with a text field say StartFiscalYear, set the default to
11/1/2004 (change this next year)
put in another textbox on the form Today, set the default to =date

build the query feeding the report to use these two fields
e.g

select * from table where [date] between forms![myform].[startFiscalYear]
and forms![myform].[today]
note you can use the build functionality in the query designer to build all
the arcane references to the form.

3. harder -- but then will work without having to update it once a year.
build code in the code module to return the start of the fiscal year

e.g.
Module1
Function FiscalYear() As Date
Dim dtFiscalYear As Date
Dim strFiscalYear As String
Dim intMonth As Integer
intMonth = Month(Date)
'this will tell you the current month
If intMonth < 11 Then
strFiscalYear = "11/1/" & Year(Date) - 1
Else
strFiscalYear = "11/1/" & Year(Date)
End If
dtFiscalYear = CDate(strFiscalYear)
FiscalYear = dtFiscalYear
End Function

then your query would look like

SELECT FinalSchedule.Date
FROM FinalSchedule
WHERE (((FinalSchedule.Date) Between fiscalyear() And Date()));

Hope this helps

Ed Warren

I have several reports that prompt for a date range. One report that is
run
each day, begins at the start of the fiscal year (Nov 1) and ends on the
current day. Rather than prompting for the date range each day, I'd like
to
just click "run" and it figure the date range automatically. What is the
best way to do this?

 

[Guest]

Hi Ed!
I am sorry to bud in on someone else's query, but I found your answer to be
very enlightening. I chose the harder option and created a module in MVB.
I just copied your formula using 04 instead of 11.
My problem is a real beginner's one. How do I move the module into my DB?
could you help?
Gerry
If I am out of line in writing to you from someone else's post, please let
me know.

There are several ways to do this depending on experience and ability.


1. Really easy
build a table with one column and one row
column [StartFiscalYear] --> date/time
then enter your fiscal year in that table

Now put that table on the query designer and use the value
[startfiscalyear] in a between statement
e.g. [date] between [startfiscalyear] and now()

2. Easy--
build a form with a text field say StartFiscalYear, set the default to
11/1/2004 (change this next year)
put in another textbox on the form Today, set the default to =date

build the query feeding the report to use these two fields
e.g

select * from table where [date] between forms![myform].[startFiscalYear]
and forms![myform].[today]
note you can use the build functionality in the query designer to build all
the arcane references to the form.

3. harder -- but then will work without having to update it once a year.
build code in the code module to return the start of the fiscal year

e.g.
Module1
Function FiscalYear() As Date
Dim dtFiscalYear As Date
Dim strFiscalYear As String
Dim intMonth As Integer
intMonth = Month(Date)
'this will tell you the current month
If intMonth < 11 Then
strFiscalYear = "11/1/" & Year(Date) - 1
Else
strFiscalYear = "11/1/" & Year(Date)
End If
dtFiscalYear = CDate(strFiscalYear)
FiscalYear = dtFiscalYear
End Function

then your query would look like

SELECT FinalSchedule.Date
FROM FinalSchedule
WHERE (((FinalSchedule.Date) Between fiscalyear() And Date()));

Hope this helps

Ed Warren

I have several reports that prompt for a date range. One report that is
run
each day, begins at the start of the fiscal year (Nov 1) and ends on the
current day. Rather than prompting for the date range each day, I'd like
to
just click "run" and it figure the date range automatically. What is the
best way to do this?