SELECT WHERE Statement

[Flannel]

I'm trying to write a SELECT statement that uses WHERE to
find a match. The WHERE piece is pointing to a list box on
a form.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = ("SELECT * FROM Table WHERE TableField = '" &
Forms!MainForm!ListBox & "'")
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

I'm getting a "Data Type mismatch in criteria expression"
error.

I have narrowed the issue down to my WHERE statement piece.

Even if I try to hard code something in it doesn't work:
"SELECT * FROM Table WHERE TableField = 'Testing'"
Thanks

 

[dan artuso]

Hi,
It sounds like TableField is not a string. What data type is it exactly?

 

[John Vinson]

I'm trying to write a SELECT statement that uses WHERE to
find a match. The WHERE piece is pointing to a list box on
a form.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = ("SELECT * FROM Table WHERE TableField = '" &
Forms!MainForm!ListBox & "'")
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

I'm getting a "Data Type mismatch in criteria expression"
error.

I have narrowed the issue down to my WHERE statement piece.

Even if I try to hard code something in it doesn't work:
"SELECT * FROM Table WHERE TableField = 'Testing'"
Thanks

Might this TableField be a LOOKUP field?

If so, the misleading, misdesigned Lookup Wizard has claimed yet
another victim. The table field actually contains a (concealed)
number, a link to the lookup table.

You can get the query to work by including the numeric ID field as the
Bound Column of the listbox (it can be set to zero width so the user
doesn't see it) and losing the quotemarks.