Due date depending on booking date

[SamDemon]

I have been asked to compose a report that calculates the due date depending on when the enquiry was made [DateOfEnquiry] to when the client departs [CostingDate].

I know that I need a calculation field in my query but I'm not sure how to go about it. The calculation will depend on the following table:-

Booking time prior to departure
Over 6 Months - deposit due2 months after confirming group

6 - 3 months - deposit due1 months after confirming group

3 months - 8 weeks - deposit due2 weeks after confirming group

Within 8 weeks - deposit dueFull payment

I hope this makes sense.

Sam


It's just common sense, shame sense isn't common!

 

[lupins46]

'Over 6 Months - deposit due2 months after confirming group'

Is '2 months after confirming' the same as '2 months after the enquiry'?

 

[SamDemon]

Hi Lupins46,

yes it is.

Sam

It's just common sense, shame sense isn't common!

 

[lupins46]

You would find it easier to setvup a VBA function to do this rather than try to build the logic in a query.
Just drop this into a standard module.

Function GetDueDate(vDepdate, vEnqDate) As Date
' usage in query
'MyDueDate:GetDueDate(CostingDate,DateofEnquiry)

If DateAdd("m", 6, vEnqDate) < vDepdate Then
GetDueDate = DateAdd("m", 2, vEnqDate)
ElseIf DateAdd("m", 3, vEnqDate) < vDepdate Then
GetDueDate = DateAdd("m", 1, vEnqDate)
ElseIf DateAdd("d", 56, vEnqDate) < vDepdate Then
GetDueDate = DateAdd("d", 14, vEnqDate)
Else
GetDueDate = Date
End If

End Function

 

[SamDemon]

Lupins46,

apologises but i'm a little new to this. How would i show this on a form? I've added the code to a standard module which i have now renamed to GetDueDate. I then tried to add a text box with the control source 'MyDueDate' but this didnt work. Could you point me in the right direction please?

Thanks!

Sam

It's just common sense, shame sense isn't common!

 

[lupins46]

In an unbound textbox on a single record form (not a continuous form) you can set the control source to :
=GetDueDate(me.txtCostingDate,me.txtDateofEnquiry)
You cannot store the result using this technique.

If you need to store the result you will need to add code to the Afterupdate event procedure for each of the two date textboxes:

if isnull(me.txtCostingDate) or isnull(Me.txtDateOfEnquiry) then exit sub
else
me.txtDueDate=GetDueDate(me.txtCostingDate,me.txtDateofEnquiry)
End if

You need to chnage the textbox names to your own.