SQL Statements using 'Like'

[JohnBrand]

Is it possible to use the wildcard '%' on a numeric field (integer, double, etc) while using the 'like' operator?

example

I would like to select all numbers that end in zero.  I know that

"ELEVATION" LIKE '%0'

works for strings.  I would like to be able to use the expression on a numeric field.

[NelsonDe_Miranda]

To my knowledge there isn't a simple straight forward way as that to select values from a number field:

However, you can:

1) Convert your "numeric field to text" - as you mentioned above

OR

Use Mod Calculations and calculate a new field which will determine whether or not your number ends in a 0.

i.e.

MOD ( "Elevation" , 10) = 0
What this simple calc does is it selects all the values in the elevation that are divisible by 10.
i.e. numbers ending in 0.

Cheers,

Nelson

[PaulHuffman]

So why when I try MOD ( "intContour" , 1000) = 0 in a Select by Attributes query on a personal geodatabase feature class, I can only get "Syntax error (missing operator)"  no matter where I place " or spaces or brackets"? It didn't seem to matter that I created "intContour" as an interger field from "Contour", a double field.

[NelsonDe_Miranda]

So using the same equation you just posted I was able to select contours that were divisible by 1000.

See screenshots below for the Query Box as well as the output result.

If you replicate what I did and still fail try flipping the equation ie. (this did not work for me but the original poster of the thread said it worked for him)

0 = [ELEVATION] MOD 1000
Source:http://lists.directionsmag.com/discussion/read.php?f=26&t=30698&a=1

[PaulHuffman]

Still didn't work. This is ArcGIS 10, sp3.  Is there some different syntax MS Access requires because this is from a personal geodatabase feature class?

However, the flipped equation did work for me!

[NelsonDe_Miranda]

In Summary:

FGDB users use: MOD ( "Elevation" , 1000) = 0
Personal geodatabase users use: 0 = [ELEVATION] MOD 1000

The syntax difference is because MSAccess does not understand the MOD Operator in the same way the file geodatabase does.

Here is a link to the MSOffice help page showing the proper syntax for MOD within an Access environment.
http://office.microsoft.com/en-us/access-help/HV080756430.aspx

[PaulHuffman]

Thanks - Nelson.  When I typed MOD into MS Access Help Search I got pages of unrelated crap.

[intContour] MOD 1000 = 0  worked for me as well.  Didn't matter in I used "Contour", the double version of my item or "intContour" the short integer.