SQL statement does not return correct results

[lwilly]

I am trying to search a table for a record based upon a variable I supply. Here is the code I am using;

Dim strCust_ID As String
Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim EQP As String

Set db = CurrentDb
Set rst = db.OpenRecordset("qryEQP&quot
strCust_ID = Me.DistNum

strSQL = "SELECT [CUST_CLAS2] FROM [tblEQP]"
strSQL = strSQL & "WHERE [CUST_ID] = " & strCust_ID
EQP = rst![CUST_CLAS2]

Me.txtEQPStatus = EQP

This returns " " but if I copy the SQL statement from the debug window and paste it into a new query it returns "C" which is correct.
What am I doing wrong? [sig][/sig]

 

[jimmythegeek]

Try this:

===================
Dim db As Database
Dim rst As Recordset
Dim strSQL As String

strSQL = "SELECT CUST_CLAS2 FROM tblEQP WHERE((([CUST_ID]) = '" & Me.DistNum & "'));"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then
Me.txtEQPStatus = rst![CUST_CLAS2]
End If

rst.Close
db.Close
==================== [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[lwilly]

Thank you, it works. I would appreciate it if you could explain what I was doing incorrectly. I can see the physical differences but it seems like mine should have worked. Regardless I appreciate your help.

Lonnie [sig][/sig]

 

[jimmythegeek]

Several things actually:

1) The recordset you were opening was qryEQP.
2) You were setting strSQL equal a string, but that's it. You were never using that string anywhere.
3) You don't need to set fields on your form = to variables, you can refer to them directly.
4) The Line (Me.txtEQPStatus = EQP) was in essence setting your status field = to the first record in you open recordset, which was qryEQP.
5) If the criteria in your SQL string, is a string, then you need single quotes (' ') around the criteria.

The difference is that I opened a recordset directly to the record you were looking for using the strSQL statement. Then if there was a record, just set the value in your field = to a value in the recordset.

Hope this helps. [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]