Query Returning EoF in VBA but not elsewhere

[lotharious]

    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim s As String
    
    Set cn = CurrentProject.Connection
    s = "SELECT InvMatInProcess.Cut FROM InvMatInProcess "
    s = s & "WHERE (((InvMatInProcess.Item)='" & Me![Material] & "') AND ((InvMatInProcess.PO)="
    s = s & Me![PO] & "))"
    Debug.Print s
    rs.Open s, cn
    If IsNull(rs!Cut) Or rs.EOF Then
        Me!CutAmt = "N/A"
    Else
        Me!CutAmt = rs!Cut
    End If
    rs.Close

Debug output

SELECT InvMatInProcess.Cut FROM InvMatInProcess WHERE (((InvMatInProcess.Item)='I-MW1/4') AND ((InvMatInProcess.PO)=576))

The Query works correctly and gives me the accurate number for InvMatInProcess.Cut if I run the query in design mode.
However the vba code is returning EOF for the recordset.

Im not sure where to look for the problem. Other code that is similar works fine for me. Any ideas?

 

[Zukkster]

What sort of DB are you connecting to SQL server?

I think it maybe the ' quotes you've used in the WHERE clause of your VBA code aren't being picked up.

In the past this has caused me problems from VBA to SQL server. It depends on how you've got your quoted identifier settings. Try """

 

[cmmrfrds]

Debug.Print "eof = " rs.EOF
If rs.EOF or NZ(rs!Cut,0)=0 Then
Me!CutAmt = "N/A"
Else
Me!CutAmt = rs!Cut
End If

 

[JerryKlmns]

A similar approach

Dim rs As ADODB.Recordset
Dim s As String
    
s = "SELECT [Cut] FROM InvMatInProcess " & _
    "WHERE (([Item]='" & Me!Material & "') " & _
    "AND    (PO=" & Me!PO & "));"
Debug.Print s
Set rs = CurrentProject.Connection.Execute s
If (rs.Cut & "" ="") or rs.EOF or rs.BOF Then
  Me!CutAmt = "N/A"
Else
  Me!CutAmt = rs.Cut
End If
rs.Close
Set rs =Nothing