query expression

[Guest]

I am creating a query that only shows records where the last two digits of
one of the fields is between 00 and 49. Any suggestions on how to word this
criteria expression would be appreciated.

 

[Marshall Barton]

I am creating a query that only shows records where the last two digits of
one of the fields is between 00 and 49. Any suggestions on how to word this
criteria expression would be appreciated.

Creat a new calculated field:
idno: CInt(Right(thefield, 2)
and use the criteria:
Between 0 And 49

 

[Guest]

In the field heading of the QBE grid, put something like:
Exp1:Right(Int([TheFieldName]), 2)

In the criteria put:
Between 0 And 49

The SQL would look something like:

SELECT JaySeq.*
FROM JaySeq
WHERE Right(Int([JaySeq]),2) Between 0 And 49;

 

[John Spencer]

Is this a text field or is it a number field? Can it ever be null?

Number field:
WHERE Field Mod 100 between 0 and 49

Text field: (and probably number field since Access will attempt to convert
the number field to a string)
WHERE Field like "*[0-4][0-9]"
WHERE Left(field,2) between "00" and "50"
WHERE Val(Left(field,2)) Between 00 and 49 --- will blow up with null values
(error 94 invalid use of null)

 

[John Spencer]

Whoops! Those "Left" should read "Right".

WHERE Right(field,2) between "00" and "50"
WHERE Val(Right(field,2)) Between 00 and 49

Is this a text field or is it a number field? Can it ever be null?

Number field:
WHERE Field Mod 100 between 0 and 49

Text field: (and probably number field since Access will attempt to
convert the number field to a string)
WHERE Field like "*[0-4][0-9]"
WHERE Left(field,2) between "00" and "50"
WHERE Val(Left(field,2)) Between 00 and 49 --- will blow up with null
values (error 94 invalid use of null)

I am creating a query that only shows records where the last two digits of
one of the fields is between 00 and 49. Any suggestions on how to word
this
criteria expression would be appreciated.