Variable Criteria for Query

[Guest]

I am using Access 2003. In my program, I base my reports on date ranges.
Currenlty, I am using a form with two "unbound" text boxes that are
"Date_From" and Date_To" for the criteria for my reports. I use these for
the criteria in the "Date" field for my query for my reports ("between
Date_From and Date_To"). These reports are usually Monthly reports, so the
user must enter both the starting month date (9-1-06), and the ending date
(9-30-06). The reports could also be yearly reports or just "user defined"
reports. What I would like to do is have the query look at a single
"unbound" text box that is formatted as a month/Year (mmmm yyyy) or just yera
(yyyy), and have Access figure out what the starting day is and what the last
day of that range is.

I know about the DateSerial Function "Between
DateSerial(Year([Forms]![frmHeader_Monthly_Flight_Summary]![txtMonth]),Month([Forms]![frmHeader_Monthly_Flight_Summary]![txtMonth]),1)
And
DateSerial(Year([Forms]![frmHeader_Monthly_Flight_Summary]![txtMonth]),Month([Forms]![frmHeader_Monthly_Flight_Summary]![txtMonth])+1,0)"

What I would like to do is be able to make this a monthly report, a yearly
report, or even just a "User Defined" report. I have tried to use an option
group with three options. I used the "When Updated" of the option group
event to enter the DateSerial Function or just the "Date_From and Date_To"
values into a text box. I then had the Query try and use that text box for
its criteria. Access did not like that Idea.

Am I trying to hard? Should I just leave it to the user to enter both
dates, and quite trying to be cute?

Balfour211

 

[Naeem]

Hi,
for the date range, i guess months always start with 01 so if that is
the case you can use the following for the from date:

from date value:
format("01/" & [Forms]![frmHeader_Monthly_Flight_Summary]![txtMonth],
"dd mmmm yyyy")

try to put the input mask something like 00/00 for 01/06 or 00/0000
(for 01/2006)

the to date can be like (if the input mask is 00/00):
format(
Day(DateSerial(Val(Right([Forms]![frmHeader_Monthly_Flight_Summary]![txtMonth],2)),
val(left([Forms]![frmHeader_Monthly_Flight_Summary]![txtMonth],2))+1,
0)) & "/" & [Forms]![frmHeader_Monthly_Flight_Summary]![txtMonth] , "dd
mmmm yyyy")

if this is too confusing try the function below to find out last day of
the requested month and then replace it with the long code:

Public Function DaysInMonth(Optional DateInput As Date) As Integer
If DateInput = 0 Then
DateInput = Date
End If
DaysInMonth = Day(DateSerial(Year(DateInput), Month(DateInput) + 1,
0))
End Function

ciao