Problem with select criteria - error #3464

[Guest]

I have a table with a 4 character code defined as text in the table. At
present all the codes are 4 digit numbers, but I do not want to restrict this
field to numerica only. I am trying to select a record based on the 4
character code.
I defined a text box on a form and a Find command button. The code is as
follows:
dim rs as recordset
set rs = CurrentDB.Openrecordset("Select * from [Benefits_Contacts] " & _
"where [Benefits_Contacts.Code] = " & txtFind.value)
I get error 3464 data type mismatch in Criteria expression.
I changed the data type of Benefits_Contacts.Code to integer and the select
worked. However, I don't want this field defined as integer. txtFind is
just an ordinary text box.

 

[Guest]

I haven't tested it, but it looks like your statement sees is as numeric, so
the format function here will force it to string and ensure it is 4
characters long. Note that the way I am doing it assumes that a number like
321 would be in the code field as "0321". If this is not correct, and it
would be stored as "321 " then it would be
Cstr(txtFind.value ) or Format(txtFind.value,"###0")

set rs = CurrentDB.Openrecordset("Select * from [Benefits_Contacts] " & _
"where [Benefits_Contacts.Code] = " &
Format(txtFind.value,"0000"))

 

[Tim Ferguson]

Note that the way I am doing it assumes that a number like
321 would be in the code field as "0321". If this is not correct, and
it would be stored as "321 " then it would be
Cstr(txtFind.value ) or Format(txtFind.value,"###0")

Half way right: the string as a whole needs to match, but it also needs
to be formatted as a string, i.e. with quote marks round it:

"... WHERE Code = " & Format(txtFind, "\""-0000\""")

or, using single quotes for SQL Server/ ADO/ etc

"... WHERE Code = " & Format(txtFind, "\'0000\'")

It's always helpful to debug any SQL that is being created on the fly:

' do all the putting together separately
strSQL = "SELECT etc, etc" & Format(etc, etc)

' The next line saves lives. Comment it out once
' you _know_ the code is okay
Debug.Assert MsgBox(strSQL, vbYesNo, "Is this okay?") = vbYes

' Now try it on the engine
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)


Hope that helps


Tim F

 

[Guest]

You are correct. I should have caught that.

Half way right: the string as a whole needs to match, but it also needs
to be formatted as a string, i.e. with quote marks round it:

"... WHERE Code = " & Format(txtFind, "\""-0000\""")

or, using single quotes for SQL Server/ ADO/ etc

"... WHERE Code = " & Format(txtFind, "\'0000\'")

It's always helpful to debug any SQL that is being created on the fly:

' do all the putting together separately
strSQL = "SELECT etc, etc" & Format(etc, etc)

' The next line saves lives. Comment it out once
' you _know_ the code is okay
Debug.Assert MsgBox(strSQL, vbYesNo, "Is this okay?") = vbYes

' Now try it on the engine
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)


Hope that helps


Tim F