Blank fields

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

I have the following criteria in a query:

IIf([Forms]![Amend Request (Ref No)]![WorkTypeList]="(All)",[tbl
Details]![Work Type],IIf([Forms]![Amend Request (Ref
No)]![WorkTypeList]="(Blank)",[tbl Details]![Work Type],[Forms]![Amend
Request (Ref No)]![WorkTypeList]))

The combo box on the form contains all of the records from the table and
where they are null I have replaced with (Blank) in the combo box. I am then
trying to filter the query using the (Blank) selection but the query does not
return the records where the field is null.

Can anyone correct me on this?

Thank you in advance.

Martin
 
Martin

If a field is "blank", that's not the same as Null. Null means nothing
there. Blank means a zero-length string (or maybe even, for some folks, a
"space"). Your IIF() statement is not testing for Null, so your
"...[WorkTypeList]="(Blank)"..." will ONLY be triggered when the field
contains the literal string "(Blank)". Is that what's in the field?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks for the response.

There is no data in the blank fields. And I only put "(Blank)" in the combo
box. So do I need to test the length of the field and if Zero then do what i
need the statement to do?

Jeff Boyce said:
Martin

If a field is "blank", that's not the same as Null. Null means nothing
there. Blank means a zero-length string (or maybe even, for some folks, a
"space"). Your IIF() statement is not testing for Null, so your
"...[WorkTypeList]="(Blank)"..." will ONLY be triggered when the field
contains the literal string "(Blank)". Is that what's in the field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Martin said:
I have the following criteria in a query:

IIf([Forms]![Amend Request (Ref No)]![WorkTypeList]="(All)",[tbl
Details]![Work Type],IIf([Forms]![Amend Request (Ref
No)]![WorkTypeList]="(Blank)",[tbl Details]![Work Type],[Forms]![Amend
Request (Ref No)]![WorkTypeList]))

The combo box on the form contains all of the records from the table and
where they are null I have replaced with (Blank) in the combo box. I am
then
trying to filter the query using the (Blank) selection but the query does
not
return the records where the field is null.

Can anyone correct me on this?

Thank you in advance.

Martin
Click to expand...
Click to expand...
 
Martin

Testing for a length of zero is a very common approach to identifying an
"empty" field. I'm not all that certain that this will identify a Null
"value" in a field.

If it doesn't, you could use something like:

Len(Nz([YourField],""))

This would (temporarily) convert a Null to a zero-length string and test the
length of it ... 0!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Martin said:
Thanks for the response.

There is no data in the blank fields. And I only put "(Blank)" in the
combo
box. So do I need to test the length of the field and if Zero then do
what i
need the statement to do?

Jeff Boyce said:
Martin

If a field is "blank", that's not the same as Null. Null means nothing
there. Blank means a zero-length string (or maybe even, for some folks,
a
"space"). Your IIF() statement is not testing for Null, so your
"...[WorkTypeList]="(Blank)"..." will ONLY be triggered when the field
contains the literal string "(Blank)". Is that what's in the field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Martin said:
I have the following criteria in a query:

IIf([Forms]![Amend Request (Ref No)]![WorkTypeList]="(All)",[tbl
Details]![Work Type],IIf([Forms]![Amend Request (Ref
No)]![WorkTypeList]="(Blank)",[tbl Details]![Work Type],[Forms]![Amend
Request (Ref No)]![WorkTypeList]))

The combo box on the form contains all of the records from the table
and
where they are null I have replaced with (Blank) in the combo box. I
am
then
trying to filter the query using the (Blank) selection but the query
does
not
return the records where the field is null.

Can anyone correct me on this?

Thank you in advance.

Martin
Click to expand...
Click to expand...
Click to expand...
 
Back
Top