Automatically setting a due date using MS Access

Closed
Posts
21
Registration date
Tuesday July 17, 2018
Status
Member
Last seen
September 5, 2018
-
 Blocked Profile -
Hi,

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.

Thanks.

1 reply


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!

Posts
21
Registration date
Tuesday July 17, 2018
Status
Member
Last seen
September 5, 2018

[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.



Thanks
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:
[table].[field]


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 ())
Posts
21
Registration date
Tuesday July 17, 2018
Status
Member
Last seen
September 5, 2018

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
Thank you for the feedback. If you need anything else, just stop by.