Need help with a definition query

[TheoFaull]

I have a point shapefile with 2 columns. Each column contains numbers relating to price increases of a service. One column is how much the price has risen by (£) and the second column is what percentage it has risen by, compared to the original price.

I want the layer in ArcMap to only display points where the price rise is above 10 (£) AND the percentage is over 50 (%).

Using the definition query tab, perhaps it would look like:

"PRICE_RISE" > 10 AND "PERCENTAGE_RISE" > 50

however, this returns an invalid sql statement...

[WesMiller]

Due to an alpha numeric sort versus a numeric sort. alphanumeric = 1 10 11 12 13 14 15 16 17 18 19 2 20 21 ..., numeric sort = 1 2 3 4 5 6 7 8 9 10 ...

[TheoFaull]

Another definition query question. My point shapefile contains a string field ("Tag") populated with values:

'AdHoc'

'AdHoc[followed by ref numbers]'

'Adhoc'

'Adhoc[followed by ref numbers]'

'TreeSurv'

etc. etc.

I wanted to split the layer so one only displayed points where the "Tag" field contains only AdHoc/Adhoc values.

"Tag" LIKE 'AdHoc%' found all these values but it missed out 'Adhoc'. Ok so it's case sensitive this query business. So...

"Tag" LIKE 'AdHoc%' AND "TAG" LIKE 'Adhoc%' BUT this returned no results.

What am I doing wrong here?

[WesMiller]

The use of AND requires both to be true for it to return anything. You could try OR or "Tag" LIKE 'Ad_oc%'

[TheoFaull]

excellent help again Wes Miller​ . see my post below though... hmmm

[TheoFaull]

Ok so it turns out replacing AND with OR fixes this. However this is the confusing bit...

I have another layer with a definition query which excludes certain values being shown, but uses the AND function. and it works!

"OWNERNAME" <> 'S_HOUSING' AND "OWNERNAME" <> 'R_LAND' AND "OWNERNAME" <> 'HOUSING ASSOCIATION'

This query successfully filters our any entries where the OWNERNAME field contains S_HOUSING, or R_LAND or HOUSING ASSOCIATION.

The OWNERNAME field is a string too FYI. How come AND works in this instance but not in the previous?

[WesMiller]

In this case <> means does not equal. So if "OWNERNAME"  does not equal 'S_HOUSING' AND "OWNERNAME" does not equal 'HOUSING ASSOCIATION' when both queries evaluate to true then select it.