Invalid Column error in SQL Server ODBC Where Clause (1 Viewer)

[Bilbo_Baggins_Esq]

Ok, I’m stumped.

Connecting Access FE to SQL SERVER BE
Connection is fine.
I can open and close it and other queries work fine.
I have only one problem with the SQL in one query

With rstRPT
    If FirstRecord = True Then
        .Open "SELECT Min([" & SourceTbl & "].[3Order ID]) AS MinOf3OrderID " & _
                "FROM [" & SourceTbl & "];" _
                , cnn1, adOpenUnspecified, adLockUnspecified
        Else
        .Open "SELECT Min([" & SourceTbl & "].[3Order ID]) AS MinOf3OrderID " & _
                "FROM [" & SourceTbl & "] " & _
                "WHERE ((([" & SourceTbl & "].[3Order ID]) > " & Chr(34) & vbMinOf3OrderID & Chr(34) & "));" _
                , cnn1, adOpenUnspecified, adLockUnspecified
    End If

End With

vbMinOf3OrderID = rstRPT!MinOf3OrderID

So, the first time I run through this, there is no problem (if FirstRecord = True).
It runs fine and vbMinOf3OrderID receives the value of “WS10001894”.

The problem comes on the NEXT run, (if FirstRecord = False)

It throws an error:

Run-time error ‘-2147217900 (80040e14)’:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column-name ’WS10001894’.

Any ideas why it wants to see the variable as a column name?

I have already tried several obvious modifications and none seems to work.

 

[Bilbo_Baggins_Esq]

ok, well, it turns out I found the answer....

for SQL Server, the general rule for string criteria is SINGLE QUOTES.

Thus, the correct and working code is as follows:

With rstRPT
    If FirstRecord = True Then
        .Open "SELECT Min([" & SourceTbl & "].[3Order ID]) AS MinOf3OrderID " & _
                "FROM [" & SourceTbl & "];" _
                , cnn1, adOpenUnspecified, adLockUnspecified
        Else
        .Open "SELECT Min([" & SourceTbl & "].[3Order ID]) AS MinOf3OrderID " & _
                "FROM [" & SourceTbl & "] " & _
                "WHERE ((([" & SourceTbl & "].[3Order ID]) > '" & vbMinOf3OrderID & "'));" _
                , cnn1, adOpenUnspecified, adLockUnspecified
    End If

End With

vbMinOf3OrderID = rstRPT!MinOf3OrderID