How to add 'Is Null' to MS Access form macro

[Lemming Train]

I've been playing around in MS Access creating a filter form to filter data from 5 of my table columns. I have a filter box above each of the columns in the form view and after a lot of trial and error it works fine for my needs, except one thing. I've just discovered that the filter is not showing any record which has an empty cell in any of the columns I'm filtering by. If I put something in the cell then run it again it works fine. How do I fix this please? I understand it's a double quote mark statement or maybe double single quote marks?

This is my ApplyFilter macro, WHERE =

[COL1] Like "*" & [Form1]![Query1]![C1] & "*" And [COL2] Like "*" & [Form1]![Query1]![C2] & "*" And [COL3] Like "*" & [Form1]![Query1]![C3] & "*" And [COL4] Like "*" & [Form1]![Query1]![C4] & "*" And [COL5] Like "*" & [Form1]![Query1]![C5] & "*"

Thanks .

[paul.deitch]

try "IS NULL".

[Lemming Train]

paul.deitch said:

try "IS NULL".

I did Paul but it won't accept the code. I added & IS NULL at the end of one of them. But I'm also at the 255 char limit for the filter so I need another solution I think. I'm sure there's some simple (for techies) work around with VBA but I know nothing about VBA and wouldn't even know where to start. Is the VBA box the thing where you right click and choose 'build event' and a box opens starting Private Sub ? Or is that something else?

[Penelope Stopit]

Going back several years when using Windows to rename many files, one file was renamed "null" and never did show up anywhere

"null" used to be a big problem, I don't know about now though

[MacW]

I think this is what you're after.

[paul.deitch]

Have you tried "Or Is Null" ?

[chilluk7]

Could try :

Nz([COL1],"") Like "*" & [Form1]![Query1]![C1] & "*" And Nz([COL2],"") Like "*" & [Form1]![Query1]![C2] & "*" And Nz([COL3],"") Like "*" & [Form1]![Query1]![C3] & "*" And Nz([COL4],"") Like "*" & [Form1]![Query1]![C4] & "*" And Nz([COL5],"") Like "*" & [Form1]![Query1]![C5] & "*"

Assuming all columns are strings - else use Nz([Col],0) or whatever is appropriate?

[bigpriest]

Lemming Train said:

I've been playing around in MS Access creating a filter form to filter data from 5 of my table columns. I have a filter box above each of the columns in the form view and after a lot of trial and error it works fine for my needs, except one thing. I've just discovered that the filter is not showing any record which has an empty cell in any of the columns I'm filtering by. If I put something in the cell then run it again it works fine. How do I fix this please? I understand it's a double quote mark statement or maybe double single quote marks?

This is my ApplyFilter macro, WHERE =

[COL1] Like "*" & [Form1]![Query1]![C1] & "*" And [COL2] Like "*" & [Form1]![Query1]![C2] & "*" And [COL3] Like "*" & [Form1]![Query1]![C3] & "*" And [COL4] Like "*" & [Form1]![Query1]![C4] & "*" And [COL5] Like "*" & [Form1]![Query1]![C5] & "*"

Thanks .

From what I can see, within each section of your code, you're searching for values that equal *[value]* so there has to be a value to return results. You'd have to add an OR statement at the end of each to say OR "" (blank values) OR Is Null (Null values) but you say you are at the character limit. Could you base your results on a sub-query that replicates your main table but excludes Blank and Null values, therefore removing the problem when you use your filters?

[Lemming Train]

paul.deitch said:

Have you tried "Or Is Null" ?

Invalid statement and won't accept it.

[COL1] Like "*" & [Form1]![Query1]![C1] & "*" OR IS NULL And [COL2] Like "*" & [Form1]![Query1]![C2] & "*" OR IS NULL (etc)


Chiliuk, your solution works thanks, but it exceeds the 255 char limit. Can you make it apply to all columns being filtered with a single statement without needing to repeat it 5 times?

Priest : OR statement won't work as it only applies to a single column. I need to filter across multiple columns, hence the AND requirement.

[WinstonWolf]

Lemming Train said:

paul.deitch said:

Have you tried "Or Is Null" ?

Invalid statement and won't accept it.

[COL1] Like "*" & [Form1]![Query1]![C1] & "*" OR IS NULL And [COL2] Like "*" & [Form1]![Query1]![C2] & "*" OR IS NULL (etc)


Chiliuk, your solution works thanks, but it exceeds the 255 char limit. Can you make it apply to all columns being filtered with a single statement without needing to repeat it 5 times?

Priest : OR statement won't work as it only applies to a single column. I need to filter across multiple columns, hence the AND requirement.

It's a quick bodge but could you rename Form1 F1 and Query1 Q1?

Saves seven characters per instance...

[Lemming Train]

WinstonWolf said:

It's a quick bodge but could you rename Form1 F1 and Query1 Q1?

Saves seven characters per instance...

I've shrunk it down as much as I can but the Nz statement above is still too long. Form1 is actually Forms and I don't think it can be reduced as it forms part of the database structure tree - TABLE > QUERIES > FORMS. The rename option for Forms is greyed out.

Edit: ignore me, I had a 6th statement in the list by accident. Reduced it to 5 as it should be and fits in now after shortening a couple of textbox labels.

Edited by Lemming Train on Wednesday 4th September 14:47

[toon10]

Lemming Train said:

I've shrunk it down as much as I can but the Nz statement above is still too long. Form1 is actually Forms and I don't think it can be reduced as it forms part of the database structure tree - TABLE > QUERIES > FORMS. The rename option for Forms is greyed out.

You should be able to actually rename the form from form1 to f1 or similar. Same with the query. Them you can change the statement to match the naming?

[WinstonWolf]

Lemming Train said:

WinstonWolf said:

It's a quick bodge but could you rename Form1 F1 and Query1 Q1?

Saves seven characters per instance...

I've shrunk it down as much as I can but the Nz statement above is still too long. Form1 is actually Forms and I don't think it can be reduced as it forms part of the database structure tree - TABLE > QUERIES > FORMS. The rename option for Forms is greyed out.

Edit: ignore me, I had a 6th statement in the list by accident. Reduced it to 5 as it should be and fits in now after shortening a couple of textbox labels.

Edited by Lemming Train on Wednesday 4th September 14:47

If it works it's a fix

[Lemming Train]

Nearly working but not quite. It's ignoring records where column 1 is null. Column 1 has the same Nz statement as the other 4 columns which all work fine but no records will show if a column 1 cell is empty.

When I click my RunMenuCommand > Command = RemoveFilterSort button, it still isn't showing the empty cells. What is wrong with it now?


Nz([CL1],"") Like "*" & [Forms]![F]![C1] & "*" And Nz([CL2],"") Like "*" & [Forms]![F]![C2] & "*" And Nz([CL3],"") Like "*" & [Forms]![F]![C3] & "*" And Nz([CL4],"") Like "*" & [Forms]![F]![C4] & "*" And Nz([CL5],"") Like "*" & [Forms]![F]![C5] & "*"

If I put some character in the blank cell it filters OK but not when it's empty.

[voyds9]

Don't understand Access but

Can't you add some thing to the null cells (that can't be used elsewhere) so at least you can get the results then filter them back out later

We used to add 9999 months to recall dates for patients so in 833 years they will get a reminder as we couldn't leave it blank.

[Lemming Train]

I fixed the null problem by populating the cells with a dash . Actually only took 5 mins to do with a swift A-Z sort and then a highlight and paste. Solves the problem and doesn't make any difference here.

I have one final question and that is how to change a Like statement to an exact match. My column 5 doesn't need to have wildcards and would function better for me on exact matches only. I have tried removing the Like statements but it's not working :

Nz([CL1],"") Like "*" & [Forms]![F]![C1] & "*" And Nz([CL2],"") Like "*" & [Forms]![F]![C2] & "*" And Nz([CL3],"") Like "*" & [Forms]![F]![C3] & "*" And Nz([CL4],"") Like "*" & [Forms]![F]![C4] & "*" And Nz([CL5],"") & [Forms]![F]![C5]

[chilluk7]

For exact match :

Nz([CL1],"") Like "*" & [Forms]![F]![C1] & "*" And Nz([CL2],"") Like "*" & [Forms]![F]![C2] & "*" And Nz([CL3],"") Like "*" & [Forms]![F]![C3] & "*" And Nz([CL4],"") Like "*" & [Forms]![F]![C4] & "*" And Nz([CL5],"") = [Forms]![F]![C5]

Is Col1 a string type field? It should have worked the same way really as the others unless there was already some strange chars in there if so.

[Lemming Train]

chilluk7 said:

For exact match :

Nz([CL1],"") Like "*" & [Forms]![F]![C1] & "*" And Nz([CL2],"") Like "*" & [Forms]![F]![C2] & "*" And Nz([CL3],"") Like "*" & [Forms]![F]![C3] & "*" And Nz([CL4],"") Like "*" & [Forms]![F]![C4] & "*" And Nz([CL5],"") = [Forms]![F]![C5]

Is Col1 a string type field? It should have worked the same way really as the others unless there was already some strange chars in there if so.

Col1 is mostly a 4-letter field, but there are some fields with > in them and also square brackets.

The statement above works for exact matches entered in Col5 thanks, however it breaks all the other filter boxes and returns no results when filtering on the others. I guess that is because it's now expecting something in Col5 on every filter? Is there a way round this please?

[Penelope Stopit]

Penelope Stopit said:

Going back several years when using Windows to rename many files, one file was renamed "null" and never did show up anywhere

"null" used to be a big problem, I don't know about now though

Have come to the conclusion that I shouldn't have replied to this topic

Not even close