Problem with Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

George R - 18 May 2008 21:40 GMT

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

Reply to this Message

Rick Brandt - 18 May 2008 22:20 GMT

> The following code produces the desired count of records when the
> [PermitNumber] is <10000. It produces an error "type mismatch in
[quoted text clipped - 7 lines]
> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

George R - 19 May 2008 02:41 GMT

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
[quoted text clipped - 13 lines]
>
> Of course this will be an alphabetical comparison, not numeric.

Reply to this Message

Paolo - 19 May 2008 08:42 GMT

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
[quoted text clipped - 21 lines]
> >
> > Of course this will be an alphabetical comparison, not numeric.

Reply to this Message

Linq Adams - 19 May 2008 13:03 GMT

Using

Val(myNum)

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

Signature

There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Reply to this Message