Setting default with an IIF Function

[cptdave1996]

Would it be wise to set a default for a field in a table with and IIF Function?
I have a DB where we have two terminals and one terminal we want to set a goal for miles ran 6600 and the other at 8800.


I had 8800 set as the default but now boss wants it like this.
if this is ok to do, would anyone know how to write it. I am still new to access.
I have uploaded a copy of the DB.

[ranman256]

no you cant, tho you build your own default table.
I have a table tConfig for this.
yours would have the settings
TERM, DFT
terminal1, 6600
terminal2, 8800

In the form entry use a combo box that pulls both values,
in the AFTERUPDATE event, the miles would be set from the 2nd value in the comboBox.
txtMiles = cboTerm.column(1)

(column counts start at 0)

[JoeM]

I don't believe you can use formulas in the Default property of a Table field.
If you wanted to do this, you would have to control all data entry via a Form, and have the Form use the formula to populate the field with the correct value as the record is added. Or if you were importing the data, you might need to run an Update Query afterwards that updates the value according to the value.

However, I question whether any of this is necessary. If the value is calculated based on values in other fields, why store it all? A general rule of thumb is that anything that can be calculated from other values should NOT be stored as a table field. There is no need to - if it can be calculated at any time!

[cptdave1996]

It is not a calculation, it will be a set standard so that we can see if they drove the required miles.
Thanks for the input...

[cptdave1996]

ok, thanks ranman256. might be a little over my head but I will give it a shot.
Thanks again!

[JoeM]

It is not a calculation, it will be a set standard so that we can see if they drove the required miles.

Are you sure? Where is this terminal information stored?

What you are describing actually is a simple calculation and not a "default" (a "default", by definition, does not vary).
The calculation would look something like:

=IIF([Terminal]="Terminal1",6600,8800)

or

=IIF([Terminal]="Terminal1",6600,IIF([Terminal]="Terminal2",8800,""))

[cptdave1996]

ok, i see what you are saying. The terminal is stored in a table called ModEmployeeList and Driver Performance.
I think what i am looking for is your second example.
Will give it a try later today. I am really busy now and can't get to it...
thanks!

[JoeM]

Another approach to take is to create a separate table, like Ranman suggested. For the same reasons I described, you wouldn't really need to use an AfterUpdate event to update a field, since you really probably do not want to store the calculation. However, then instead of having a calculation, you simply link to this table to get the value.

The advantage to doing it that way is if you ever need to change those values down the road, you are simply updating a few table records, instead of hunting through calculated fields on queries, forms, reports, etc to see where you used it. So it makes it a little easier to maintain, which is great, especially if you want to let a non-technical person maintain it.

[cptdave1996]

I was thinking about that but i wasn't really sure how to put it in my queries and report.
again, I will work on it this weekend with these suggestions...

[JoeM]

In your query, simply add this new table, and join it to the other table on the "Terminal" field, and then return the value.

[cptdave1996]

Great thanks!

[cptdave1996]

In your query, simply add this new table, and join it to the other table on the "Terminal" field, and then return the value.

Ok I added a new table named it Terminals and have two fields, one for each term and in the fields, I put 8800 for one term and then 6600 for the other.
I am not sure how to link the tables though.

[June7]

So you save the terminal identifier in Driver Performance table. The associated miles can be displayed on form by several methods.

1. combobox to select terminal has a column for the miles field, the miles can be viewed in the dropdown, also an expression in textbox can reference the column by index, index begins with 0: =[cbxTerminal].[Column](1) or the combobox RowSource can concatentate the fields so both values will show in the box and a separate textbox is not needed:

SELECT TerminalID, TerminalName & " : " & Miles As TermMiles FROM Terminals;

2. include Terminals table in the form RecordSource by OUTER joining on the PK/FK fields, bind textbox to the miles field but don't allow edits, set Locked Yes.

3. DLookup()


Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be Truck_Num or TruckNum.

[JoeM]

I am not sure how to link the tables though.

Access is a relational database program (the "relational" being how tables are related to each other). Creating queries between related objects is one of the most powerful reasons to use Access, and is a task should be in every Access users skill set.

If you Google "Access create query between two tables", you will find many, many links and videos on the topic (I like videos myself, as I tend to be a visual learner). Any beginner book in Access will cover this topic also.

Here is one link of a video that shows how to do it to get you started: https://www.youtube.com/watch?v=yqabnPqyNrk
There are many others out there.

[cptdave1996]

So you save the terminal identifier in Driver Performance table. The associated miles can be displayed on form by several methods.

1. combobox to select terminal has a column for the miles field, the miles can be viewed in the dropdown, also an expression in textbox can reference the column by index, index begins with 0: =[cbxTerminal].[Column](1) or the combobox RowSource can concatentate the fields so both values will show in the box and a separate textbox is not needed:

SELECT TerminalID, TerminalName & " : " & Miles As TermMiles FROM Terminals;

2. include Terminals table in the form RecordSource by OUTER joining on the PK/FK fields, bind textbox to the miles field but don't allow edits, set Locked Yes.

3. DLookup()


Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be Truck_Num or TruckNum.

Hi June7,

Thanks.
I am going to change my form so that I can just either put in 8800 or 6600 for the goal miles. I had someone help me build this and not sure how he got the queries to work for my report so I don't want to mess that up.