Recordset FindFirst

[Guest]

Below is my procedure for matching user input against a table's contents. The FindFirst DAO recordset method is giving me the error "Data type mismatch in criteria expression". I don't understand why this is happening. The table's field referred to is a text field and the variable from user input is a text string as well. Where am I going wrong

Private Sub GLCrAcctASEND_Exit(Cancel As Integer
Dim db As DAO.Databas
Dim rs As DAO.Recordse
Set db = Access.CurrentD
Set rs = db.OpenRecordset("tbl_GLAcctsSEND-LdgA", dbOpenSnapshot
rs.FindFirst "[GLIntAcctASEND] = " & GLCrAcctASEN
If rs.NoMatch The
. .
Cancel = Tru
End I
End Sub

 

[Dan Artuso]

Hi,
String criteria must be delimited with quotes, dates with # and numbers
require no delimiter:
rs.FindFirst "[GLIntAcctASEND] = '" & GLCrAcctASEND & "'"

--
HTH
Dan Artuso, Access MVP

Below is my procedure for matching user input against a table's contents. The FindFirst DAO recordset method is giving me the

error "Data type mismatch in criteria expression". I don't understand why this is happening. The table's field referred to is a
text field and the variable from user input is a text string as well. Where am I going wrong?

Private Sub GLCrAcctASEND_Exit(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = Access.CurrentDb
Set rs = db.OpenRecordset("tbl_GLAcctsSEND-LdgA", dbOpenSnapshot)
rs.FindFirst "[GLIntAcctASEND] = " & GLCrAcctASEND
If rs.NoMatch Then
. . .
Cancel = True
End If
End Sub

 

[Tim Ferguson]

Set rs = db.OpenRecordset("tbl_GLAcctsSEND-LdgA", dbOpenSnapshot)
rs.FindFirst "[GLIntAcctASEND] = " & GLCrAcctASEND

or even use the database:

Set rs = db.OpenRecordset( _
"SELECT Something FROM tbl_GLAcctsSEND-LdgA " & _
"WHERE GLIntAcctASEND = """ & GLCrAcctASEND & """", _
dbOpenSnapshot, dbForwardOnly)

If rs.BOF Then
' it wasn't there

HTH


Tim F

 

[Dan Artuso]

Hi,
The argument to FindFirst is a string, so whatever you use must evaluate to
a string.
It might be easier for you to see if you assign the criteria to a variable,
then you can see what
the 'result' will look like.
In your case, you want a string something like :
"[GLIntAcctASEND] = 'somecriteria'"

The way you originally had it, it evaluated to:
"[GLIntAcctASEND] = somecriteria"

You can see that there are no single quotes around your criteria which was
why you were
getting the error message.
The sample I gave you will evaluate to:
"[GLIntAcctASEND] = 'somecriteria'"

The ampersand at the end is concatenating the second single quote onto the
criteria.

HTH
Dan Artuso, MVP

Don,
Thanks for the help. So, the whole FirstFind "argument" has to be in

double quotes. But why can't the string criteria just be in single quotes?
Also, why is the second ampersand necessary at the end? Pardon my
ignorance. I haven't worked with Recordet code much yet.

ctdak

----- Dan Artuso wrote: -----

Hi,
String criteria must be delimited with quotes, dates with # and numbers
require no delimiter:
rs.FindFirst "[GLIntAcctASEND] = '" & GLCrAcctASEND & "'"

--
HTH
Dan Artuso, Access MVP

Below is my procedure for matching user input against a table's

contents. The FindFirst DAO recordset method is giving me the

error "Data type mismatch in criteria expression". I don't

understand why this is happening. The table's field referred to is a

text field and the variable from user input is a text string as well. Where am I going wrong?

Private Sub GLCrAcctASEND_Exit(Cancel As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = Access.CurrentDb
Set rs = db.OpenRecordset("tbl_GLAcctsSEND-LdgA", dbOpenSnapshot)
rs.FindFirst "[GLIntAcctASEND] = " & GLCrAcctASEND
If rs.NoMatch Then
. . .
Cancel = True
End If
End Sub

 

[Guest]

Dan
Thanks for the explanation. Very helpful
ctda

----- Dan Artuso wrote: ----

Hi
The argument to FindFirst is a string, so whatever you use must evaluate t
a string
It might be easier for you to see if you assign the criteria to a variable
then you can see wha
the 'result' will look like
In your case, you want a string something like
"[GLIntAcctASEND] = 'somecriteria'

The way you originally had it, it evaluated to
"[GLIntAcctASEND] = somecriteria

You can see that there are no single quotes around your criteria which wa
why you wer
getting the error message
The sample I gave you will evaluate to
"[GLIntAcctASEND] = 'somecriteria'

The ampersand at the end is concatenating the second single quote onto th
criteria

HT
Dan Artuso, MV

Don
Thanks for the help. So, the whole FirstFind "argument" has to be i

double quotes. But why can't the string criteria just be in single quotes
Also, why is the second ampersand necessary at the end? Pardon m
ignorance. I haven't worked with Recordet code much yet

ctda

----- Dan Artuso wrote: ----
Hi

String criteria must be delimited with quotes, dates with # an number
require no delimiter
rs.FindFirst "[GLIntAcctASEND] = '" & GLCrAcctASEND & "'

-

HT
Dan Artuso, Access MV

Below is my procedure for matching user input against a table'

contents. The FindFirst DAO recordset method is giving me th

error "Data type mismatch in criteria expression". I don'

understand why this is happening. The table's field referred to is

text field and the variable from user input is a text string as well Where am I going wrong

Private Sub GLCrAcctASEND_Exit(Cancel As Integer

Dim db As DAO.Databas
Dim rs As DAO.Recordse
Set db = Access.CurrentD
Set rs = db.OpenRecordset("tbl_GLAcctsSEND-LdgA" dbOpenSnapshot
rs.FindFirst "[GLIntAcctASEND] = " & GLCrAcctASEN
If rs.NoMatch The
. .
Cancel = Tru
End I
End Su