Calculating dates using IIF statement (1 Viewer)

[kbushon2006]

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]

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]

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.