Help with nested IIF Statement using DateADD.

[metronj]

Hello All,

Recently I was asked to make some changes to some business logic for a report generator I created for our office. Basically what I'm trying to do is this:

1)Evaluate a Date Field
2)If it's populated, leave it alone
3)If it's null, Evaluate a Shipping port field
4)If it's a California port add 50 days
5)If it's a NJ port add 70 days
6)If it's anything else add 60 days.

Can someone help me with the format please? Here is what I'm thinking of using for this, but I'm not sure if it will work. I also would like a "trap all" condition for anything that might sneak in there other then the 3 I know for sure can occur.

Thanks to any and all who can help me out with this, I sincerely appreciate it.

zdatecalc: IIf(Eval(IsNull([ETA Date])),IIf([portcalc]="Long Beach, CA",DateAdd("d",50,[Prod Date]),IIf([portcalc]="Newark, NJ",DateAdd("d",70,[Prod Date]),IIf([portcalc]="TBD",DateAdd("d",60,[Prod Date]),[ETA Date]))))

[ADezii]

1. Create an UPDATE Query that will calculate ETA Date based on whether or not it is NULL. If already has a Value, leave it alone. When it doesn't have a Value, calculate the ETA Date based on the Shipping Port.
2. SQL for the UPDATE Query:
   

   UPDATE Table1 SET Table1.[ETA Date] = fCalcDate([ETA Date],[PortCall]);

3. Function definition:
   

   Public Function fCalcDate(varDate As Variant, strPort As String) As Date
   If IsNull(varDate) Then Exit Function
    
   Select Case strPort
     Case "CA"
       fCalcDate = DateAdd("d", 50, Date)      'California
     Case "NJ"
       fCalcDate = DateAdd("d", 70, Date)      'New Jersey
     Case Else
       fCalcDate = DateAdd("d", 60, Date)      'All Other States
   End Select
   End Function

4. The above Logic is based on the assumption that the Shipping Port cannot be NULL (Required = Yes).

[metronj]

Hi ADezii,

Thanks for your reply. I tried that and it set all of my dates to 12/30/1899 lol. I guess you are on the right track but something needs tweaking :)

Let me try to clear up the exact field names I'm using:
[ETA Date] = the date I'm trying to modify.
[Prod Date] = the date I have for sure. This is the date I want to add 50 or 70 days to if [ETA Date] is null and then put that calculated date into the [ETA Date] field.
[Port] = The field I need to inspect to determine how many days to add to the [Prod Date] if [ETA Date] is Null. Port names can be Long Beach, CA \ Newark, NJ \ TBD (to be determined) \ All others.

The logic is: If the [ETA Date] hasn't been given to us from the factory, we want to look at the [Prod Date] and add the appropriate number of days to it so our dealers have a rough estimate of when new equipment will arrive for them to sell to the end user. [Prod Date] however must not be touched as it is a reference field, the resultant date from the calculation must be put into [ETA Date]. Consequently if we have the [ETA Date] then it must be left alone as it is a much more accurate reflection of when product will actually arrive instead of our guesstimated date.

The reason we do this is because if we don't show our dealers SOMETHING they call all day long wondering when product will arrive. :)

Thanks a bunch for your help!

[ADezii]

1. You now need to pass the Production Date to the Function in order to Recalculate the ETA Date if it is NULL.
2. SQL Statement:
   

   UPDATE Table1 SET Table1.[ETA Date] = fCalcDate([ETA Date],[PortCall],[Prod Date]);

3. New Function Definition:
   

   Public Function fCalcDate(varDate As Variant, strPort As String, dteProdDate As Date) As Date
   If IsNull(varDate) Then Exit Function
    
   Select Case strPort
     Case "CA"
       fCalcDate = DateAdd("d", 50, dteProdDate)      'California
     Case "NJ"
       fCalcDate = DateAdd("d", 70, dteProdDate)      'New Jersey
     Case Else
       fCalcDate = DateAdd("d", 60, dteProdDate)      'All Other States
   End Select
   End Function

[metronj]

Hi ADezii,

Same thing is happening. Here are some screenshots and the code I'm trying:

UPDATE Table1 SET Table1.[ETA Date] = fCalcDate([ETA Date],[Port],[Prod Date]);

 Public Function fCalcDate(varDate As Variant, strPort As String, dteProd_Date As Date) As Date
    If IsNull(varDate) Then Exit Function
 
    Select Case strPort
      Case "Long Beach, CA"
        fCalcDate = DateAdd("d", 50, dteProd_Date)      'California
      Case "Newark, NJ"
        fCalcDate = DateAdd("d", 70, dteProd_Date)      'New Jersey
      Case Else
        fCalcDate = DateAdd("d", 60, dteProd_Date)      'All Other States
    End Select
    End Function
 
 
 

Thanks Again!

[ADezii]

Are both Date Fields defined as DATE?TIME Data Types?

[metronj]

Here was the solution:

1)Use an Update query to change the ETA Data Type to Text.

ALTER TABLE Table1 ALTER COLUMN [ETA Date] TEXT


2 Use an Update query with a IIF on the "update to:" line to determine whether or not the ETA date field is NULL. If it is NULL perform the function, if not just redisplay whatever date is in there.

IIf(IsNull([Table1]![ETA Date]),fCalcDate([ETA Date],[Port],[Prod Date]),[Table1]![ETA Date])

3)Here is the function with some tweaks:

Public Function fCalcDate(ETA_Date As Variant, Port As String, Prod_Date As Variant) As Date
 
    Select Case Port
      Case "Long Beach, CA"
        fCalcDate = DateAdd("d", 51, Prod_Date)      'California
      Case "Newark, NJ"
        fCalcDate = DateAdd("d", 72, Prod_Date)      'New Jersey
      Case Else
        fCalcDate = DateAdd("d", 51, Prod_Date)      'All Other States
    End Select
    End Function
 
 

4) Use an Update Query to change the ETA Date Datatype back to a Date format.

ALTER TABLE Table1 ALTER COLUMN [ETA Date] DATE

The reason I went this way is because Access treats a NULL date as 0.0 (12\30\1899) so after investigating that for a few hours I came up with the conclusion that the easiest way to deal with it was to just do the check and calculations on the field as straight text and then just format it back to date. It turns out that situation is pretty tricky to deal with and this was the easiest way (for me) I could come up with, and it works like a charm.

Hope this helps someone someday.


Thanks guys!