Calculate Date based on Past Date and Frequency

[jchandler88]

Good afternoon,



Apologies in advance if this has been covered before, I found a few similar topics but none went into enough detail for me to follow as my Access skills are next to non-existent nowadays (high school was some time ago).

The purpose of my database is to track inspections of sites for asbestos.

I have a table that lists all the site inspection details amongst which are the Previous Inspection Date, Next Predicted Due Date and Frequency fields.

What I require is that the Next Predicted Due Date to be calculated from the Previous Inspection Date + Frequency, with the frequency being anywhere from 1-5 years (by yearly increments, no months involved).

To the best of my understanding this would be better done via a query for the sake of the database's performance but beyond that I'm clueless as to how to proceed.

Apologies again for the noob nature of the post but any assistance would be much appreciated. If there's any more information or details required to lend me that assistance, ask away and I will provide what I can.

Thanks,
James

[pbaldy]

You can use the DateAdd() function, using the previous date and frequency fields for the second and third arguments.

[jchandler88]

You can use the DateAdd() function, using the previous date and frequency fields for the second and third arguments.

I'm afraid I'll need it dumbed down a little more unfortunately.

[June7]

Teach a man to fish …

See if this tutorial helps http://www.fontstuff.com/access/acctut03.htm

and https://support.microsoft.com/en-us/...alues-in-acces

[jchandler88]

Teach a man to fish …

See if this tutorial helps http://www.fontstuff.com/access/acctut03.htm

and https://support.microsoft.com/en-us/...alues-in-acces

It did help. Kind of.

The query draws the Previous Inspection Date field from the Asset List table and the Days field from the Inspection Frequency table. (To simplify the calculation I added a third column to Inspection Frequency where it represents 1-5 years in days e.g. 1 year = 365, 2 years = .730 etc). I drop those two fields into the query itself then create a third field which is "Next Predicted Due Date: [Previous Inspection Date]+[Days]". When I open the query, the Next Predicted Due Date displays as intended, no issues whatsoever. However, I add it to the table as a Lookup field, I select the query, select the Next Predicted Due Date column. Save. Open the table and that column is just blank. Nothing. I also note that it changes the data type to Date/Time? Not sure if that's intended or indicative of something.

I'll chalk it up to being an "ID-10T"/"PEBKAC" issue but would appreciate any further assistance.

Thanks again,
James

[pbaldy]

This is the type of thing that is not generally saved:

http://allenbrowne.com/casu-14.html

[June7]

I think you misunderstand what a lookup field is. A lookup field is equivalent to a combobox on form and is to facilitate data entry. No values show in table via lookup field because you haven't entered any data into that field.

I NEVER build lookups in table. http://access.mvps.org/Access/lookupfields.htm

As Paul and Allen advise, no need to save this calculated value to table.

[jchandler88]

I think you misunderstand what a lookup field is. A lookup field is equivalent to a combobox on form and is to facilitate data entry. No values show in table via lookup field because you haven't entered any data into that field.

I NEVER build lookups in table. http://access.mvps.org/Access/lookupfields.htm

As Paul and Allen advise, no need to save this calculated value to table.

I follow that I have misunderstood what a lookup field is. I don't follow this no need to save to table aspect.

How do I take the information you've given me about making a query (which seems to work just fine in and of itself, thank you) and then get that to populate or create that "Predicted Date" field in the table?

[pbaldy]

Allen's link contains thoughts and methods to save the value in the table if appropriate.

[Micron]

PMFJI: I don't see any mention of a form here, just (I think) table lookup fields, which I agree that we should avoid. Nor should we be storing a calculated future inspection date. So on the not mentioned form, you should have an unbound control that calculates the next date by adding the last inspection date + the frequency. Likely that you will have to use the Max function somehow to get the Max([Last Inspection Date]); somehow being via a query or another unbound calculated control on the form that uses DMax on the table date field (or query if the form is based on a query). Same could hold true for a report. Like always, many ways to do anything in Access, so your query(ies) could also do this. Note that if all is done in a query and the form is based on it, the form won't be updatable due to the field calculations or use of aggregate functions in the query.