CODEWITHSUNDEEP
ms-accessdatabase-design
Nathan Dukellis
Nathan Dukellis

Reputation: 3

Adding an Expression to a MS Access table

I need to add a "STATUS" field to an Access table... Creating the field is the easy part.

I need to display one of three words based on a date from another field.

So in the "Status" field I need to take the date and if it less that 180 days from the date field have the "Status" field display "CURRENT"

If the date is between 181 days and 365 days I need it to display "SUSPENDED" and over 365 days I need it to display "EXPIRED".

If it is also possible to have the field show color based on the current, suspended, expired output that would be a bonus.

Upvotes: 0

Views: 165

Answers (3)

HansUp
HansUp

Reputation: 97101

Use a Switch() expression in a query to derive "Status".

Here it is formatted with each condition/value pair on a separate line. Similar to a VBA Select Case ... End Select block, Switch() returns the value from the first condition which evaluates as True, and ignores the rest. 

Switch
    (
        DateDiff('d', [YourDateField], Date()) < 181, 'CURRENT',
        DateDiff('d', [YourDateField], Date()) BETWEEN 181 AND 365, 'SUSPENDED',
        DateDiff('d', [YourDateField], Date()) > 365, 'EXPIRED',
        True, Null
    )

The last condition catches any YourDateField value (eg Null) which doesn't satisfy one of the first three conditions.

Upvotes: 1

Brad
Brad

Reputation: 12245

On the form where you are presenting your data you can create a calculated value in a textbox. You can use Conditional Formatting to change the color (in datasheet view) or VBA (in Form view)

Alternatively if you are using a query to present your data on your form you can add another table with these threshold dates and join to it. Then your dates can be dynamic and not hardcoded into your forms.

Upvotes: 1

user5818624
user5818624

Reputation:

I find using calculated columns in tables can be cumbersome and you are limited to what you can do. I would recommend creating a query on your table instead and add the following expression to a new query field:

Status: IIf(DateDiff("d",[YourDateField], Date())<=180,"CURRENT",IIf(DateDiff("d",[YourDateField], Date())>=181 And DateDiff("d",[YourDateField],Date())<365,"SUSPENDED","EXPIRED"))

Make sure to test this a let me know if the calculation is right for each scenario. I may have it backwards.

As far as formatting the field based on the status, this can be accomplished with a textbox in a form or a report. If you select the "Status" textbox in the form/report's design view and then in the ribbon go to the "Format" tab in the "Form/Report Design Tools" menu, click on "Conditional Formatting". There you can specify rules to the textbox background color based on what the status value is.

Hope this helps!

Upvotes: 2

Related Questions