Help with iif statement in query

  • Thread starter Thread starter Sarah
  • Start date Start date
S

Sarah

I have a query which has the following iif statment:
Accrued Vacation: (([Consolidated]![Regular Day (REG)]/8)/24)

This tells me how many hours have been accrued for vacation in 2008

I have another iif statment in this query which is:
Expr1: IIf([Vacation_Balances]![Vacation]=" ",[Accrued
Vacation],[Vacation_Balances]![Vacation])

This statement is returning a blank cell for those employees who it should
be returning the result from the previous iif statment, however it is
returning a blank cell.

Can someone help me with this - please let me know what other information is
necessary.

thank you!
 
Sarah said:
I have a query which has the following iif statment:
Accrued Vacation: (([Consolidated]![Regular Day (REG)]/8)/24)
Click to expand...

First, did you know that you can give your Fields a caption in your table
design? That way you can use a query friendly field name (Reg_Day) for
instance, yet use a user-friendly label that will automatically appear in
your queries and on your forms (Regular Day (REG)).
This tells me how many hours have been accrued for vacation in 2008

I have another iif statment in this query which is:
Expr1: IIf([Vacation_Balances]![Vacation]=" ",[Accrued
Vacation],[Vacation_Balances]![Vacation])

This statement is returning a blank cell for those employees who it should
be returning the result from the previous iif statment, however it is
returning a blank cell.
Click to expand...

Are you sure that the field value would be a single space? I would think
that the value would be more likely to be an empty string ("") or Null. You
could try:

IIf(Nz([Vacation_Balances]![Vacation], " ")=" ",[Accrued
Vacation],[Vacation_Balances]![Vacation])

HTH;

Amy
 
That did work - thank you!

Amy Blankenship said:
Sarah said:
I have a query which has the following iif statment:
Accrued Vacation: (([Consolidated]![Regular Day (REG)]/8)/24)
Click to expand...

First, did you know that you can give your Fields a caption in your table
design? That way you can use a query friendly field name (Reg_Day) for
instance, yet use a user-friendly label that will automatically appear in
your queries and on your forms (Regular Day (REG)).
This tells me how many hours have been accrued for vacation in 2008

I have another iif statment in this query which is:
Expr1: IIf([Vacation_Balances]![Vacation]=" ",[Accrued
Vacation],[Vacation_Balances]![Vacation])

This statement is returning a blank cell for those employees who it should
be returning the result from the previous iif statment, however it is
returning a blank cell.
Click to expand...

Are you sure that the field value would be a single space? I would think
that the value would be more likely to be an empty string ("") or Null. You
could try:

IIf(Nz([Vacation_Balances]![Vacation], " ")=" ",[Accrued
Vacation],[Vacation_Balances]![Vacation])

HTH;

Amy
Click to expand...
 
In the first query there is no IIF statement.

In the 2nd query, the "then" part seems to be missing a table name.

HTH
 
Back
Top