IF Statements in Append Querys?

[Joycelet]

Hi

I posted below about cumalative data - which I've kind of sorted.

I have a query that looks at Table:ActualSales and Table:ImportedSales and takes the figures away to give me a variance - but sometimes the variance will be blank because the product hasn't been sold before so I need to do a kind of IF Statement on the variance field to say.

IF Variance is Blank then copy over TotalThisWeeks Figures?

Can this be done in an Access Query?

Thanks

 

[PHV]

Have a look at the IIf and/or the Nz functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Joycelet]

Hi

I tried the following but i'm missing something as it's not populating the data if variance is blank - here's the IF statement:

Expr1: IIf([Variance]="",[TotalThisWeek],[TotalThisWeek]-[TotalActual])

Thanks

N

 

[PHV]

And what about this ?
Expr1: IIf(IsNull([Variance]),[TotalThisWeek],[TotalThisWeek]-[TotalActual])

Or this:
Expr1: [TotalThisWeek]-IIf(IsNull([Variance]),0,[TotalActual])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Joycelet]

Excellent - Thanks for the help