ACCESS FEED: Re: [MS_AccessPros] Filtering report with mulitple criterias

Senin, 13 Juli 2015

Re: [MS_AccessPros] Filtering report with mulitple criterias

Hi Art,

adding on to Duane's excellent advice .. . and I will also enclose in With since more than one thing happens with the object

ask yourself: do you want the chebox to be tripple-state (yes/no/null) -- that is what I generally do for unbound criteria. If so, then:

with Me.checkbox_controlname ' YN
    If Not IsNull(.value) Then
       strWhere = strWhere & " AND [MyYNfield] = " & .value
    End If
end with

~~~

in this way, you can do
With me.mycontrolname
where mycontrolname is each control the user can enter criteria for. I added a comment to the end, YN, so it is easier to see when you want to ctrl-drag those statements to make a copy for that control type ;) ~

Warm Regards,
crystal

Learn Access -6- Create a Continuous Form in Microsoft Access
https://www.youtube.com/watch?v=norAB9QvIls&list=PL1B2705CCB40CA4CA
- includes many tips for designing forms efficiently

~ be awesome today ~

On 7/13/2015 2:07 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] wrote:

How would I handle check boxes on my filter form.

With Warm Regards,

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265 Ext. 130

Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."

From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Monday, July 13, 2015 3:02 PM
Subject: RE: [MS_AccessPros] Filtering report with mulitple criterias

Hi Art,

Good question.

I almost always leave the report's record source clear of any dynamic filtering. Assuming you have a form with a number of text and combo boxes and a button to open the report. The code to open the report might look like:

Dim strWhere as String
Dim strReport as String
strReport = "rptMyReportNameHere"
strWhere = "1= 1 "    'no effect on the filter
If Not IsNull(Me.txtStartDate) Then
    strWhere = strWhere & " AND [MyDateField] >= #" & Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
    strWhere = strWhere & " AND [MyDateField] <= #" & Me.txtEndDate & "# "
End If
If Not IsNull(Me.txtCity) Then
    strWhere = strWhere & " AND [MyCityField] = """ & Me.txtCity & """ "
End If
If Not IsNull(Me.txtUnitNumber) Then
     strWhere = strWhere & " AND [MyUnitNumberField] = """ & Me.txtUnitNumber & """ "
End If
If Not IsNull(Me.txtSomeNumeric) Then
     strWhere = strWhere & " AND [MySomeNumericField] = " & Me.txtSomeNumeric & " "
End If
' add more criteria as needed watching for date, string, and numeric data types
DoCmd.OpenReport strReport, acViewPreview, , strWhere

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 13 Jul 2015 12:31:56 -0700
Subject: [MS_AccessPros] Filtering report with mulitple criterias

I am looking for an example of how to filter a report using multiple dropdowns. I have a report which I need to filter by community, move-in date, city, class, project, tenant, unit number. It could be single criteria or some or all of them. If anyone knows of anything, please let me know.

Thank you,

Art Lorenzini
Sioux Falls, SD