Problem with Query

B

bmistry

Hi,

I have created a Expression in a query with the following
code:

IIf(IsNull([Practice]) Or IsNull([LocationDescr]),"No
Data","Some data")

However, some of the records have 'Some Data' even though
one or both of the field LocationDescr & Practice are
blank. Does anyone know why this is happening and why?

Many thank

bmistry
 
B

Brian Camire

Are either "Practice" or "LocationDescr" fields with a data type of Text and
the "Allow Zero Length" property set to Yes? If so, they may contain empty
strings which are not Null but appear blank. For this reason, it is good
practice to set "Allow Zero Length" property set to No unless you have a
good reason to do otherwise. In any case, if you modify your expression to

IIf(IsNull([Practice]) Or IsNull([LocationDescr]) Or [Practice]="" Or
[LocationDescr]="","No Data","Some data")

and it gives you the answer you want, that probably explains it.
 
B

bmistry

Thank you - that worked perfectly!
-----Original Message-----
Are either "Practice" or "LocationDescr" fields with a data type of Text and
the "Allow Zero Length" property set to Yes? If so, they may contain empty
strings which are not Null but appear blank. For this reason, it is good
practice to set "Allow Zero Length" property set to No unless you have a
good reason to do otherwise. In any case, if you modify your expression to

IIf(IsNull([Practice]) Or IsNull([LocationDescr]) Or [Practice]="" Or
[LocationDescr]="","No Data","Some data")

and it gives you the answer you want, that probably explains it.

Hi,

I have created a Expression in a query with the following
code:

IIf(IsNull([Practice]) Or IsNull([LocationDescr]),"No
Data","Some data")

However, some of the records have 'Some Data' even though
one or both of the field LocationDescr & Practice are
blank. Does anyone know why this is happening and why?

Many thank

bmistry
Click to expand...


.
Click to expand...
 
Top