Increment

[mama16]

Hi, I created a field in a table "months_In_operation" I'd like for that field to increment every month by one so I don't have to enter the data(numbers) every month. How can I do that?
Can I do it on the Design View, where I created it.

Thanks

 

[MoLaker]

Do you really need to store months_In_operation? By storing (in a separate table, perhaps) the date the operation began, months in operation is only a quick calculation away.

 

[mama16]

That field holds a number

AZ 1
FL 25
Tx 16

I just want to increment that number so next month I won't have to input it

AZ 2

FL 26

Tx 17

 

[PHV]

And what about an update query ?
UPDATE months_In_operation SET [number field] = [number field] + 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MoLaker]

mama16,

How is this data to be used? The reason I ask is if it is to be used as a read-only source for a website or some other application that looks at this data statically, then the incrementing would have to occur upon the 1st of each month. On the other hand, if the data is not used or viewed until a user opens a form in the database, then the data could be incremented at that time and not have to worry about the precise start of the month. The answer to this would, in my mind, determine just when and how the monthly update occurs.

Regardless of your answer, I know of no way you can increment the field from within the table itself. A procedure in a macro or form must perform it.

 

[mama16]

It's going to be view everytime the user opens the form. There are some divisions to be done using this number like calculating averages, whatever amount divided by these numbers. There are 24 states, and each state has a number relating to that state."number of months the state has been using the system" This number is the one I want to increment every new month.

Any ideas?

 

[PHV]

Why not simply store the date the state began using the system ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MoLaker]

PHV more or less echoed what I said in my first reply. If you do not store the date the state began, then you have no way of verifying if the data has been incremented. For example, if you had a manual process by which on the 1st of each month you ran it to increment the field, you would not be able to tell if the proess had been run already - unless you had some starting point. If you had an automated procedure that ran the 1st of each month, the same problem would exist. Did it run or didn't it?

If you record the start date of each state then the months_in_operation can be calculated anytime and if you want, the field updated when the calculation reflects the next month. But, I will say again, if you store the start date, it will not be necessary to store the incremented number. You can calculate it on-the-fly any time it is needed - such as when the user opens the form.