Create an "If/Then" expression for a query?

  • Thread starter Thread starter Abi
  • Start date Start date
A

Abi

Query columns:
Hours Weeks Finance
## # ##.##

I want the Finance column to do the following:

If [Weeks] = 4, [Hours] / 160, but if [Weeks] = 5,
[Hours] / 200

I can't seem to get it right. Help!
 
Abi,

You cannot use If in a query, but you can use IIf.
What do you wish to do if the weeks is neither 4 nor 5?

Finance:IIf([Weeks] = 4,[Hours]/160,IIf([Weeks]=5,[Hours]/200,Null))
 
Query columns:
Hours Weeks Finance
## # ##.##

I want the Finance column to do the following:

If [Weeks] = 4, [Hours] / 160, but if [Weeks] = 5,
[Hours] / 200

I can't seem to get it right. Help!
Click to expand...

Try:

Finance: [Hours] / ([Weeks] * 40)

if you can count on Weeks always being either 4 or 5 - no IIF needed.
 
THIS WORKS IF THERE ARE ONLY 2 POSSIBLE VALUES ( 4 AND 5)
IN THE COLUMN WEEKS.
=IF(B2=4,A2/160,A2/200)

IF YOU WANT TO TRAP ANY OTHER VALUES, THIS WORKS:
=IF(B4=4,A4/160,IF(B4=5,A4/200,"ERROR"))
 
Sorry about my first reply, I though you meant in excel.
In access, I can only see this being done by a macro
running 2 queries.
Query 1 updates finance when weeks = 4
and
Query 2 updates finance when weeks = 5
This leaves finance empty when weeks is anything else.
 
Back
Top