If then Else Statement not correct

[antonycat]

Hi All

This is my first attempt at any code but cannot get it to work. Please could someone help me correct the following code including the date formatting:

Public Function DateCode() As String

'1)If chrCoDivision = TS, then DateCode = Format([dtmDlyDate]+[dblMaxShelfLife]) as ”dd mmm”)
'2)If chrCoDivision = TM and dtmFinalDepotDly is null,
'then DateCode = Format(([dtmDlyDate]+[dblMaxShelfLife])- 9 Weeks) as ”Wk 00 ddd”)
'3)If chrCoDivision = TM, FinalDepotDly is not null
'and DateDiff([dtmDlyDate],[dtmFinalDepotDly]) > ([dblMaxShelfLife]-[dblMinShelfLife])
'then DateCode = Format(([dtmDlyDate]+[dblMaxShelfLife])- 9 Weeks) as ”Wk 00 ddd”)
'4)If chrCoDivision = TM, FinalDepotDly is not null
'and DateDiff(d,[dtmDlyDate],[dtmFinalDepotDly]) <= ([dblMaxShelfLife]-[dblMinShelfLife])
'then DateCode = Format(([dtmFinalDepotDly]+[dblMinShelfLife])- 9 Weeks) as ”Wk 00 ddd”).

Dim dtmDlyDate As Date
Dim chrCoDivision As String
Dim dtmFinalDepotDly As Date
Dim dblMaxShelfLife As Double
Dim dblMinShelfLife As Double

If chrCoDivision = "TS" Then
DateCode = Format([dtmDlyDate] + [dblMaxShelfLife], "dd mmm", vbSunday)
ElseIf chrCoDivision = "TM" And dtmFinalDepotDly = "" Then
DateCode = CStr("Wk " & _
(Format(DatePart("ww", DateAdd("ww", -9, ([dtmDlyDate] + [dblMaxShelfLife])), 1), "00")) _
& " " & (Format([dtmDlyDate] + [dblMaxShelfLife], "ddd")))
ElseIf chrCoDivision = "TM" And dtmFinalDepotDly <> "" Then
If DateDiff("d", [dtmDlyDate], [dtmFinalDepotDly]) > ([dblMaxShelfLife] - [dblMinShelfLife]) Then
DateCode = CStr("Wk " & _
(Format(DatePart("ww", DateAdd("ww", -9, ([dtmDlyDate] + [dblMaxShelfLife])), 1), "00")) _
& " " & (Format([dtmDlyDate] + [dblMaxShelfLife], "ddd")))
ElseIf DateDiff("d", [dtmDlyDate], [dtmFinalDepotDly]) <= ([dblMaxShelfLife] - [dblMinShelfLife]) Then
DateCode = CStr("Wk " & _
(Format(DatePart("ww", DateAdd("ww", -9, ([dtmDlyDate] + [dblMaxShelfLife])), 1), "00")) _
& " " & (Format([dtmFinalDepotDly] + [dblMinShelfLife], "ddd")))
End If
Else
DateCode = ""
End If

End Function

I have written what I would like to achieve at the top of the function to help people understand what I am tryin g to do.

Thanks in advance
Regards
Antony

 

[lupins46]

Please give an example of what is not working.

 

[BobStubbs]

I would write this as a set of nested 'If' statements:

If chrCoDivision = "TS" then
    DateCode = [Condition 1 formula]
Else
    If IsNull(dtmFinalDepotDly) then
        [Condition 2 formula]
    Else
        If DateDiff([dtmDlyDate],[dtmFinalDepotDly]) > ([dblMaxShelfLife]-[dblMinShelfLife]) then
            [Condition 3 formula]
        Else
            [Condition 4 formula]
        End If
    End If
End If

I think this makes the code easier to read and understand. If you put your four formulae where I have written [Condition 1 formula] etc, this should work.

Because the formatting formulae are quite complex, I would test each of these separately first, to make sure that the correct format is being applied.

By the way, you can see easily from the nested structure, that you will get an unexpected result with a value in the chrCoDivision other than TS or TM. If there's a possibility that there could be other values in your data, a Select Case structure is better:

Select Case chrCoDivision

Case = "TS"
   'Condition 1 code goes here
Case = "TM"
   'Code for conditions 2, 3, 4 goes here
Case Else
   'Error handling goes here, or leave blank to do nothing
End Select

I hope that this helps.


Bob Stubbs

 

[Remou]

Hi
As far as I know, you can't say:
dtmFinalDepotDly = ""
because this variable is formatted as a date.

This seems to work:
dtmFinalDepotDly = 0
dtmFinalDepotDly <> 0

 

[antonycat]

Bob

Thanks for the help. Here is the changed code:

Public Function DateCode(dtmDlyDate As Date, chrCoDivision As String) As String

'1)If chrCoDivision = TS, then DateCode = Format([dtmDlyDate]+[dblMaxShelfLife])  as ”dd mmm”)
'2)If chrCoDivision = TM and dtmFinalDlyDate is null,
'then DateCode = Format(([dtmDlyDate]+[dblMaxShelfLife])- 9 Weeks) as ”Wk 00 ddd”)
'3)If chrCoDivision = TM, FinalDlyDate is not null
'and DateDiff(d,[dtmDlyDate],[dtmFinalDlyDate]) > ([dblMaxShelfLife]-[dblMinShelfLife])
'then DateCode = Format(([dtmDlyDate]+[dblMaxShelfLife])- 9 Weeks) as ”Wk 00 ddd”)
'4)If chrCoDivision = TM, FinalDlyDate is not null
'and DateDiff(d,[dtmDlyDate],[dtmFinalDlyDate]) <= ([dblMaxShelfLife]-[dblMinShelfLife])
'then DateCode = Format(([dtmFinalDlyDate]+[dblMinShelfLife])- 9 Weeks) as ”Wk 00 ddd”).

Dim dtmFinalDlyDate As Date
Dim dblMaxShelfLife As Double
Dim dblMinShelfLife As Double

If chrCoDivision = "TS" Then
    DateCode = Format(([dtmDlyDate] + [dblMaxShelfLife]), "dd mmm", vbSunday)
Else
    If IsNull(dtmDlyDate) Then
        DateCode = CStr("Wk " & _
        (Format(DatePart("ww", DateAdd("ww", -9, ([dtmDlyDate] + [dblMaxShelfLife])), 1), "00")) _
        & " " & (Format([dtmDlyDate] + [dblMaxShelfLife], "ddd")))
    Else
        If DateDiff("d", [dtmDlyDate], [dtmFinalDlyDate]) > ([dblMaxShelfLife] - [dblMinShelfLife]) Then
        DateCode = CStr("Wk " & _
        (Format(DatePart("ww", DateAdd("ww", -9, ([dtmDlyDate] + [dblMaxShelfLife])), 1), "00")) _
        & " " & (Format([dtmDlyDate] + [dblMaxShelfLife], "ddd")))
        Else
            DateCode = CStr("Wk " & _
        (Format(DatePart("ww", DateAdd("ww", -9, ([dtmFinalDlyDate] + [dblMinShelfLife])), 1), "00")) _
        & " " & (Format([dtmFinalDlyDate] + [dblMinShelfLife], "ddd")))
        End If
    End If
End If

End Function

However when I run the query, using this function to calculate the date code, the calculation is not taking the fields MaxShelfLife, MinShelfLife & FinalDlyDate into account, whether the DateCode is "dd mmm" or "Wk 00 ddd". eg. CoDivision = TS, DlyDate = 01/03/2005 the DateCode = 01 Mar but it should be "11 Mar" (DlyDate + 10) or "Wk 02 Fri"

Why is this ? Have I not specified the fields in the correct place ?

I will try the Select Case once I have got the If Else working.

Thanks again for everyones help
Regards
Antony

 

[PHV]

I think that dtmFinalDlyDate, dblMaxShelfLife and dblMinShelfLife must also be in the function's arguments list.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[antonycat]

I have now put the dtmFinalDlyDate, dblMaxShelfLife and dblMinShelfLife into the function's arguments list but when I run the query using the function, on all the rows where the dtmFinalDlyDate is null, the DateCode is returned as an error ?

This is the calculated DateCode field I have set up in the query where I need the formatting to be done prior to running an append query:

DUDateCode: DateCode([dtmDlyDate],[chrCoDivision],[dblMaxShelfLife],[dtmFinalDlyDate],[dblMinShelfLife])

Any help greatly appreciated.

Regards
Antony

 

[PHV]

So, your function must handle the case where dtmFinalDlyDate is null.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[antonycat]

That is correct. So should I put the dtmFinalDlyDate is null as the first statement in the code or leave it where it is at the moment ? or how do I solve this issue ?

Regards
Antony

 

[PHV]

You have to know which value should be returned by your function when chrCoDivision<>"TS" and dtmDlyDate is null and dtmFinalDlyDate is null.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244