getting a field to change automatically

hey folks

ive just recently discovered access (have been using excel spreadsheets as db's for a very long time), anyways im developing a db for my dads business and what i want to do is have a field (numbered format) where all the values will increase by +1 every year

is there any easy way to accomplish this?

Can you be more specific? What is this field and how does it fit into the overall database? That can determine the method we are looking at.

First up welcome to the forum.

Coming from an excel background, the first thing you must do is forget everything you have learned from excel regarding the storage and manipulation of data.

A good place to start is by becoming familiar with the concept of data normalisation which is what really makes Access different to Excel. Once you can get your head around that, you will have made a great start down the road of database development.

Now to answer your question have a look at the Dmax() function +1. and search this forum on the subject.

Good luck and have fun

And I second everything that JBB just wrote.

basically the field stores how long a customer has been with us, as we give our customers greater discounts for every year theyve been with us, for simplicity we just want the field to be increased by 1 every january first (instead of manually changing it for each of our customers), the field name is cusYears in tblCustomers,

lol, thanks jbb (especially for the warm welcome),

im glad someones confirmed that everything i learned from excel is almost meaningless in access (untill now i only had a suspicion), im reading through the dmax function now and ill let ya know how it goes

cheers

Okay, so now we know that you do NOT need to store this information. You use a QUERY to pull it and you use a function called DateDiff which will give you the information and you never have to update it.

The usage in a query would be something like this:

MyNewFieldNameateDiff("yyyy", [YourDateFieldWhereCustomerBecameCustomer], Date())

You've just fallen into one of the many traps you will encounter along your path. Storing a calculated field. It is generally accepted (with a few very limited exceptions) that calculated values are not stored in Access.

In excel this is quite an accepted practice, as if there is a change in one of the underlying values then the cell simply recalculates. This does not automatically happen in Access however unless you force it to. But then why store a value that is really only need for display purposes? Given that all the underlying values are already stored, in your tables, it is a simple matter to calculate the result in a form or report as it is required. This also has the added advantage that it will save space (particularly if you have large amounts of data).

In your case you can use the DateDiff() function in combination with the client join date and the Date() function to calculate at any time how long a client has been with you. Avoid the Now() function unless you specifically require a time component on your dates.

Bob I hope this doesn't get too deliverance