Access 2010 - Yes/No (boolean) field Left Join where "Is Null" works in one query but not in another (1 Viewer)

[InstructionWhich7142]

I'm using a left join query from a main parts list to a table of "records to exclude or discount" where if a boolean is true they should be discounted and false excluded entirely, so the where clause is if the left join doesn't match (i.e. the boolean Is Null) they should be included or if the boolean is True they should display, so my criteria says "Is Null or True", to leave out the "false" boolean which is the ones that should be excluded entirely from the output,

This worked on one query, I got Square checkbox with Square black middle and Ticks, i.e. true, however on a second query I only get true, if I just leave "Is Null" I get 0 results, I previously didn't have the boolean and just did the "Is Null" on an Integer index and it worked fine finding NULL values, any ideas on the inconsistency? Thanks,

 

[theDBguy]

Hi. Hard to say. Can you maybe post the SQL statement?

 

[InstructionWhich7142]

It appears to be an Access 2010 Query GUI bug, if instead of having two "or" rows in the GUI, I combine the Or criteria (it's only one statement different anyway) into a single criteria field and delete the second line it works as expected!

oh god, this is what it looks like apparently!



I've tried to simplify it for readability but every time I change the criteria even slightly it works as intended, I assume I've exceeded Access' ability to get all the brackets in the right place! lol

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[InstructionWhich7142]

Thanks, are there any known issues with 2010's GUI query designer?

 

[theDBguy]

Thanks, are there any known issues with 2010's GUI query designer?

I can't think of any.

 

[InstructionWhich7142]

ok, that's odd, I've seen one or two issues before where I added some complex criteria onto a totals query and the query designer changed it if I saved and re-opened and made it incorrect, it moved which fields I'd saved in which boxes etc

 

[theDBguy]

ok, that's odd, I've seen one or two issues before where I added some complex criteria onto a totals query and the query designer changed it if I saved and re-opened and made it incorrect, it moved which fields I'd saved in which boxes etc

Unfortunately, that's normal or expected behavior.

 

[InstructionWhich7142]

haha, fair enough, I guess it has to interpret the GUI view into a WHERE clause and sometimes it can't figure the brackets out I guess?

 

[theDBguy]

haha, fair enough, I guess it has to interpret the GUI view into a WHERE clause and sometimes it can't figure the brackets out I guess?

Correct!