I’m having a tough time ‘relearning’ Access, 'cause I want to build some project tracking databases. I want to figure out how to manipulate data for one value based off of that of another–like in Excel, it ought to be B = A + some constant.
For an extremely simple example, I have a truck. It was last seen for routine service on a “LastServiceDate” of 1/1/06. I want to automatically generate a value in the same table (“Table1”) called “NextServiceDate” for exactly 30 days later.
The expression builder thing is driving me nuts. I’ve read the MS Help entry (which is named in a misleading fashion), and I’ve tried doing this (for the “NextServiceDate” field:
= LastServiceDate + 30
Then the thing tells me I have a syntax error. Am I missing something in the coding? Do I have to tell the danged expression builder to look in Table1 for the value?
Help. Please. Tomorrow when I go to work, I’ll be at wits’ end.
Tripler
“PC Load Letter, what the f*ck does that mean?!?”
Databases and spreadsheets like Excel look similar until you try to do something like this. Access is not a spreadsheet and the theoretical understructure dictates that you can’t easily do what you want to do. That is not to say that it is impossible.
The basic table structure is such that you can’t just start manipulating fields to create other feilds in that way.You usually do this through queries on top of the original table. That is the suggested route. Use queries to build a view of the table data that also includes fields like you want created through calculations.
Tripler, you can easily do this in code. Say for instance your data entry field has an entry that will always be today’s date. As the user enters the field, have it default to today’s date. Then when they tab to the Next Service date field, in the on got focus event of that field, put in your code which would like this:
formname.nextservicedate = date() + 30
or
formname.nextservicedate = cdate(formname.entrydate) + 30
or
formname.nextservicedate = formname.entrydate + 30
(I whipped out a quick form, all three methods work, A2K2, SP3)
One of the general principles of database design is that you don’t store anything you can calculate without a good reason. The reasoning behind this is two-fold: first, it takes up extra space, and second, it introduces a potential for error–whenever you update the inputs to a calculation, you have to update the outputs as well.
Here, there’s probably no risk of running out of space, so you just have to worry about keeping the data up-to-date. I’d go with a query, as Shagnasty mentioned, but you could use BF’s method as well. Just be sure that it’ll trigger when you change a value in addition to adding a new one, or else you’ll have to manually update two fields whenever you mistype a date.
I will try to make my answer from before more concrete.
Database tables are not spreadsheets. They are not meant to store redundant calculations within themselves. That can be done further downstream if desired.
Tables store the raw data.
Queries can be used to build a view of that data that includes calculations like you want. In Access, a query can be used anywhere a table can so that is where I think you want to focus your energy. Just start a query on that table, include all your fields, and build any calculations you want within that.
You can also build calculations within forms and reports as well.
If you absolutely have to get include that type of calculation in a query, you can do it through code, update queries, or insert it directly into the table as the data is passed in through whatever source it comes from.