Solved query not working with empty control

[John Sh]

This query fails if the control "cboInfra" is an empty string.
The field defaults to "" and I have tried " ".
I have tried various placements of "%" to no avail..

Note. The code is generated by Access from the Query designer.

SELECT Boxes.Collection
FROM Boxes
GROUP BY Boxes.Collection, Boxes.Family, Boxes.Infra
HAVING (((Boxes.Family) ALike [Forms]![Box_Contents]![cboFamily] & "%") AND ((Boxes.Infra) ALike [Forms]![Box_Contents]![cboInfra] & '%'));

 

[tvanstiphout]

Are you sure it's an empty string? They are hard to distinguish visually from a Null value.
But this always works:
Put the cursor in that field.
In the Immediate window (Ctrl+G) write:
?Screen.ActiveControl = ""
False
?IsNull(Screen.ActiveControl)
True

To fix the query, you can wrap with the Nz function:
AND (Nz(Boxes.Infra) ALike Nz([Forms]![Box_Contents]![cboInfra]) & '%'));

 

[John Sh]

Are you sure it's an empty string? They are hard to distinguish visually from a Null value.
But this always works:
Put the cursor in that field.
In the Immediate window (Ctrl+G) write:
?Screen.ActiveControl = ""
False
?IsNull(Screen.ActiveControl)
True

To fix the query, you can wrap with the Nz function:
AND (Nz(Boxes.Infra) ALike Nz([Forms]![Box_Contents]![cboInfra]) & '%'));

Thank you Tom
I have set the table defaults to "" and had also changed the contents of cboinfra to " ", so with a definite space, just to eliminate the chance of a null
I had tried wrapping the [Forms]![Box etc with nz but not the [Boxes.infra]. That made the difference.
John

 

[MajP]

I have set the table defaults to "" and had also changed the contents of cboinfra to " ", so with a definite space, just to eliminate the chance of a null

That sounds like a horrible idea to me, doing the opposite of what you should do. 99% of the time when you see a blank field in access it has a null value in it not an empty string. You have to jump through hoops to force an empty string into a field. So instead of defaulting to the thing that happens 99% of the time you are forcing it to the thing that is an anomaly. Instead you should ensure no empty strings are in the field and if empty that is a null.

 

[John Sh]

That sounds like a horrible idea to me, doing the opposite of what you should do. 99% of the time when you see a blank field in access it has a null value in it not an empty string. You have to jump through hoops to force an empty string into a field. So instead of defaulting to the thing that happens 99% of the time you are forcing it to the thing that is an anomaly. Instead you should ensure no empty strings are in the field and if empty that is a null.

That's exactly what I was trying to do, in a testing phase.