Date calc based on table field

[WCDoan]

I have tried to figure this out, but haven't had much luck. Have looked thru
the groups, but haven't found one like this...I don't think. Anyhow, I have a
report and I need to calculate a void date using the DateOfSale. However, I
need to also check for the TypeOfSale to know how to calculate the void date.
I tried using a nested IIF in the control source of an unbound text field,
but it would never save what I entered. So, I tried doing it on an event in
the detail but it asked for the void date in a parameter box. I then moved
the following from the detail section to the On Open event of the form. It
now doesn't ask for the void date, but it also doesn't print anything. If
someone could point me in the right direction, I would be much appreciative.

Dim VoidDate As Date

Select Case TypeOfSale
Case Is = "Y3"
VoidDate = DateSerial(Year([DateOfSale]), Month([DateOfSale]),
Day([DateOfSale]) + 3)
Case Is = "Y2"
VoidDate = DateSerial(Year([DateOfSale]), Month([DateOfSale]),
Day([DateOfSale]) + 2)
Case Else
VoidDate = DateSerial(Year([DateOfSale]), Month([DateOfSale]),
Day([DateOfSale]) + 1)
End Select

Thanks,
RandyM

 

[strive4peace]

Hi Randy,

make this a function in a general module:

'~~~~~~~~~~~~~
Function GetVoidDate( _
pTypeOfSale as string, _
pDateOfSale as date) as Date

Select Case TypeOfSale
Case Is = "Y3"
GetVoidDate= DateValue([DateOfSale]) + 3
Case Is = "Y2"
GetVoidDate= DateValue([DateOfSale]) + 2
Case Else
GetVoidDate= DateValue([DateOfSale]) + 1
End Select
End Function
'~~~~~~~~~~~`

then, in the RecordSource for your form:

field --> VoidDate: GetVoidDate(nz([TypeOfSale],""),
[DateOfSale])

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*