compare strings in SQL

[zhuanyi]

Hello,
I would like to put a restriction on the SQL statement such that if one particular field contains the character "MO", then we take in the record, else we ignore them. May I know how could this be done in the "WHERE" part of the SQL statement? I do not think SQL would read things like Instr(). Thanks!

Regards,
Anyi

 

[WayneRyan]

Anyi,

You can use:

Where [ThatField] <> 'MO'

Where [ThatField] Not Like '*MO*'

Where Instr([ThatField], "MO") > 0

all should be valid.

Wayne

 

[zhuanyi]

oh, i did not Instr will work...thx a lot!

 

[ed333]

Hi, I am having a similar problem in Access XP, and the above suggestions do not seem to be working.

I have a query that needs to select all records except those that have the word "OFF" in a certain field. This field either has "OFF" or is unpopulated, however, someone could put in an "OFF" and then later delete it, using a form.

When I put "Is Null" for the criteria of that field, I get results, but if someone has deleted the word "OFF", the field no longer seems to be null, and the results are not accurate.

The problem is that when I use the criteria of <>"OFF", I get no results at all! Same with Not Like "OFF" -- no records returned! I know this isn't right, but I can't seem to figure out how to wrangle these criteria fields to get the results I need!!

Thanks in advance for your help!

Ed

 

[MSAccessRookie]

Hi, I am having a similar problem in Access XP, and the above suggestions do not seem to be working.

I have a query that needs to select all records except those that have the word "OFF" in a certain field. This field either has "OFF" or is unpopulated, however, someone could put in an "OFF" and then later delete it, using a form.

When I put "Is Null" for the criteria of that field, I get results, but if someone has deleted the word "OFF", the field no longer seems to be null, and the results are not accurate.

The problem is that when I use the criteria of <>"OFF", I get no results at all! Same with Not Like "OFF" -- no records returned! I know this isn't right, but I can't seem to figure out how to wrangle these criteria fields to get the results I need!!

Thanks in advance for your help!

Ed

Comparing with "=" or "<>" does not take Null records into account. Try combining the two and see if the results are what you want.

((YourField <> "OFF") Or (YourField Is Null))
​

 

[ed333]

Thanks for the reply....I tried putting both criteria in a single box (I'm using the Design View) like this: (<>"OFF") OR (Not Null)

I'm still getting no records returned!!! All I want are records that do not have the word "OFF" in that one field, and I'm not sure why simple string comparison is returning no records! Arrgh!

 

[MSAccessRookie]

Thanks for the reply....I tried putting both criteria in a single box (I'm using the Design View) like this: (<>"OFF") OR (Not Null)

I'm still getting no records returned!!! All I want are records that do not have the word "OFF" in that one field, and I'm not sure why simple string comparison is returning no records! Arrgh!

Try expanding the entry to include the name of the Column that is being tested (Tablename.ColumnName).

 

[boblarson]

You can't use <>"OFF" to restrict if the full field is not OFF, but you are wanting if it appears anywhere in the field. You would use:

(Instr(1, [FieldNameHere], "OFF") = 0) OR ([FieldNameHere] Is Null)

 

[ed333]

Try expanding the entry to include the name of the Column that is being tested (Tablename.ColumnName).

Thanks for the suggestion...I tried that with no luck.

If I leave the criteria box blank for the field in question, I get results where (in the field I am concerned with) some of the records have "OFF" and some have no entry.

Why doesn't <>"OFF" give me all the records that don't have "OFF" in that field?
Is this a bug in access or am I grossly misunderstanding something?

 

[Banana]

Personally, I find it simpler to do this:

NOT ([FieldNameHere] & "") = 'OFF'

(or in case of where the word OFF may be part of a longer string**)

NOT ([FieldNameHere] & "") LIKE '*OFF*'

Either will get you all records without the 'OFF', including the nulls and ZLS.

**If this is the case, I would want to rethink the design. I'm not quite sure if that is actually the case and ignore that if this isn't; my apologies.

 

[boblarson]

Why doesn't <>"OFF" give me all the records that don't have "OFF" in that field?
Is this a bug in access or am I grossly misunderstanding something?

You are grossly misunderstanding. See my post above for what you would need to look for.

<> "OFF"

would be looking for the WHOLE FIELD having OFF as the value, not just part of it. You would need the Instr part I posted.

 

[ed333]

You can't use <>"OFF" to restrict if the full field is not OFF, but you are wanting if it appears anywhere in the field. You would use:

(Instr(1, [FieldNameHere], "OFF") = 0) OR ([FieldNameHere] Is Null)

Thanks!!! That worked! The full field is "OFF", but I tried your suggestion and seem to be getting the correct results now!

All that is in the field is either "OFF" or nothing at all. Are there other 'invisible' characters being added to the fields that don't show up as text that cause the simple comparison to not work?

I still don't understand why a simple comparison doesn't work, though.

 

[boblarson]

I still don't understand why a simple comparison doesn't work, though.

The field might have some hidden characters, perhaps. Hard to say without actually seeing it and playing with it.

 

[ed333]

The field might have some hidden characters, perhaps. Hard to say without actually seeing it and playing with it.

Well, at any rate, thanks! It's working for now!