HOW DO I USE CRITERION IF NOT BLANK IN ACCESS QUERY

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone advise how I enter criterion 'if field not blank' in a query -
e.g. If MyField not blank "1202=MyField".

Thanks
Dudley
 
If you want to replace the Null (blank) value, with another value then use
the NZ function
Nz([MyField],0) ' will replace the null with 0, you can replace with any
other value.

The other way is to use the iif.
iif(isnull([MyField]),0,[MyField]) ' If the value of my field is null it
will replace it with 0, other way get the value of myField
 
Hi,


To find if there is a NULL value, the criteria is


I S NULL


and to find if the field has not a NULL value:


NOT IS NULL


Do not use = or <>, since when applied to NULL, they return NULL, as you
can test it in the immediate debug window:


? NULL = 4
Null


? NULL <> NULL
Null


? NULL = NULL
Null



Null, in such context, can be seen as meaning "unknown". Since you can't
tell, for sure, if two unknown values are equal together, or different,
until the moment you really know each of the two, the result of the = and
<> test is unpredictable, unknown... NULL.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks for the advice, but I do not want to replace blank with a value,
rather to enter specified text when the field is not blank e.g.

If surname field is blank leave it blank
If surname field has "Smith" enter text "6102=Smith"

I assume this would be something like
Iff(([Surname],isnotblank),("6102"[Surname])) but I do not know what to put
for the isnotblank.

Dudley

Ofer said:
If you want to replace the Null (blank) value, with another value then use
the NZ function
Nz([MyField],0) ' will replace the null with 0, you can replace with any
other value.

The other way is to use the iif.
iif(isnull([MyField]),0,[MyField]) ' If the value of my field is null it
will replace it with 0, other way get the value of myField

Dudley said:
Can anyone advise how I enter criterion 'if field not blank' in a query -
e.g. If MyField not blank "1202=MyField".

Thanks
Dudley
Click to expand...
Click to expand...
 
Try this
IIF(not isnull([Surname],"0612" & [Surname])

Dudley said:
Thanks for the advice, but I do not want to replace blank with a value,
rather to enter specified text when the field is not blank e.g.

If surname field is blank leave it blank
If surname field has "Smith" enter text "6102=Smith"

I assume this would be something like
Iff(([Surname],isnotblank),("6102"[Surname])) but I do not know what to put
for the isnotblank.

Dudley

Ofer said:
If you want to replace the Null (blank) value, with another value then use
the NZ function
Nz([MyField],0) ' will replace the null with 0, you can replace with any
other value.

The other way is to use the iif.
iif(isnull([MyField]),0,[MyField]) ' If the value of my field is null it
will replace it with 0, other way get the value of myField

Dudley said:
Can anyone advise how I enter criterion 'if field not blank' in a query -
e.g. If MyField not blank "1202=MyField".

Thanks
Dudley
Click to expand...
Click to expand...
Click to expand...
 
I didn't close the brackets
IIF(not isnull([Surname]),"0612" & [Surname])


Ofer said:
Try this
IIF(not isnull([Surname],"0612" & [Surname])

Dudley said:
Thanks for the advice, but I do not want to replace blank with a value,
rather to enter specified text when the field is not blank e.g.

If surname field is blank leave it blank
If surname field has "Smith" enter text "6102=Smith"

I assume this would be something like
Iff(([Surname],isnotblank),("6102"[Surname])) but I do not know what to put
for the isnotblank.

Dudley

Ofer said:
If you want to replace the Null (blank) value, with another value then use
the NZ function
Nz([MyField],0) ' will replace the null with 0, you can replace with any
other value.

The other way is to use the iif.
iif(isnull([MyField]),0,[MyField]) ' If the value of my field is null it
will replace it with 0, other way get the value of myField

:

Can anyone advise how I enter criterion 'if field not blank' in a query -
e.g. If MyField not blank "1202=MyField".

Thanks
Dudley
Click to expand...
Click to expand...
Click to expand...
 
Blank can be either Null or an empty String "" depending on how you set the
Field in the Table (check Access Help on the Properties "Required" and
"Allow Zero Length"). Depending on how you set the 2 Properties, you can
use different Boolean expressions.

For a general Boolean expression that check for both not Null and not empty
String, you can use:

ModMyField: IIf( [MyField] & "" = "", [MyField], "1202 = " & [MyField])
 
Back
Top