CODEWITHSUNDEEP
sqlms-accessif-statementcasems-access-2013
Dreamer64
Dreamer64

Reputation: 1189

CASE statement in Access 2013 (and how to use it)?

I'm new to Microsoft Access programming.

I want to check a date field and return if it is

I made the code below: (using expression Builder)

 Expr_Timeout: 
    IIf([Report_DateTime]=Date(),"Today","")+
    IIf([Report_DateTime]=Date()-1,"Yesterday","")+
    IIf([Report_DateTime]<Date()-7,"Last Week","")+
    IIf([Report_DateTime]<Date()-30,"Last Month","")+
    IIf([Report_DateTime]<Date()-31,"Old","")

Is there a better way to do this? Other languages have a CASE statement but I'm not sure how to do it in Access. I'm using access 2013.

Thanks.

Upvotes: 1

Views: 10225

Answers (2)

Dreamer64
Dreamer64

Reputation: 1189

Thanks for Gord Thompson for giving me a clue, this is what I was looking for, it's working perfect :

expr1: Switch([date_]=Date(),"today",
              [date_]=Date()-1,"yesterday",
              [date_]>Date()-7,"week ago")

Upvotes: 2

Gord Thompson
Gord Thompson

Reputation: 123809

There is a Switch() function that you can use in Access SQL queries and in VBA code (ref: here).

Example:

Switch([Report_DateTime]=Date(), "Today", [Report_DateTime]=Date()-1, "Yesterday", [Report_DateTime]<Date()-1, "Before Yesterday")

There is also a Select Case construct in VBA (ref: here).

Example:

Select Case [Report_DateTime]
    Case Date()
        status = "Today"
    Case Date() - 1
        status = "Yesterday"
    Case < (Date() - 1)
        status = "Before Yesterday"
End Select

Upvotes: 3

Related Questions