DLookup in strSQL for OpenRecordset

[szucker1]

I keep getting the error "Microsoft Access can't find the field 'I' referred to in your expression." when I try to run the following code:

 
    Dim rst1 As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT tblAssignments.Position " & _
    "FROM (tblAssignments " & _
    "INNER JOIN [tblTournament Levels] " & _
    "ON tblAssignments.Level = [tblTournament Levels].Level) " & _
    "INNER JOIN [tblReferee Credit Rates] " & _
    "ON [tblTournament Levels].Level = [tblReferee Credit Rates].Level " & _
    "WHERE (((""" & DLookup("[Credit]", "tblReferee Credit Rates", "[Level] = """ & [tblAssignments].[Level] & """ " & _
    "And [Position] = """ & [tblAssignments].[Position] & """") & """) Is Null))"
    Set rst1 = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

the strSQL string (except I put in """ & ... & """ around the DLookup in this try) came from the Row Source Property of a Combo Box which works fine. I am guessing the problem is in using the ampersand and quote marks correctly but I cannot get it to work.

[June7]

One problem is the spaces in the table name in the DLookup. Enclose the name in []. Now what happens?

A coding technique that can help debug long sql statement is to build the string in stages.

strSQL = strSQL & part1
strSQL = strSQL & part2
etc.

Also, set breakpoint to pause code and view the compiled string. Combine this with Debug.Print strSQL to easily read the string in VBA Immediate window. Is it correct?

[szucker1]

June7,

Thank you so much for your reply. Your coding technique was instrumental in helping me solve the problem. It was, as I thought, in the ampersands and quotes. I was treating the DLookup function, as well as, field names in the function as a variants by ending my string and then using ampersands to add them to the string. That was all wrong as I needed to just treat it all as characters and add extra quotes so the resultant string looked like what was in the Row Source Property. So the part of the string which was WRONG was:

 
    "WHERE (((""" & DLookup("[Credit]", "tblReferee Credit Rates", "[Level] = """ & [tblAssignments].[Level] & """ " & _
    "And [Position] = """ & [tblAssignments].[Position] & """") & """) Is Null))"

and after being CORRECTED is:

 
    "WHERE ((DLookup(""[Credit]"", ""[tblReferee Credit Rates]"", ""[Level] = """""" & [tblAssignments]![Level] & """""" " & _
    "And [Position] = """""" & [tblAssignments]![Position] & """""""") Is Null))"

The the table name with spaces that was not in [] was not a problem because the table name was already in quotes for the domain part of the DLookup function but I went ahead added the brackets just to be consistant.