• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4860
  • Last Modified:

Is there a If Between function in Access

Is there a If Between function in Access?

I would like to compare a date field with today's date, and put the result into a group based on the number that the comparison returns.  I'm not sure exactly how it would look.  I would like to do it in Access, but I will settle for Excel too.  In example:

If Date() - [DateField] is =< 44 & => 1 then output (Group 1)
If Date() - [DateField] is =< 90 & => 45 then output (Group 2)

I know I could use a bunch of (AND) or (OR) statements, but I'm hoping that the is a better way to do it; simular to the way you can use conditional formatting in Excel.
Something like IFBetween(Date()-[DateField], 45, 90)

I already know how to do it this way:
DateGroup: IIF(Date()-[DateField] =< 90 AND Date()-[DateField] => 45, "Group 2", IIF( etc...

Thanks in advance for your help, Jon
p.s. I will double the points if you can tell me how to do it in both Access and Excel.
0
Jon Bredensteiner
Asked:
Jon Bredensteiner
3 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
In a query you can use BETWEEN aaa AND bbb

mx
0
 
Jon BredensteinerProject ManagerAuthor Commented:
What would the syntax look like using this information?

DateGroup: IIF(Date()-[DateField] =< 90 AND Date()-[DateField] => 45, "Group 2", "Group 1")
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, you would typically use the Between / And syntax in a criteria expression.  What you have above appears to be ok as is.

For example ... lets say you have a date field and you want to filter over a range, you would have as a criteria expression:

Between #11-1-2007# And #11-30-2007#   for example.

mx
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
ldunscombeCommented:
DateGroup: IIF(Date()-[DateField] Between 90 AND 45, "Group 2", "Group 1")

Leigh
0
 
GRayLCommented:
Switch(Date()-DateFld<=44,Group1,Date-DateFld<=90,Group2, <add as you like>)

This function keeps testing until if find the one that fits and that's it.
0
 
Jon BredensteinerProject ManagerAuthor Commented:
Thanks ldunscombe, that worked perfectly.

And thank you GRayL for the information on the Switch function.

Both solutions work great, so I will split the points between the two of you.  Thanks again, Jon

p.s. If either of you know how to do this in Excel, let me know, and I will post another question under Excel, for you to answer.  Thanks...
0
 
Jon BredensteinerProject ManagerAuthor Commented:
I really like the Switch function, it requires a lot less text
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now