Date Fields

[znpnh]

Hi All,

I'm somewhat of a beginner when it comes to VBA, and I don't even know if I need VBA for this one. I have a form that contains three fields, LASTINSPECTIONDATE, FREQUENCYOFINSPECTION, and NEXTINSPECTIONDATE. The FREQUENCYOFINSPECTION is a combo box containing only three choices; "one", "two", or "three". The user will enter the date into the LASTINSPECTIONDATE field, and decide on the frequency of inspections (this is per year). I am trying to get the NEXTINSPECTIONDATE to auto populate based on the date entered into the LASTINSPECTIONDATE, and the FREQUENCYOFINSPECTION field. For example, if the user enters 1/1/14 in the LASTINSPECTIONDATE, and the frequency is set to two, the NEXTINSPECTIONDATE would populate with 6/1/14. If the frequency was set to one, it would populate 1/1/15. I have tried the IIF statement, and tried inserting the DateSerial function within the IIF statement, but it just returns the actual DateSerial string, and does not calculate it. Any help would be appreciated.

 

[Galaxiom]

NextInspectionDate is a calculated value and should not be stored in the table.

Calculate it each time it is required using the DateAdd function.

 

[CJ_London]

The calculation you require to calculate the next inspection date is:

dateadd("m",(12/FREQUENCYOFINSPECTION,LASTINSPECTIONDATE)

This can be used in a query, so no vba required or in a form to populate an unbound textbox, formatted as a date of some sort. I agree with Galaxiom, you should not be storing this value in a table.

Note that I am assuming you are using 1,2,3 and not one,two,three for frequencyofinspection. If you are using the text, you'll need to substitute with a nested iif statement to convert the text to a number.

You can use a combo box with two columns and hide the first column if you want to store the number in the table but display a text value

 

[znpnh]

Thank you so much! Your expression worked perfectly. I added it to a query. I beg the question, why aren't you supposed to add a calculated field to a table? Just trying to learn the proper way of doing things. Thanks!

 

[CJ_London]

1. it takes a lot of maintaining - if you change the frequency or the last inspection date you will need to recalculate
2. it takes additional space and will bloat your db

- the only exception I can think of is where a temporary table is made to 'summarise' data prior to reporting to improve performance.