Query Criteria using a form field with In() (1 Viewer)

[steve1111]

Hello all,

I have a form that has multiselect listboxes that on the click event will update a strWhere text field that is used when opening a report to pass in the where clause. that part works fine. (example of strWhereVanID In(1,4,5))

However, i want to use part of that same field the (In(1,4,5) to pass into the query in the event the user would rather export the data to excel. When I set the criteria to Forms!theform!theField or Forms!theform!theField is null in the query criteria it gives me a "expression is too complex...."

However if i type in the field value from the form, for example In(1,4,5) then the query runs fine. I have tried using eval() with no luck.

Thanks for any suggestions.

 

[sneuberg]

It sound like you want to set the WHERE clause of the query in the same way you can with reports. You may have notice that DoCmd.OpenQuery doesn't have this sort of argument. You cannot set this via a reference as it appear you are trying to do. If for example you put something like strWhereVanID In(1,4,5) in the reference then the WHERE clause will end up looking something like:

WHERE strWhereVanID = strWhereVanID In(1,4,5)

I don't know why the error you get is about complexity when I'm fairly certain that just incorrect syntax.

QueryDefs (stored queries you create) do have a filter property which act just like the WHERE clause. I found out how you set is in this Web Page.

First they use the following subroutine and so you will need to add that to your module.

Sub SetQueryProperty(strQueryName As String, strPropertyName As String, varPropVal)
On Error Resume Next
    Dim db As Database
    Dim qdf As QueryDef
    Dim prp As DAO.Property
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs(strQueryName)
    
    With qdf
        Set prp = qdf.Properties(strPropertyName)
        If Err Then
            Set prp = .CreateProperty(strPropertyName, dbText, varPropVal)
            .Properties.Append prp
        Else
            prp.Value = varPropVal
        End If
    End With
    
    Set prp = Nothing
    Set qdf = Nothing
    Set db = Nothing
    
End Sub

The use of this is demonstrated in the following code that you can find in the attached database in the RunQuery_Click subroutine in the form module.

Dim strWhere As String
Dim qdf As DAO.QueryDef

strWhere = "strWhereVanID In(1,4,5)"
SetQueryProperty "Query2", "Filter", strWhere
SetQueryProperty "Query2", "FilterOnLoad", True
DoCmd.OpenQuery "Query2", acViewNormal, acEdit

Here the filter is set to the value in your post, i.e., "strWhereVanID In(1,4,5)". You can see when you click the button on the form that this works. I found through experimentation that I had to set the FilterOnLoad to True to get this to work.


Instead of doing this, another way that might work for you is to output a report to excel. If you do that note that the headings in Excel are the names of the textboxes in the report and that you do this in three steps.

1. Open the report with the desire WHERE clause in hidden view
2. Use DoCmd.OutputTO to output the report to Excel
3. Close the report

 

[steve1111]

Brilliant, worked perfectly! THANK YOU!

 

[steve1111]

Hi,

One issue I cant seem to work through is how to set the filter of the query back to null. If i multi select from my form lbx the filter two values it updates my txtstrWhere to "DivisionID In(1,2)" like it should. Then i run the query, all works well still and filters the query as it should. but then if i update the lbx to not selecting any values the txtstrWhere updates back to a null value, as it should, but when i run the query again it is still pulling up the "DivisionID In(1,2)".

When i open the query in design view the "DivisionID In(1,2)" saved in the Filter line in the properties. I need a way that after i export my query to Excel it clears the filter line in my query....I hope i explained that correctly.

 

[sneuberg]

Are you say that if you execute a statement like:

SetQueryProperty "Query2", "Filter", ""

that the querydef still has the previous filter in it?

I think I need to see your code to figure this out. Could you upload your database or a copy with the relevant parts.

 

[steve1111]

I have tried Null, "" and vbNullString all as a filter and it keeps saving the previous queryDef
thanks for the peek

Code:
If Me.chkExcel = True Then
' --------First check if the query is open and close if it is
If CurrentData.AllQueries("accqInvoices").IsLoaded Then
SetQueryProperty "accqInvoices", "Filter", vbNullString
DoCmd.Close acQuery, "accqInvoices", acSaveYes
Else
End If
' -----Next Check if the txtStrWhere is null
If IsNull(Forms!rptfInvoices!txtStrWhere) Then
SetQueryProperty "accqInvoices", "Filter", vbNullString
SetQueryProperty "accqInvoices", "FilterOnLoad", False
DoCmd.OutputTo acOutputQuery, "accqInvoices", acFormatXLSX, "S:\Shared Workspace\Transportation\Transportation Coordinators\RM\OutputFiles\accqInvoices.xlsx", True, "", , acExportQualityPrint
Else
strWhere = Forms!rptfInvoices!txtStrWhere
SetQueryProperty "accqInvoices", "Filter", strWhere
SetQueryProperty "accqInvoices", "FilterOnLoad", True
DoCmd.OutputTo acOutputQuery, "accqInvoices", acFormatXLSX, "S:\Shared Workspace\Transportation\Transportation Coordinators\RM\OutputFiles\accqInvoices.xlsx", True, "", , acExportQualityPrint
End If
End If

SetQueryProperty "accqInvoices", "Filter", Null

 

[sneuberg]

Weird but it seems it needs to be set to an empty string not just erased. When I try

strWhere = """"

in my test database it sets the filter to " and displays all records. If I change strWhere to a variant I find that

strWhere = True

also works.

 

[steve1111]

Fantastic thanks, both scenarios worked for me as well!