Automatically setting a due date using MS Access

PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018 - Updated on Jul 23, 2018 at 01:24 PM
 Blocked Profile - Aug 6, 2018 at 04:23 PM

I am looking to set a field to automatically set a due date based on a frequency (1-9), period (d, ww, m, yyyy) and date recorded. To this end, I have been trying to implement the DateAdd([Period],[Frequency],[Date Recorded]) function. I have been applying this directly to the form in the Due Date box, which displays the expected result once all the other fields are filled, however it is not transferring the date into the new record. Image of form design attached.

Any thoughts on how to fix this?

I'd also like to use Day, Week, Month and year rather than the abbreviations above, so if you have ideas on how I could cover off both it would be amazing.


1 response

Blocked Profile
Jul 23, 2018 at 05:05 PM
Are you using PERIOD as a variable?
The syntax for DateAdd is:
DateAdd(interval, Number, date)

So your formula to populate the entry should look like:
DateAdd("d", 1, now())

This would return the date for 1 day FROM now!

If it is not returning a date, the date is invalid. Try HARDCODING a date first, so that you can see the function work, then attempt at making the variables work once you understand how the functions work.

Post back! It makes me happy to see you are trying it in Access! We are here!

PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018
Jul 24, 2018 at 04:37 AM
[PERIOD], [FREQUENCY] and [DATE RECORDED] are all referencing fields on the form, where; PERIOD = ("d", "ww", "m" or "yyyy"), FREQUENCY = (1-11) and DATE RECORDED = Now().

For some reason the tasks are no longer recording on the table that the form is linked to. I was looking for the form to open from a home-screen command button, which seems to work, then the data can be entered and on clicking the "Add Task" command button the record would be added to the table, the form would move to a new record, then close. To do this I embedded a macro (image below). Can you see any issues that may account for the issue in adding records. I can't really check whether any changes to the DateAdd() Function are effective till the records are showing in the table.

Ok, I just created a form with a text box. In the text box I manually placed the following:

=weekdayname (weekday (dateadd ("d",1, Now ())))) & ", "& dateadd ("d",1, now ())

This produces an output of Wednesday, 7/25/2018 and a time stamp. Get an ouput to display something, then we can toil with the variables.

You have to link the variable to the table. So you must use the syntax of:

The reason to do this is because you may have a BUILT IN QUERY that returns the number of RED Cars into a value called [cars]. In this case, [cars] could also be a field in a table, so you must reference the name of the query/table. So if the query was called [countingcars], the variable would be initialized as:

intCars = [countingcars].[cars]

You may need to initialize a variable with the values stored in the DB. So instead of linking the formula to the DB value, use a crosswalk and do something like:

in a sub of FORM_OPEN():

intValue = [table].[period]

Then use:
weekdayname (weekday (dateadd ("d",intValue, Now ())))) & ", "& dateadd ("d",1, now ())
PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018
Aug 6, 2018 at 10:28 AM
I was really struggling with this so I will just set a due date when I run a query. Thanks for your suggestions.
Blocked Profile
Aug 6, 2018 at 04:23 PM
Thank you for the feedback. If you need anything else, just stop by.