date calculated from a fixed date and a number of days (1 Viewer)

HI all,

trying to find a way to automatically calculate a table entry based on a known, set date (DateReceived field) plus a choosable number from a list (CheckInterval field - choice between 30, 45 and 60 days) and the calculated value automatically entered into a "CheckDue" field for that record

Example would be a received date of 1st November 2019 with a 45 day interval before a status check has to be made.

Aim is to later run a report showing which documents are either due or overdue for checking based on the current date compared to the "CheckDue" date (will cross that bridge much later though)

Thought the DateAdd function might work, but it is not working as hoped (not working at all!)

huge thanks in advance

Thought the DateAdd function might work, but it is not working as hoped (not working at all!

Click to expand...

DateAdd is indeed the way to go. Can you demonstrate how it is not working for you?

Generally you don't store calculated fields but to calculate your new date, you simply:


DateAdd("d", 45, "11/1/2019")


or try


DateAdd("d", [CheckInterval], [DateReceived])


What are you getting from the DateAdd function that isn't working?

not getting a result as it will not allow me to add the DataAdd expression to a calculated field. It may be that I am using the wrong data type for the expression but not spotted which it should be to allow an expression to be added

not getting a result as it will not allow me to add the DataAdd expression to a calculated field.

Click to expand...

That doesn't make sense. You should be able to manually type anything into a new field box in the query designer.

Can you post your SQL to demonstrate what you have?

Charlie G said:

not getting a result as it will not allow me to add the DataAdd expression to a calculated field. It may be that I am using the wrong data type for the expression but not spotted which it should be to allow an expression to be added

Click to expand...

Hi Charlie. It sounds like you're trying to create a Calculated Field in the table. If so, you could just add the number to the date field. For example:


[DateFieldName]+45


Or, if the number is another field in the same table, then maybe something like:


[DateFieldName]+[OtherFieldNameHere]