data-type mismatch in criteria expression (querying autonumber field)

[Todd]

HI there, the following code is returning the error, data-
type mismatch in criteria expression. I assume that my
Autonumber field has a diferent kind of datatype ? Can I
base criteria on that field? HELP.

Dim strsql As String
Dim findthis As Integer
Dim db As Database
Call CurrentDb.Execute("delete * from tbltemp2")
findthis = Me.Text70
If IsNull(findthis) Then
MsgBox "You must Enter an IPR into the Field To Search for
it.", vbCritical, "Search"
Exit Sub
Else
Set db = CurrentDb
strsql = "INSERT INTO tbltemp2 (IPRNumber, firstname,
lastname, address, account_number) Select tblhistory.
[aunIPRNumber], tblHistory.[strcustomernamefirst],
tblhistory.[strcustomernamelast], tblhistory.
[strcustomeraddress], tblhistory.[straccountnumber] " & _
"FROM tblHistory " & _
"WHERE tblHistory.[aunIPRNumber]= '" & findthis & "'"
Call CurrentDb.Execute(strsql)

Thanks.
Todd

 

[George Nicholson]

"WHERE tblHistory.[aunIPRNumber]= '" & findthis & "'"

is ok *IF* aunIPRNumber is a text field. If it is indeed a number field,
you should use

"WHERE tblHistory.[aunIPRNumber]= " & findthis

 

[Todd]

Thanks George, it works now. Why the difference in Text
and Numbers ?
Todd

-----Original Message-----

"WHERE tblHistory.[aunIPRNumber]= '" & findthis & "'"

is ok *IF* aunIPRNumber is a text field. If it is indeed a number field,
you should use

"WHERE tblHistory.[aunIPRNumber]= " & findthis

--
George Nicholson

Remove 'Junk' from return address.

HI there, the following code is returning the error, data-
type mismatch in criteria expression. I assume that my
Autonumber field has a diferent kind of datatype ? Can I
base criteria on that field? HELP.

Dim strsql As String
Dim findthis As Integer
Dim db As Database
Call CurrentDb.Execute("delete * from tbltemp2")
findthis = Me.Text70
If IsNull(findthis) Then
MsgBox "You must Enter an IPR into the Field To Search for
it.", vbCritical, "Search"
Exit Sub
Else
Set db = CurrentDb
strsql = "INSERT INTO tbltemp2 (IPRNumber, firstname,
lastname, address, account_number) Select tblhistory.
[aunIPRNumber], tblHistory.[strcustomernamefirst],
tblhistory.[strcustomernamelast], tblhistory.
[strcustomeraddress], tblhistory.[straccountnumber] " & _
"FROM tblHistory " & _
"WHERE tblHistory.[aunIPRNumber]= '" & findthis & "'"
Call CurrentDb.Execute(strsql)

Thanks.
Todd

.

 

[George Nicholson]

When constructing an SQL statement, text criteria are expected to be
enclosed in quotes, number criteria are not. Your original code contained
the equivalent of:

WHERE aunIPRNumber = 'mynumber' (note the single quotes)

but, assuming aunIPRNumber contained numerical values, you needed it to be:

WHERE aunIPRNumber = mynumber (no quotes)

--
George Nicholson

Remove 'Junk' from return address.

Thanks George, it works now. Why the difference in Text
and Numbers ?
Todd

-----Original Message-----

"WHERE tblHistory.[aunIPRNumber]= '" & findthis & "'"

is ok *IF* aunIPRNumber is a text field. If it is indeed a number field,
you should use

"WHERE tblHistory.[aunIPRNumber]= " & findthis

--
George Nicholson

Remove 'Junk' from return address.

HI there, the following code is returning the error, data-
type mismatch in criteria expression. I assume that my
Autonumber field has a diferent kind of datatype ? Can I
base criteria on that field? HELP.

Dim strsql As String
Dim findthis As Integer
Dim db As Database
Call CurrentDb.Execute("delete * from tbltemp2")
findthis = Me.Text70
If IsNull(findthis) Then
MsgBox "You must Enter an IPR into the Field To Search for
it.", vbCritical, "Search"
Exit Sub
Else
Set db = CurrentDb
strsql = "INSERT INTO tbltemp2 (IPRNumber, firstname,
lastname, address, account_number) Select tblhistory.
[aunIPRNumber], tblHistory.[strcustomernamefirst],
tblhistory.[strcustomernamelast], tblhistory.
[strcustomeraddress], tblhistory.[straccountnumber] " & _
"FROM tblHistory " & _
"WHERE tblHistory.[aunIPRNumber]= '" & findthis & "'"
Call CurrentDb.Execute(strsql)

Thanks.
Todd

.