Set Query Criteria based on Control values

[postman]

I have a report based on several criteria that the user can select in listboxes on a form.

Each of these criteria have an "ALL" option which, if selected, will prevent that criteria from being added to the WHERE clause in the Query string.

Currently, this is done in VBA, but I was wondering if there was a way to do this in the SQL statement itself (or the Query designer in Access), so I can rely less on VBA.

Thanks in advance.

[Mihail]

Dim MyCriteria As String
    MyCriteria = MyListBox
    If MyCriteria = "ALL" Then
        MyCriteria = "*"
    End If
 
    Dim MySQL As String
    MySQL = _
        "SELECT TableName.FieldName " & _
        "FROM TableName " & _
        "WHERE (((TableName.FieldName) Like """ & MyCriteria & """));"
 

Another solution:
Create a PUBLIC Function

Public Function ReturnCriteria(FieldIdentifier As String) As String
'The argument FieldIdentifier will pass the FieldName from your query to tha function
    Select Case FieldIdentifier
        Case "Field_1_Name"
            ReturnCriteria = ListBox_1
        Case "Field_2_Name"
            ReturnCriteria = ListBox_2
        '.............................
    End Select
    If ReturnCriteria = "ALL" Then
        ReturnCriteria = "Like ""*"""
    End If
End Function

In your query, in "Criteria" row, for every field where you wish to pass criteria from a list box, write:
ReturnCriteria("FieldName")


Good luck !

[Adam Tippelt]

I've recently done this myself for a report based on a union query that references about 8 different tables from 7 different filter boxes. I used combo boxes as opposed to list boxes, but I'll talk you through what I did as I don't seem why listboxes would be a problem:

Firstly, how have you added in the All option? Have you added it as another value in the table, or just added it to the filter using SQL?
I found the SQL route is tidier because the all option was only needed to indicate 'no filter', and not an actual value in the table, so for the rowsource of each filter listbox, you're going to want something along the lines of:

SELECT DISTINCT TableName.FieldName FROM TableName UNION SELECT '(All)' FROM TableName
ORDER BY TableName.FieldName;

(Putting brackets around All defaults it to the top of the list)

After I did that for each filter box, the report recordsource can be based on a query like this:

SELECT TableName.Field1, TableName.Field2, TableName.Field3
FROM TableName 
WHERE (((TableName.Field1=Forms! FilterForm!Filter1 Or Forms! FilterForm!Filter1 ="(All)")=True) And ((TableName.Field2=Forms!FilterForm!Filter2 Or Forms! FilterForm!Filter2="(All)")=True) And ((TableName.Field3=Forms! FilterForm!Filter3 Or Forms! FilterForm!Filter3 Is Null)=True))
ORDER BY Whatever;
 

This example treats Field1 and Field2 as strings, and Field3 as an integer.

As you've probably noticed, there is slightly different coding for field3 - this is because integer fields have a slight problem with the '(All)' value - because '(All)' is a string, when you try and run the report it errors as a type mismatch. I don't know whether changing the field to type Variant would solve this, but to get round it, on the "load report" button on the filter form, I added some code before it opened the report:

If Me.IntegerField.Value = "(All)" Then
    Me.IntegerField.Value = Null
End If
DoCmd.OpenReport "ReportName", acViewReport

This will allow you to create an entire query to base the report on, with only a very small amount of programming required to handle integers (maybe not even required if Variant works)

It might not be the perfect solution, but it's one I've found to work well.

Hope that helps.

Adam.

[postman]

Thank you both for your responses. That will help.

Thanks.