Default value is result of IF() statement.

[dredgy]

Hi, I'm interpreting an Excel spreadsheet into an Access database, and I have one particular problem.

In one column in excel, the formula used, uses nested if statements and the weekday() function and I'm not sure how to conver the Excel syntax to Access.

I always want the default value of this field to be:

=IF(WEEKDAY(Sheet1!$A7) = 7, $B$2, IF(WEEKDAY(Sheet1!$A7) = 1, $B$3, $B$1))

Or, in slightly more database relatable terms:

=IF(WEEKDAY([@date_column]) = 7, [!another_table@another_column2], IF(WEEKDAY([@date_column]) = 1,[!another_table@another_column], [!another_table@another_column]))

Hard to explain, and I'm sure less than ideal syntax.

So the table has two columns, "date_column" and "weekday_column". So the default value of "weekday_column" checks "date_column" for the day, and then returns the appropriate value from "another_table". If it's Saturday, you get one value, if Sunday another, any other day of the week you get the standard.

Thank you very much for any help, and hit me up with any questions, I know it's terribly explained.

[JoeM]

It is a bit confusing. Perhaps posting example of the different scenarios would help.

Note, if you have one field that is dependent or is calculated based on another field, that second field should NOT be stored at the table level, but rather calculated in a query.
If you have a complex calculation, I would recommend creating a User Defined Function (UDF) to do it.

[dredgy]

Hi thanks for your answer. Definitely not storing it at the table level (now) but instead doing a form and trying to calculate it from there.

At this stage I haven't quite gotten to where I want to be so don't need help (think I'm understanding it alright so far). It was just transitioning from a spreadsheet way of thinking to a database way of thinking.

[JoeM]

Don't try to do the calculations directly on the Form. It is easier/better to do them in a query.
You can then use the query as the Source of your Form, and list those calculated values there that way.

[June7]

The Access equivalent would be IIf()

Something like:

=IIf(WEEKDAY([fieldname]) = 7, value2, IIf(WEEKDAY([fieldname]) = 1, value3, value1))

or

=IIf(WEEKDAY([fieldnameA]) = 7, [fieldnameB2], IIf(WEEKDAY([fieldnameA]) = 1, [fieldnameB3], [fieldnameB1))