Macro or VBA don't know which way to go to get outcome

[thechazm]

I have a database that I have been stuck on for about 2 weeks now. I keep running in circles between macros and VBA code. Can't seem to figure out which way is going to work so thats the reason for the post.

Anyway the rundown is I have a table that has a persons report date formatted as a medium date and I have a field in that table that has MonthsOnBoard. What I am trying to do is create a macro or a function that will go through each person in the personnel table and calculate total months since they have reported and store it in MonthsOnBoard.

I have been able to do some what of the right calculation by adding in a datediff function in a text box on a report but thats not where I need this information at least not for now.

Table Structure
Personnel-->[Name]
[Report Date]
[MonthsOnBoard]

Thanks for everyones help in advance.

Thechazm

[DonRayner]

I have a database that I have been stuck on for about 2 weeks now. I keep running in circles between macros and VBA code. Can't seem to figure out which way is going to work so thats the reason for the post.

Anyway the rundown is I have a table that has a persons report date formatted as a medium date and I have a field in that table that has MonthsOnBoard. What I am trying to do is create a macro or a function that will go through each person in the personnel table and calculate total months since they have reported and store it in MonthsOnBoard.

I have been able to do some what of the right calculation by adding in a datediff function in a text box on a report but thats not where I need this information at least not for now.

Table Structure
Personnel-->[Name]
[Report Date]
[MonthsOnBoard]

Thanks for everyones help in advance.

Thechazm

As long as you can perform the formula on you data to get the information then it's kind of redundant to store the result in another location. You could for example use the same formula in an unbound text field on a report or you could use it in a query.... etc.

Where do you want the data? and what do you want to do with it?

[missinglinq]

As Don has said, in most cases (including this one) calculations should not be stored but rather redone when needed. Typically this kind of thing would be done in a query, and then you base your form or reports on the query. You have the field in your table and query named ReportDate so, in your query, you make a calculated field. In a blank field enter the name of the calculated field, such as MonthsOnBoard then a colon then your expression/function:

MonthsOnBoard: DateDiff("m",[ReportDate],Date())

Now base your form/report on the query, and refer to MonsthsOnBoard as you would any other field. Any time you do this, the value you get for MonthsOnBoard will be up to date, because it will have just been recalculated.

Forms and reports are generally best based on queries, even when only one table is involved, because you can do this and other data manipulation.

Just remember, anytime you make this kind of change, be sure to change the RecordSource of your form/report to the query. Also remember any time you add a feild to your underlying table to go into design view for the query and add the field to the query.

Welcome to Bytes!

Linq ;0)>

[NeoPa]

It sounds like you may get great benefit from reading Normalisation and Table structures.

[thechazm]

Thanks for the information. I know its bad practice to store information in a table that should be updated all the time but since I am baseing a lot of functions off of these values and they only change a very few times a month I added an update when you first open the database to run the function.

Thanks all for your help and appreciate your time :)

[NeoPa]

There are times when real life situations make following the "rules" blindly inappropriate. I would never treat rules as hard and fast. It sounds like you have a good understanding of the principles and WHY it's generally a good idea.

Anyway, pleased that all seems well now :)