Str(NZ....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following code

rstAmort.FindFirst "[LoanNumber] = " & Str(Nz(Forms![Loan
Information]!LoanNumber, 0))


but get "Data Type Mismatch in criteria expression" Runtime error 3464"

I've checked the data types for both the variable and for the field and they
are both Type 8...so... where's the mismatch.... possibly in the 0 (zero) at
the end of the expression?
 
Could be a couple of things, so break the line into 2:
Dim strWhere As String
strWhere = "[LoanNumber] = " & Nz(Forms![Loan Information]![LoanNumber],
0)
rstAmort.FindFirst strWhere

At least you will know which part is failing.
 
I'm not sure what you mean when you say that you 'checked the data types for
both the variable and for the field and they are both Type 8'. I looked up
the DAO DataTypeEnum, and I see that the member of that enum with the
literal value 8 is Date, which doesn't sound like a very appropriate data
type for a loan number. Perhaps you could clarify what the data type of the
field 'LoanNumber' is?
 
With the code you supplied I find that
strWhere = "LoanNumber = 1132"
and I get the same error at the rstAmort.FindFirst strWhere line

When I ask to ? rstAmort!LoanNumber in the Immediate window I get 1100 which
is the first record in the recordset so I know the recordset is populated. It
must be in the FindFirst command?


Allen Browne said:
Could be a couple of things, so break the line into 2:
Dim strWhere As String
strWhere = "[LoanNumber] = " & Nz(Forms![Loan Information]![LoanNumber],
0)
rstAmort.FindFirst strWhere

At least you will know which part is failing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

John@OAI said:
I have the following code

rstAmort.FindFirst "[LoanNumber] = " & Str(Nz(Forms![Loan
Information]!LoanNumber, 0))


but get "Data Type Mismatch in criteria expression" Runtime error 3464"

I've checked the data types for both the variable and for the field and
they
are both Type 8...so... where's the mismatch.... possibly in the 0 (zero)
at
the end of the expression?
Click to expand...
Click to expand...
 
I should have said I checked the VarType of each. The data type of the field
LoanNumber is Text and I set the variable type of strWhere by using Dim
strWhere as String

Brendan Reynolds said:
I'm not sure what you mean when you say that you 'checked the data types for
both the variable and for the field and they are both Type 8'. I looked up
the DAO DataTypeEnum, and I see that the member of that enum with the
literal value 8 is Date, which doesn't sound like a very appropriate data
type for a loan number. Perhaps you could clarify what the data type of the
field 'LoanNumber' is?

--
Brendan Reynolds
Access MVP

John@OAI said:
I have the following code

rstAmort.FindFirst "[LoanNumber] = " & Str(Nz(Forms![Loan
Information]!LoanNumber, 0))


but get "Data Type Mismatch in criteria expression" Runtime error 3464"

I've checked the data types for both the variable and for the field and
they
are both Type 8...so... where's the mismatch.... possibly in the 0 (zero)
at
the end of the expression?
Click to expand...
Click to expand...
 
That's the problem, then, you need quotes around text values ...

rstAmort.FindFirst "[LoanNumber] = '" & Nz(Forms![Loan
Information]!LoanNumber, "0") & "'"

That's a single quote followed by a double quote after the = sign, and a
single quote between two double quotes at the end. Also note the quotes
around the 0. I took out the call to the Str function as it is not doing
anything useful in this context.

--
Brendan Reynolds
Access MVP

John@OAI said:
I should have said I checked the VarType of each. The data type of the
field
LoanNumber is Text and I set the variable type of strWhere by using Dim
strWhere as String

Brendan Reynolds said:
I'm not sure what you mean when you say that you 'checked the data types
for
both the variable and for the field and they are both Type 8'. I looked
up
the DAO DataTypeEnum, and I see that the member of that enum with the
literal value 8 is Date, which doesn't sound like a very appropriate data
type for a loan number. Perhaps you could clarify what the data type of
the
field 'LoanNumber' is?

--
Brendan Reynolds
Access MVP

John@OAI said:
I have the following code

rstAmort.FindFirst "[LoanNumber] = " & Str(Nz(Forms![Loan
Information]!LoanNumber, 0))


but get "Data Type Mismatch in criteria expression" Runtime error 3464"

I've checked the data types for both the variable and for the field and
they
are both Type 8...so... where's the mismatch.... possibly in the 0
(zero)
at
the end of the expression?
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Brendan, that did it! I simply MUST remember this!!!

Brendan Reynolds said:
That's the problem, then, you need quotes around text values ...

rstAmort.FindFirst "[LoanNumber] = '" & Nz(Forms![Loan
Information]!LoanNumber, "0") & "'"

That's a single quote followed by a double quote after the = sign, and a
single quote between two double quotes at the end. Also note the quotes
around the 0. I took out the call to the Str function as it is not doing
anything useful in this context.

--
Brendan Reynolds
Access MVP

John@OAI said:
I should have said I checked the VarType of each. The data type of the
field
LoanNumber is Text and I set the variable type of strWhere by using Dim
strWhere as String

Brendan Reynolds said:
I'm not sure what you mean when you say that you 'checked the data types
for
both the variable and for the field and they are both Type 8'. I looked
up
the DAO DataTypeEnum, and I see that the member of that enum with the
literal value 8 is Date, which doesn't sound like a very appropriate data
type for a loan number. Perhaps you could clarify what the data type of
the
field 'LoanNumber' is?

--
Brendan Reynolds
Access MVP

I have the following code

rstAmort.FindFirst "[LoanNumber] = " & Str(Nz(Forms![Loan
Information]!LoanNumber, 0))


but get "Data Type Mismatch in criteria expression" Runtime error 3464"

I've checked the data types for both the variable and for the field and
they
are both Type 8...so... where's the mismatch.... possibly in the 0
(zero)
at
the end of the expression?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top