Pesky quotes

[brewpedals]

I could use some help. If you could review my code below I would appreciate it. It generates the following error:

Run-time Error 3075: Syntax error (missing operator) in query expression '((([tbl_UnitList].[SectionCode]) = & nbrSec & ) And (([tbl_UT].[APICode]) = "));'

Notice please that the reference to '" & strAPI & "' is replaced by only a double quotation mark.

My intent is to run the SQL statement and insert the result into 20 text box controls on my form using the strAPI and nbrSec variables as filters.

The query runs fine with out the variables strAPI and nbrSec, but I was hoping to not repeat the SQL for each of the 20 controls on my form.

Dim strAPI As [B]String[/B]
Dim nbrSec As [B]Long[/B]
Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT [tbl_UnitList].[SectionCode], Count([tbl_UT].[ASSETID]) AS BackLog " & _
    "FROM tbl_UT INNER JOIN tbl_UnitList ON [tbl_UT].[UnitCode] = [tbl_UnitList].[UnitCode] " & _
    "WHERE(((DateDiff('q', Now(), [NextUTInsp])) < -1 Or (DateDiff('q', Now(), [NextUTInsp])) Is Null)) " & _
    "GROUP BY [tbl_UnitList].[SectionCode], [tbl_UT].[APICode] " & _
    "HAVING ((([tbl_UnitList].[SectionCode]) =  & nbrSec & ) And (([tbl_UT].[APICode]) = '" & strAPI & "'));"
    

strAPI = "API 570"
nbrSec = 1
Set rs = CurrentDb.OpenRecordset(strSQL)
Me.txt_Dcc570UtBl.Value = rs.Fields("BackLog").Value
Set rs = Nothing

Thanks for your time.

 

[checoturco]

did you try

((([tbl_UnitList].[SectionCode]) =" & nbrSec & ") And (([tbl_UT].[APICode]) = "));'

 

[brewpedals]

Thanks checoturco,

Yes, it returns "Run-time Error 3021: No current record." I believe that is because adding double quotes on the nbrSec variable interprets it as text, and it is a Long interger.

I believe the problem is my usage of quotes around my strAPI variable.
None of the following work:

'" & strAPI & "'
""" & strAPI & """
''' & strAPI & '''
& strAPI &

& strQuote & strAPI & strQuote &
(I Dim'd strQuote as string and set it = Chr$(34) )