Problem with Criteria

[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

 

[Rick Brandt]

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

< 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.

 

[George R]

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.

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

< 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.

 

[Paolo]

Hi George R,

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

HTH Paolo

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.

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

< 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.

 

[Linq Adams via AccessMonster.com]

Using

Val(myNum)

will allow string/text data to be used as a number, assuming that the text
can be interpreted as a number.