Is Null Not working in access query

[Japn]

Hi there I Created a access database to sort my sales territory by postcode and added a status field to put R for removed or certain language to make sure these" records dont show when running the query I used "is null" without quotes in the status field. this used to work or at least it seamed to but noticed lately that when I update the table with a R in status column then run the query it also Displays all the fields including those with entries in the status field. Why is this, Does the status field have to appear before any other field in the query?


I only want the query to display records with blank status field.

Copy Of SQL Below:

SELECT TerritoryTBL.[Flat/House Number], TerritoryTBL.[House Name], TerritoryTBL.Area, TerritoryTBL.Road, TerritoryTBL.Parish, TerritoryTBL.Island, TerritoryTBL.[Latitude N], TerritoryTBL.[Longitude W], (+[Flat/House Number]+", ") & (+[House Name]+",") & (" "+[Area]+",") & " " & [Road] AS FullAddress, TerritoryTBL.Postcode, TerritoryTBL.[Status/Type]
FROM TerritoryTBL
WHERE (((TerritoryTBL.Postcode)="JE2 3ZU" Or (TerritoryTBL.Postcode)="JE2 3ZT") AND ((TerritoryTBL.[Status/Type]) Is Null)) OR (((TerritoryTBL.Postcode)="JE2 3ZG" Or (TerritoryTBL.Postcode)="JE2 3ZL")) OR (((TerritoryTBL.Postcode)="JE2 3ZD"));

Please Help. Note I did also try Is Null Or "" same result.

[pbaldy]

First thing I would do is get rid of all the extra parentheses that Access likes to add, and then add them to specify your logic. When you mix And & Or, you should make sure the logic is clear.

[John_G]

Looking at the Where clause you have shown, the only time the Status field will have any limiting effect is for postal codes "JE2 3ZU" and "JE2 3ZT". All records will show for any of the other postal codes, regardless of what the status is.

[Japn]

Looking at the Where clause you have shown, the only time the Status field will have any limiting effect is for postal codes "JE2 3ZU" and "JE2 3ZT". All records will show for any of the other postal codes, regardless of what the status is.

Thank you John G for your quick responce, is there anything I can do differently to correct this so that whenever anything is input into status field the record is removed from the query result.

[June7]

Maybe:

WHERE [Status/Type] Is Null AND Postcode IN ("JE2 3ZU", "JE2 3ZT", "JE2 3ZG", "JE2 3ZL", "JE2 3ZD");

Recommend no spaces or special characters/punctuation (underscore is exception) in naming convention.

[Japn]

Looking at the Where clause you have shown, the only time the Status field will have any limiting effect is for postal codes "JE2 3ZU" and "JE2 3ZT". All records will show for any of the other postal codes, regardless of what the status is.

So can I just Clarify my Problem, as I understand it, the reason that my "Is Null" Criteria is not working is because I have another Criteria field in the query listing addresses by postcode that it becomes irrelevant as to whether status field is null or not, in other words as long as postcode is being searched for it will be displayed in the results.

If this is the case how can I correct this in the easiest manner, would I be better off creating 1 query with "Is Null" to only display records with null values in the status field, then run all my further querys from the first query, would this work,

[Japn]

is there an easier method?

[John_G]

Are you querying for a specific group of postal codes, or all of them? If you only want a few postal codes, then use June7's suggestion of IN ("JE2 3ZU", "JE2 3ZT", "JE2 3ZG", "JE2 3ZL", "JE2 3ZD") in the criteria for Postal code, and Is Null in the criteria for Status. If you want all postal codes, just the Is Null criteria will work.

[Japn]

Are you querying for a specific group of postal codes, or all of them? If you only want a few postal codes, then use June7's suggestion of IN ("JE2 3ZU", "JE2 3ZT", "JE2 3ZG", "JE2 3ZL", "JE2 3ZD") in the criteria for Postal code, and Is Null in the criteria for Status. If you want all postal codes, just the Is Null criteria will work.

Thank you Very Very Much, Works perfectly! Fixed! Thank you "June 7" & "John_G" as writing the answer in bold I understood much better, thank you again guys I just have to modify 68 more queries

[June7]

Why do you have 68 queries?

My biggest db has only 23 query objects.

Might find this of interest http://allenbrowne.com/ser-50.html

[Japn]

Just because I created 68 territories divided up by different postcodes then I export to excel to create a table to specific A5 size then geolocate addresses onto google maps, where the db comes in is it is really handy to sort out addresses and remove customers that no longer apply but still track address so they are not re-input. sounds complicated and is but I just could not figure a db method of doing it all in Access.

How does the info in the link help me June 7, Sorry but am a novice to access still finding my way round but loving it.

[June7]

If you have 68 queries just for different static filtering criteria and they are otherwise the same structure, I suggest 1 query with dynamic filtering is more manageable. The export of 68 different outputs of the same basic dataset but filtered dynamically can be automated with programming.

Editing the existing 68 queries will resolve the immediate issue and hopefully will never have to edit them again.

[Japn]

I know what you meant now about leaving out spaces and special characters, as Access 2010 adds them and just confuses the issue?

[June7]

Access doesn't add spaces and special characters so don't know what you are experiencing.