set a payment date of 2nd of month

[Sarah]

I am trying to use an update query to mark all purchase invoices received in
one month to be paid on the 2nd of the following month. I can't work out how
to fix the payment date as the 2nd of that month when the date of the
invoices varies. Can anyone help please?

 

[Douglas J. Steele]

DateSerial(Year(InvoiceDate), Month(InvoiceDate) + 1, 2)

(Don't worry, that'll work fine even for InvoiceDate values in December)

 

[Marshall Barton]

I am trying to use an update query to mark all purchase invoices received in
one month to be paid on the 2nd of the following month. I can't work out how
to fix the payment date as the 2nd of that month when the date of the
invoices varies.

Use the DateSerial function something like:

DateSerial(Year(invdate), Month(invdate)+1, 2)

 

[Sarah]

Thanks very much for your replies - I understand it now, and it's working!

Sarah

 

[KenSheridan via AccessMonster.com]

I notice that this refers to an update query, so presumably you are assigning
the payment date to a column in the table. Unless you want to be able to
edit the payment date you don't need to do this, you can simply return it in
a computed column in a query, or a computed control in a form or report using
an expression along the lines that the others have given you.

However, what f the 2nd of the following month is not a business day? Would
you want the date automatically adjusted to either the preceding Friday or
following Monday? And if so what if the Friday or Monday is a public holiday
(which would be the case here if it fell on the first Monday in May for
instance); would you want it adjusting forward or back? You could cater for
these possibilities with the following function:

Public Function GetPaymentDate(varDate As Variant, _
Optional intAdjust As Integer = 1)

Dim dtmPaymentdate As Date
Dim strCriteria As String

If Not IsNull(varDate) Then
dtmPaymentdate = DateSerial(Year(varDate), Month(varDate) + 1, 2)
Do
strCriteria = "HolDate = #" & Format(dtmPaymentdate, "yyyy-mm-dd")
& "#"
If Weekday(dtmPaymentdate, vbMonday) > 5 _
Or Not IsNull(DLookup("HolDate", "Holidays", strCriteria))
Then
dtmPaymentdate = DateAdd("d", intAdjust, dtmPaymentdate)
Else
Exit Do
End If
Loop
GetPaymentDate = dtmPaymentdate
End If

End Function

As well as checking if the payment date would fall on a Saturday or Sunday it
also looks for public holiday dates in a table Holidays with a date/time
column HolDate. To adjust the payment date forward to the next Monday or non-
holiday date, in the event of it falling on a weekend or holiday date the
intAdjust argument is optional so you'd simply call the function with:

GetPaymentDate([InvoiceDate])

If you want to adjust it backwards to the preceding Friday or non-holiday
date, you'd enter -1 as the intAdjust argument:

GetPaymentDate([InvoiceDate], -1)

Just paste the above function into any standard module in the database and
call it in a query or as the ControlSource of a computed control in a form or
report.

Ken Sheridan
Stafford, England

 

[Ken Snell [MVP]]

I have a function that can be used for such recurring dates. Put the
function in a regular module, and call it from your update query.

DateForFixedDayOfMonthYear
http://www.accessmvp.com/KDSnell/VBA_Functions.htm#FixedDayMthYr