two combo box to open report

[benjammin]

Hi,

This should be an easy one but I'm having a hard time understanding when to use double/single quotes, ampersands and brackets in VBA.

Anyways I have two combo boxes, one that chooses a Site (bound to SiteID) and the other chooses a Location (bound to LocationID) based on the Site. That works great. I have a query that has both SiteID and LocationID in it and it works just fine when I run a report based off of SiteID but when I try to combine the two things don't work.

Dim strWhere As StringIf Not IsNull(Me.cboSiteName) Then

    strWhere = "[SiteID] = '" & Me.cboSiteName & "'"
End If
    
Select Case Me.cboReports.Value
Case "Basic Report"
    If Me.cboSiteName = 0 Then
        DoCmd.OpenReport Reportname:="rptCalibrationALL", _
        View:=acViewReport
    Else
        DoCmd.OpenReport "rptCalibrationALL", acViewReport, , strWhere, acWindowNormal
    End If

Anyways while I was typing this I figured it out, LocationID is a number, SiteID is 3 text characters so I was trying to use single quotes when I only needed double. All works now after I added another one of these:

If Not IsNull(Me.cboLocationName) Then    strWhere = " SiteID = '" & cboSiteName & "' And LocationID = " & cboLocationName & ""
End If

So I really don't have any question except... what would you do differently to make it better and does anyone have a good resource to explain how to quote literal strings in SQL/VBA and where ampersands fit in?! Thanks

[pbaldy]

There's a decent tutorial here:

http://www.baldyweb.com/BuildSQL.htm

What you're doing would follow the same rules.

[benjammin]

Thanks for the link!

[pbaldy]

No problemo!