Multiple IF statements in query

[joshandsony]

Are you able to do multiple if statements within a query? I need to take a due date, and for every "drop shipment" I need to take a week off the due date, for every other shipment I need to do one that takes 2 weeks off the due date. Can someone help me with this?

 

[raskew]

Sure -

Here's an example, based on Northwind's Orders table, that creates a calculated field and populating it with [RequiredDate] minus 1 week if [ShipCountry] is Brazil or Germany, or minus 2 weeks for all other [ShipCountry]'s.

SELECT
    Orders.OrderID
  , Orders.RequiredDate
  , Orders.ShipCountry
  , DateAdd("ww",[COLOR="Red"]IIf([ShipCountry] In ("Brazil","Germany"),-1,-2)[/COLOR],[RequiredDate]) AS NewReqDte
FROM
   Orders;

You should be able to adapt it to your situation, replacing table/field names and modifying the Iif() statement.

HTH - Bob

 

[joshandsony]

So it should look something like this:
IIf( [Drop Ship] = ("Y"),-1,-2), [Due Date] )

 

[raskew]

Assuming [Drop Ship] is a Yes/No (Checkbox) field:

DateAdd("ww",IIf([[Drop Ship] = True,-1,-2),[Due Date])

or even (perhaps)

DateAdd("ww",IIf([[Drop Ship],-1,-2),[Due Date])

Bob

 

[joshandsony]

Cool that worked. Thank you so much.

 

[joshandsony]

Oh one more thing. If I have the due date column in the query, how do I sort that to only show 21 days back from today date and before.

Example: Today is 4/6/09. I need it to show everything previous to 3/23/09

 

[raskew]

In the criteria cell of the date field:

<= Date()-21

Bob

 

[joshandsony]

Thank you, thank you, thank you. You rock!

 

[raskew]

Glad it worked for you.

Note: At my age, I probably more roll than rock.

Bob

 

[joshandsony]

Hahaha. That was pretty funny.