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

Senin, 13 Juli 2015

Re: [MS_AccessPros] Filtering report with mulitple criterias

> "is there any way for a user to set the value to Null without using code?

yes -- they can click (or press Spacebar?) to alternate between the choices -- Yes / No/ Null

I like this. On combox, I sometimes put a small command button with an X to clear the choice -- and definitely on listboxes as there is no way for the user to do it. I usually use triple-state checkboxes for unbound criteria. Tables must have one value or the other ... problem with Yes/No. Many years, I used Integer instead as per Allen Browne's recommendation -- and still do except for web databases (Access Apps). However, I like the triple state to know if the criteria needs to be considered.

I agree with your example though -- option group is good :) ~

Also a bit different than you, I like to do this:

~~~~~~~~~~~~~~~
dim vWhere as variant
vWhere = null

with me.controlname
   if not isnull(.value) then
      vWhere = (vWhere + " AND ") & "[numberfieldname]=" & .value
   endif 'hey, why type a space when VBA will correct it if you don't ... leftover from habits a long time ago ~
end with

~~~~~~~~~~~~~

thanks, Duane

hope you enjoyed May 17 in some way ~

Warm Regards,
crystal

~ be awesome today ~

On 7/13/2015 3:51 PM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] wrote:

Crystal,

With a triple-state check box, is there any way for a user to set the value to Null without using code? That's why I would avoid check boxes for criteria.

I might use an option group with:
o Active
o Inactive
o Both

Or depending on form space possibly a combo box with three options.

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 13 Jul 2015 15:09:23 -0600
Subject: Re: [MS_AccessPros] Filtering report with mulitple criterias

correction:
checkbox to be triple-state

On 7/13/2015 3:07 PM, crystal 8 wrote:

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