If Statements in MS Access Tables

[Brent Janetzki]

Hi, I am creating a Database and I am baffled how to make the following If Statement Work in a table. =IIf(IsNull([Sold Date]),Date()-[Listing Date],[Sold Date]-[Listing Date])

I want to count the number of days between the list date and sale date, but if the sale date is blank, I want to calculate it from todays date. The problem is that MS Access gives me an error that this formula cannot be used in a calculated table. Any ideas.

 

[spikepl]

1. Believe what Access tells you.
2. Data that can be derived from existing data is redundant and should be stored only if cirumstances warrant it. Do they? Otherwise, since you access data via queries, in the query you can make a calculated field that does what you want.

 

[Brent Janetzki]

Sorry I am new to MS Access and don't understand. Are you saying that this can't be done in a table? Im trying to ce fileds alc the number of days on market between two dates ie. the listing date and the sales date, but if the property has not sold, it should use todays date so all of the days on market have a entry in it. DOes this make sense?

 

[Mile-O]

Don't make the mistake of thinking an Access table is in any way like an Excel spreadsheet. If it did such things, we would just use Excel.

Tables are purely used to store relevant data. They should, in 99.9% of situations, not be used to store the results of calculations, either.

Where you would do calculations is in queries.

Also, since you're new, you'd be advised to do some reading on normalisation before you get too deep into your database design. This is something crucial to understanding how to get the best out of Access.

 

[Brent Janetzki]

So are you saying that I do the calc in a querie which will then populate the answer back into the table?

 

[KenHigg]

Where are you trying to use the caculated value? To display in a form or report? What they are saying is that in most cases it breaks some generic rules to calculate a value and store it in a table.

 

[Mile-O]

So are you saying that I do the calc in a querie which will then populate the answer back into the table?

Half right. We do the calculation in the query. Always in the query. We do not store the answer in the table at all.

Consider this.

You have two fields called Date Start and Date End. In Date Start is 01-Feb-2015 and in Date End is 28-Feb-2015. In a query, you can do a quick formula like (DateEnd - DateStart) + 1 and the result will be 28. Perfect! 28 days is the right answer. So, what happens when we put that 28 into a table, in a field called Days...?

Well, we'll have Start Date: 01-Feb-2015, End Date: 28-Feb-2015
, and Days: 28. Nothing wrong with that.

But then a user goes in to the database and changes Start Date to 15-Feb-2015. So, we now have: Start Date: 14-Feb-2015, End Date: 28-Feb-2015, and Days: 28. Now, 28 is obviously wrong (it should be 14) but that's the value stored in the field.

That's where you start to get reporting issues in your data.

Therefore, we always do the calculation in a query. That way we'll always have the correct value. There's no need to store the value because we can calculate it at run-time with 100% accuracy.

Tables are just the raw data, organised in such a way. Queries are where we can manipulate the data in those tables, sort the data, create calculations, totals, groups, etc. Forms where we manipulate the data. Reports where we present the data.

Some reading:

• First Normal Form
• Second Normal Form
• Third Normal Form

 

[jdraw]

Brent,

Here is a link to Crystal's Access Basics. It is intended as a learning/reference aid for MSAccess and vba. It has explanations and examples.

 

[spikepl]

Data in a table is immutable (unless YOU "mute" it, so to speak) - that is the point of having a data storage facility like a table. Your proposed values would change by the day - and Access, logically, rejects the idea.

 

[The_Doc_Man]

Brent, here is something that may help. It is a rhetorical question in a way... why do you want ANYTHING from a table?

When you build forms, reports, and aggregate queries, you want a source of records that will feed these items you are building. A table is ONE possible recordsource. A query is another, FAR more flexible recordsource. You can put the formula in the query and use the query to drive your report. However, watch out for using computed values in queries that will be updated. There, your better choice is to put the formula in the form or report field and let it compute the value there.

In summary, FIRST decide where you really need the information. SECOND, compute it where you need it. THIRD - particularly if it references anything that changes frequently (like today's date, e.g.) it doesn't belong in a place where static data should be stored - like a table.