Conditionally using * in a query formed from strings?

[Andy.T]

Hi guys,

I have a button on a Form which opens a report, passing in two WHERE clauses which are driven by two comboboxes on the Form (one for ProductType and one for ProductCollection).

Everything works great but I needed to add an "All" option to the comboboxes so that the user can get all Producttypes from a specific Collection or vice versa.

I've managed to add the "All" option to one of the comboboxes (using the UNION query method on the rowsource). I'm a bit stumped though, as to how I would actually tweak the function below to do a select * where the combobox value is found to be = All.

Private Sub ProduceReport_Click()
  
Dim strWhere As String
strWhere = "producttype = " & Me.ProductTypeForReport
strWhere = strWhere & " AND ProductCollectionName = '" & Me.CollectionForReport
strWhere = strWhere & "'"
  
Debug.Print strWhere
  
DoCmd.OpenReport "Products Report", acViewReport, , strWhere
    
End Sub

Any pointers would be a huge help, I'm tearing my hair out! :banghead:

Thanks,

Andy

 

[vbaInet]

You can use an IF...END IF statement. Have a think!

 

[Andy.T]

Thanks I did have a go at breaking it up and ....

...doh!!!

I'm being a muppet aren't I? I've literally, just realised as I'm typing this that I don't need to pass in the "All"s do I as they're not needed to constrain the query?!!

Gah!

 

[vbaInet]

That's right! Just check what value it is, and build the string accordingly!

 

[Andy.T]

This is probably about four times longer than it needs to be but it works great.

I couldn't think of a way to make it more cleverly cope with all four possible conditions (i.e both dropdowns are set to all, neither drop down is set to all, or one or other of them are) but here's what I did..

Private Sub ProduceReport_Click()
Dim strWhere As String
If Me.ProductTypeForReport = "All" And Me.CollectionForReport = "All" Then
DoCmd.OpenReport "Products Report", acViewReport

Else
End If

If Me.ProductTypeForReport <> "All" And Me.CollectionForReport <> "All" Then
strWhere = "producttypename = '" & Me.ProductTypeForReport
strWhere = strWhere & "' AND ProductCollectionName = '" & Me.CollectionForReport
strWhere = strWhere & "'"
Debug.Print strWhere
DoCmd.OpenReport "Products Report", acViewReport, , strWhere

Else
End If

If Me.ProductTypeForReport <> "All" And Me.CollectionForReport = "All" Then
strWhere = "producttypename = '" & Me.ProductTypeForReport
strWhere = strWhere & "'"
Debug.Print strWhere
DoCmd.OpenReport "Products Report", acViewReport, , strWhere

Else
End If

If Me.ProductTypeForReport = "All" And Me.CollectionForReport <> "All" Then
strWhere = "ProductCollectionName = '" & Me.CollectionForReport
strWhere = strWhere & "'"
Debug.Print strWhere
DoCmd.OpenReport "Products Report", acViewReport, , strWhere

Else
End If

End Sub

Thanks for your help.

 

[vbaInet]

It's good to see someone who takes on the initiative to come up with something Very good attempt!

Perhaps you could consider one of these shorter examples:

Private Sub ProduceReport_Click()
    Dim strWhere As String
    Dim strPfr   As String
    Dim strCfr   As String
    
    strPfr = Me.ProductTypeForReport
    strCfr = Me.CollectionForReport
    
    ' Construct
    If strPfr <> "All" Then
        strWhere = "producttypename = '" & strPfr & "' AND "
    End If
    
    If strCfr <> "All" Then
        strWhere = strWhere & "ProductCollectionName = '" & strCfr & "' AND "
    End If
    
    ' Strip trailing " AND "
    strWhere = Left(strWhere, Len(strWhere) - 5)
    
    DoCmd.OpenReport "Products Report", acViewReport, , strWhere
End Sub

Private Sub ProduceReport_Click()
    Dim strWhere As String
    Dim strPfr   As String
    Dim strCfr   As String
    
    strPfr = Me.ProductTypeForReport
    strCfr = Me.CollectionForReport
    
    ' Construct
    strWhere = "producttypename = '" & strPfr & "' AND "
    strWhere = strWhere & "ProductCollectionName = '" & strCfr & "' AND "
    
    ' Strip trailing " AND "
    strWhere = Left(strWhere, Len(strWhere) - 5)
    
    ' Finalise if either values contain "All"
    If strPfr = "All" Or strCfr = "All" Then
        strWhere = Replace(strWhere, "= 'All'", "Not Is Null")
    End If
    
    DoCmd.OpenReport "Products Report", acViewReport, , strWhere
End Sub

 

[Andy.T]

I knew there'd be a clever way to conditionally make it cope with both or one. It was just too clever for me.

Thanks again!

 

[vbaInet]

You're welcome!