IIF Statement: If value in field1 is empty then

M

Maruseru

Hi dear pros

If have records which have no value in the the field [CSFB_1]. Now I would
like to fill all empy fields with the value of the field [uid] when I run a
query

ID: IIF([CSFB_1]="";[uid];[CSFB_1])

Any idea

Best regards
Maruseru
 
J

John Spencer

ID: IIF([CSFB_1] is Null;[uid];[CSFB_1])

OR optionally use the NZ function
ID: NZ([CSFB_1],[UID])

If CFSB_1 could be a zero length string or Null, you can rewrite the
expression to the following.

ID: IIF([CSFB_1] & "" = "";[uid];[CSFB_1])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
D

Dale Fye

Do you really need to fill that value in? Is the value in [CSFB_1] actually
an empty string ("") or is it NULL?

What would normally go in the [CSFB_1] field? Would in normally match what
is in [UID]? If so, why do you need both columns? If not, why do you want
to force that in this case?

After reviewing these questions, If you really think you need to update that
field, the following should do it?

Update yourTable
SET [CSFB_1] = [UID]
WHERE LEN([CSFB_1] & "") = 0

In your query, I would use:

ID: IIF(LEN([CSFB_1] & "") = 0, [Uid], [CSFB_1])

The reason for the Len([CSFB_1) & "") is that if [CSFB_1] is NULL then the
LEN( ) function would error out. Also, just testing [CSFB_1] = "" will not
identify those records where that field is null.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
M

Maruseru via AccessMonster.com

Hi John

thanks for your reply. I tried the last option and it works fine.

John said:
ID: IIF([CSFB_1] is Null;[uid];[CSFB_1])

OR optionally use the NZ function
ID: NZ([CSFB_1],[UID])

If CFSB_1 could be a zero length string or Null, you can rewrite the
expression to the following.

ID: IIF([CSFB_1] & "" = "";[uid];[CSFB_1])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi dear pros
Click to expand...
[quoted text clipped - 8 lines]
Best regards
Maruseru
Click to expand...
Click to expand...
 
M

Maruseru via AccessMonster.com

Hi Dale
thanks for your reply. I tried the last option and it works also fine like
Johns solution.

Dale said:
Do you really need to fill that value in? Is the value in [CSFB_1] actually
an empty string ("") or is it NULL?

What would normally go in the [CSFB_1] field? Would in normally match what
is in [UID]? If so, why do you need both columns? If not, why do you want
to force that in this case?

After reviewing these questions, If you really think you need to update that
field, the following should do it?

Update yourTable
SET [CSFB_1] = [UID]
WHERE LEN([CSFB_1] & "") = 0

In your query, I would use:

ID: IIF(LEN([CSFB_1] & "") = 0, [Uid], [CSFB_1])

The reason for the Len([CSFB_1) & "") is that if [CSFB_1] is NULL then the
LEN( ) function would error out. Also, just testing [CSFB_1] = "" will not
identify those records where that field is null.
Hi dear pros
Click to expand...
[quoted text clipped - 8 lines]
Best regards
Maruseru
Click to expand...
Click to expand...
 
Top