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

 

[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