Cannot See Nulls (1 Viewer)

[Joye]

I already posted this problem in the query section, but got no where with it... here is the problem now...

I have a form with three combo boxs - cboStatus - cboFunder - cboResource on my form. Depending on what you chose as an option the subform, which is based on a query will show the data with the chosen criteria.

Only the Status field is a required field, so there are lots of nulls in the funders and the resource fields.

In the all the fields I have "ALL" or "*" as an option.

So in the criteria in the query I use this...

Like fCboSearch (forms!form name!combobox)

This calls up this function...

Public Function fCboSearch(vCboSearch As Variant)

If IsNull(vCboSearch) Or vCboSearch = " " Or vCboSearch = "*" Then
fCboSearch = "*"
Else
fCboSearch = vCboSearch
End If


End Function

This works great to show me all the records or just records with a certain criteria selected, BUT BUT BUT I cannot get it to show my the NULLS, they are no where to be found. How can I change this to show me NULLS??

Thanks. Joye

 

[FoFa]

If IsNull(vCboSearch) Or vCboSearch = " " Or vCboSearch = "*" Then
fCboSearch = "'*' OR MyColumn IS NULL"
Else
fCboSearch = vCboSearch
End If

Replace MyColumn with your column name.
I Think

 

[Joye]

That does not work, it gives me an error message.

 

[FoFa]

Well what you basically need is the following,
Where you say:
MyCol Like fCboSearch (forms!form name!combobox)

IF the (If IsNull(vCboSearch) Or vCboSearch = " " Or vCboSearch = "*" Then) part is true, it needs to look like:
(MyCol Like fCboSearch (forms!form name!combobox) OR MyCol IS NULL)

But if that is False, then use what you have. The LIKE "*" does not consider a NULL a match (for some unknown reason to the SQL creators).

 

[Joye]

AAAHHHH... it just does not work. I cannot get it to work. When I put in the column name it tells me it does not recognize it. I have tried putting the tablename.fieldname and tried just the field name, but nothing works. I cannot get it to recognize the field, and therefore show the nulls.

Maybe you could spell out the code... the Table name is ProspectProjectInformation and the field is called ProspectStatus.

 

[Joye]

I am going to try to attach an example of what I am trying to do.
If you open the database and open FormExample you will see two tabs. The combo box on the top of the form will show you the data for whichever record you choose, but on the second tab I want people to be able to view all the project information from here, so instead of choosing a project you choose a status, and you can see the list of all the projects with that status. Problem is, one of the project "Hancock" does not have a status filled in, it is null, I cannot get it to appear in the list... see above for the codes and suggestions people have given me. I am sure this is simple, but I cannot figure it out.
Thanks for whoever takes a stab at this.