Creating Form To Filter Multiple Date Parameters 2

[nyamrembo]

Hi,

I am trying to build a form that I can use to filter date parameters. What I have in mind is a form that has three date fields, [purchase date], [receive date] [ship date]. The parameters will be used independently but I want them on one form so the user can select whichever one they are filtering the date for. Say if I want to find all the purchased products for a specific month in a specific year, I enter something like mm-yyyy(10-2009) and it pulls all the products purchased the month of October 2009 in a report form. And if I want to filter the products that were shipped that month, I enter the date criteria for that month and pull the report. I have a query parameter that works fine, but I want to use a form so that the user has options to chose the criteria they want to print the report for. Does anyone have an idea how i can go about doing this?
Thanks in advance for your help.

 

[ZmrAbdulla]

Look into the sample db provided by Microsoft(Northwind.mdb)

Zameer Abdulla

 

[hondaman2003]

in VBA there is a command that looks something like this:

DoCmd.OpenReport

In the options of this command there is a filtering option that is structured like a where SQL statement. You could use it like this

DoCmd.OpenReport "report name", acViewPreview, , "[purchase date]=#" & [date field on form] & "#"

I know you want to be able to report out on different dates so you could have a button for each type of date you want to report on.

Does that make sence?

 

[dhookom]

The solution depends on the specifications. Do you want to search each of the date fields at the same time or only choose a single date field?

I would create a form with two text boxes (txtBegDate and txtEndDate). These could be filled by typing or some other selection process (calendar or whatever). I would have a list box or option group (OptGroupDateField) to select the date field. These would have values of 1, 2, 3. Then my code would look something like:

Dim strWhere as String
Dim strDateField as String
strWhere = "1=1 "
If Not IsNull(Me.OptGroupDateField) Then
    strDateField = Choose(Me.OptGroupDateField, _
        "[Purchase Date]","[Receive Date]","[Ship Date]")
    If Not IsNull(Me.txtBegDate) Then
        strWhere = strWhere & " AND " & strDateField & _
            " >= #" & Me.txtBegDate & "# "
    End If
    If Not IsNull(Me.txtEndDate) Then
        strWhere = strWhere & " AND " & strDateField & _
            " <= #" & Me.txtEndDate & "# "
    End If  
End If
DoCmd.OpenReport "rptMyRptName", acViewPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[nyamrembo]

Thank you. I will first try with text box because I need a date range.Will let you know how it fairs.

 

[nyamrembo]

I have successfully created a form with two date fields and used the code above and I am able to filter the report using the date range that I specify and this works great. Now, I have another question, I have several reports that will be using this same date filter. My question is, is there a way to make the code filter for multiple reports? Right now what I have done is created a command button for each report ([purchase date report], [receive date report], [ship date report]) with the same code and just changing the report name under each command button but this is kind redundant.

Thanks again

 

[hondaman2003]

you could build an option group and have an option for each report then have one button. The code for the button would be all the same as above except for replacing the open report command with this code:

if optReportname=0 then msgbox "Please select a report to run":exit sub
dim strReportName
select case optReportName
case 1
strReportName="report name"
case 2
strReportName="report name2"
end select

DoCmd.OpenReport strReportName, acViewPreview, , strWhere

 

[nyamrembo]

Beautiful idea. Will test and let you know. Thanks a bunch

 

[hondaman2003]

One more idea would be a combo box. Which ever nicer looking.

 

[nyamrembo]

Thank again. It worked beautifully!