DateAdd Question

[knitter via AccessMonster.com]

Hi Group,
I'm using this code from John Vinson (many thanks for that) and it's working
very well:
DueDate: DateAdd(Switch([Frequency] = "Annual", "yyyy", [Frequency] =
"Monthly", "m", [Frequency] = "Weekly", "ww"), 1, [CompletedDate])

However, my Frequency field contains Semi-Annual and Bi-Monthly also. I can't
get the syntax right to include those frequency types. I tried using
"Monthly", "m",6, but I got an error. Do I need to enclose that in
parenthesis maybe?

Can anyone help?

 

[Douglas J. Steele]

Unfortunately, you can't use that approach if the number has to change.

You could try:

DueDate: DateAdd(Switch([Frequency] = "Annual", "yyyy", [Frequency]
="Monthly", "m", [Frequency] = "Weekly", "ww", [Frequency]="Semi-Annual",
"m", [Frequency]="Bi-Monthly", "m"), Switch([Frequency] = "Annual", 1,
[Frequency] ="Monthly", 1, [Frequency] = "Weekly", 1,
[Frequency]="Semi-Annual", 6, [Frequency]="Bi-Monthly", 2), [CompletedDate])

However, I think you'd be far better off writing a function, and calling the
function.

Function AddDate(OriginalDate As Variant, Frequency As Variant) As Variant

If IsNull(OriginalDate) Or IsNull(Frequency) Then
AddDate = Null
Else
Select Case Frequency
Case "Annual"
AddDate = DateAdd("yyyy", 1, OriginalDate)
Case "Monthly"
AddDate = DateAdd("m", 1, OriginalDate)
Case "Weekly"
AddDate = DateAdd("ww", 1, OriginalDate)
Case "Semi-Annual"
AddDate = DateAdd("m", 6, OriginalDate)
Case "Bi-Monthly"
AddDate = DateAdd("m", 2, OriginalDate)
Case Else
AddDate = Null
End Select
End If

End Function

You'd then use:

DueDate: AddDate([CompletedDate], [Frequency])