Creating a Date

[whisp0214]

I am trying to come up with a way to calculate a date based on another date already entered. Here is the scenario. Company A always pays on the 26th of every month for shipments based off of the previous month. So all orders placed in February, would then be paid on March 26th. Every order has a date ranging from the first of the month to the last as to when it shipped. Basically I would like to add another field to the Purchase Order table that shows the payment date of 3/26/2018 for all 2018 February orders. I have played around with DateAdd and Month/Day/Year, but have not come up with anything. Is there a simple solution to this?



Thanks in advance.

[June7]

And exactly what is the issue? Does the DateAdd calculation not return the correct value? Post the expression.

DateAdd function is not available to Calculated type field in table. Do calc in query or textbox.

[whisp0214]

The issue is there are hundreds of orders in a given month that all have different dates attached to them ranging from the 1st to the last of the month, but they are all paid at the same time. So a 2/1/2018 order and a 2/28/2018 order will be paid on 3/26. How do I have both orders show the same date of 3/26.

DateAdd('n" , This value is my issue, [Date from Field1])

[CJ_London]

try

to get to the same day of next month

dateadd("m",1,mydate)

subtract the day of the month and add 26

dateadd("d",26-day(mydate),dateadd("m",1,mydate))

[ssanfu]

Another option:

DateSerial(Year(DateAdd("m", 1, OrderDate)), Month(DateAdd("m", 1, OrderDate)), 26)

In a query:

SELECT OrderDate, DateSerial(Year(DateAdd("m", 1, OrderDate)), Month(DateAdd("m", 1, OrderDate)), 26) AS PayDate
FROM YourTable