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

[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!

 

[Fredg]

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

 

[John Vinson]

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!

Try:

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

if you can count on Weeks always being either 4 or 5 - no IIF needed.

 

[PETER]

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

 

[PETER]

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.