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

Selasa, 14 Juli 2015

Re: [MS_AccessPros] Filtering report with mulitple criterias

Rather than use a command button to clear the combo box, I use a UNION query to provide a "null" row. Because of a bug (feature) in Access, I first build a ztblNulls table that has one column of each data type that I might want to null out in a combo box. (Typically, Long and Text is enough.) I save one row with a Null value in all columns. Then I set the Row Source like this:

SELECT ztblNulls.NullLong, "<< All values >>" As CustomerName

FROM ztblNulls

UNION ALL

SELECT CustomerID, CustomerName

FROM Customers

ORDER BY CustomerName;

The "<" sorts before any other characters, so the "null" row always appears at the top. User simply chooses that row to clear the value in the combo box.

Reason for the table workaround:

If you were to do:

SELECT Null As CustomerID, "<< All Values >>" As CustomerName

FROM AnyTable

UNION

SELECT CustomerID, CustomerName

FROM Customers

ORDER BY CustomerName

.. you get weird unprintable and unusable values in the first column because the Null in the first SELECT is a true Variant with no data type. It ends up returning the hex values for the true long CusomerID column. An added benefit is the UNION ALL with the single row from the dummy table is more efficient.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jul 14, 2015, at 1:37 AM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

> "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