If based caluculation

[lloyd2002]

Hi All,

Trying to see if I can add an IF based calculation into my query - so depending on the value in Field1, then take the date in Field2 and add 30 days.

Any thoughts on how to write this?

 

[Joe4]

In Access, you would use an Immediate If statement. See: MS Access: iif Function
If you need help in writing it, please provide more details.

 

[lloyd2002]

Thanks Joe,

This what I have as a start

IIf([Point of Invoice]="entry to bodybuilder",Sum([DA - Gate rel# date]+30),0)

so If the field "Point of Invoice" has the comment "entry to bodybuilder", then I want it to display the value from field "DA - Gate rel# date" and add a further 30 days to it.

Appears to be simple enough, expect I'm now getting an error message that sates my execute query dosen't include the specified expression "Vehicle Number" as part of an aggregate function. Why does this field need to be included?

 

[Joe4]

Why are you including the SUM function on the date? That part doesn't seem to make much sense to me.
I think you just want:

[COLOR=#333333]IIf([Point of Invoice]="entry to bodybuilder",[DA - Gate rel# date]+30,0)[/COLOR]

(assuming your date field is of Date data type and not String/Text)

 

[lloyd2002]

Thought I needed the Sum to make the calculation work - that all appears to work fine, thank you

 

[Joe4]

You are welcome.

You would only use SUM in an Aggregate Query, and you would typically only sum number fields (it usually doesn't make sense to sum date fields).