using an if statement on an sql string

[LuigiCorleone]

I'm populating an access report from an sql string thus: (edited version below)

SQL = "SELECT asset.Local_ID, asset.Category, asset.Asset_Label, asset.Asset_Loc, " _
& "asset.Task_Price, asset.Invoice_Price"

Me.RecordSource = SQL

No problems, everything is working fine except....

I want to populate a field on my report called "Price" with the greatest of either the asset.Task_Price or the asset.Invoice_Price.
So each record on the report displays the "Price" as the highest of either figure.

Do I use an "If" statement and if so, where do I put it. My feeble attempts thus far have been spectacularly unsuccessful.

Thanks in eager anticipation

Luigi

 

[WayneRyan]

Luigi,

Look in help for the Iif Statement

Wayne

 

[LuigiCorleone]

Hi Wayne

I tried that however my Iif formula didnt produce the desired result.

Can you be more specific?

Cheers

Luigi

 

[Mile-O]

SQL = "SELECT asset.Local_ID, asset.Category, asset.Asset_Label, asset.Asset_Loc, " _
& IIf(asset.Task_Price > asset.Invoice_Price, "asset.Task_Price", "asset.Invoice_Price") & " as Price;"

Try something like that...

 

[DukeCleo]

Luigi,

Place and unbound textbox in the report (call it txtPrice) and place the following in the Detail_Format event...

me.txtPrice=Iif(asset.Task_Price>asset.Invoice_Price, asset.Task_Price, asset.Invoice_Price)

(all in 1 line)

Hope this helps.

Scott Miles

 

[WayneRyan]

Luigi,

From Help:

= IIf([OrderAmount] > 1000, "Large", "Small")

SQL = "SELECT asset.Local_ID, " & _
"asset.Category, " & _
"asset.Asset_Label, " & _
"asset.Asset_Loc, " & _
Iif(Task_Price > Invoice_Price, Task_Price, Invoice_Price) as Price" & _
"From YourTable"

I would prefer to put that in a query and use that as
the report's source, just put:

Price: Iif(Task_Price > Invoice_Price, Task_Price, Invoice_Price)
as a column.

Wayne

 

[Mile-O]

Whoops! Forgot the From!

 

[WayneRyan]

Wow,

Four replies in 7 minutes ...

Wayne

 

[LuigiCorleone]

almost there

Hey thanks guys

I'm spoilt for choice! Actually I cant try it out for a few hours but I'm wetting my pants with exitement.

I'll go with the SQL tweak rather than the query.

Watch this space!

Cheers

Luigi