Problem with Criteria

  • Thread starter Thread starter George R
  • Start date Start date
G

George R

The following code produces the desired count of records when the
[PermitNumber] is <10000. It produces an error "type mismatch in criteria
expression" when [PermitNumber] is 10000 or greater. "most" is determined by
the length of the current Permit Number. Permit Number is a text field.

myCriteria = "Left([PermitNumber]," & most & ") = " & myNum
myCount = DCount("PermitNumber", "Property Master", myCriteria)

I would appreciate any assistance anyone could provide.
Thank you
 
George said:
The following code produces the desired count of records when the
[PermitNumber] is <10000. It produces an error "type mismatch in
criteria expression" when [PermitNumber] is 10000 or greater. "most"
is determined by the length of the current Permit Number. Permit
Number is a text field.

myCriteria = "Left([PermitNumber]," & most & ") = " & myNum
myCount = DCount("PermitNumber", "Property Master", myCriteria)

I would appreciate any assistance anyone could provide.
Thank you
Click to expand...

< 10000 and > 10000 is not meaningful on a Text field. You need to use...

< "10000" and > "10000"

Of course this will be an alphabetical comparison, not numeric.
 
I believe it is now a string. myNum is declared as a string and it appears as
"10000" (with the quotes) when the pointer rests over it. myCriteria appears
as "Left([PermitNumber],5) = 10000" with the quotes. The data type error
occurs.
With myNum as 9898, it similarly appears as "9898" and myCriteria appears as
"Left([PermritNumber],4) = 9898" (again, with the quotes) and there is no
error.

Rick Brandt said:
George said:
The following code produces the desired count of records when the
[PermitNumber] is <10000. It produces an error "type mismatch in
criteria expression" when [PermitNumber] is 10000 or greater. "most"
is determined by the length of the current Permit Number. Permit
Number is a text field.

myCriteria = "Left([PermitNumber]," & most & ") = " & myNum
myCount = DCount("PermitNumber", "Property Master", myCriteria)

I would appreciate any assistance anyone could provide.
Thank you
Click to expand...

< 10000 and > 10000 is not meaningful on a Text field. You need to use...

< "10000" and > "10000"

Of course this will be an alphabetical comparison, not numeric.
Click to expand...
 
Hi George R,

try in this way
myCriteria = "Left([PermitNumber]," & most & ") = '" & myNum & "'"

HTH Paolo

George R said:
I believe it is now a string. myNum is declared as a string and it appears as
"10000" (with the quotes) when the pointer rests over it. myCriteria appears
as "Left([PermitNumber],5) = 10000" with the quotes. The data type error
occurs.
With myNum as 9898, it similarly appears as "9898" and myCriteria appears as
"Left([PermritNumber],4) = 9898" (again, with the quotes) and there is no
error.

Rick Brandt said:
George said:
The following code produces the desired count of records when the
[PermitNumber] is <10000. It produces an error "type mismatch in
criteria expression" when [PermitNumber] is 10000 or greater. "most"
is determined by the length of the current Permit Number. Permit
Number is a text field.

myCriteria = "Left([PermitNumber]," & most & ") = " & myNum
myCount = DCount("PermitNumber", "Property Master", myCriteria)

I would appreciate any assistance anyone could provide.
Thank you
Click to expand...

< 10000 and > 10000 is not meaningful on a Text field. You need to use...

< "10000" and > "10000"

Of course this will be an alphabetical comparison, not numeric.
Click to expand...
Click to expand...
 
Back
Top