Calculating dates using IIF statement (1 Viewer)

kbushon2006

New member
Local time
Today, 16:09
Joined
Jun 20, 2017
Messages
3
Hi,

I am trying to calculate the date a shipment will arrive at site depending on if it goes by Ocean, Vessel, Air or Truck. I need to write a statement for a report that states something like:

IIf [Planned MOT] like "Ocean" or "Vessel" Then [Site ETA] = [Forecast Ship Date] + 56

Else [Site ETA] = [Forecast Ship Date] +14.

I just cannot get the syntax correct. I have not used MS Access in a long time and am struggling.
 

static

Registered User.
Local time
Today, 22:09
Joined
Nov 2, 2015
Messages
823
iif is a function. x=iif(expression,if true,if false)

=iif([Planned MOT] = "ocean" or [Planned MOT]="vessel",[Forecast Ship Date] + 56,[Forecast Ship Date] +14)

But, really you should have a shipment table with the duration's that links to the main table.
 

plog

Banishment Pending
Local time
Today, 16:09
Joined
May 11, 2011
Messages
10,930
Geniusly, Access has two different If constructs each with different characters.

IIf (https://www.techonthenet.com/access/functions/advanced/iif.php) is a function and can be used outside a module. This one seperates the test, the true result and the false result by commas.

If is what you use in VBA and seperates the test from the true result by 'Then' and the true result from the false result by 'Else'

So, my guess is you have an extra 'I' in your Iif. Further, I advise using DateAdd (https://www.techonthenet.com/access/functions/date/dateadd.php) function to add increments to time values.
 

Users who are viewing this thread

Top Bottom