HOW DO I USE CRITERION IF NOT BLANK IN ACCESS QUERY

[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

 

[Guest]

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

 

[Michel Walsh]

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

 

[Guest]

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

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

 

[Guest]

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

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

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

 

[Guest]

I didn't close the brackets
IIF(not isnull([Surname]),"0612" & [Surname])

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

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

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

 

[Van T. Dinh]

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])