x
Solved

# Is there a If Between function in Access

Posted on 2007-11-29
Medium Priority
4,889 Views
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...

p.s. I will double the points if you can tell me how to do it in both Access and Excel.
0
Question by:Jon Bredensteiner

LVL 75

Expert Comment

ID: 20376589
In a query you can use BETWEEN aaa AND bbb

mx
0

Author Comment

ID: 20377272
What would the syntax look like using this information?

DateGroup: IIF(Date()-[DateField] =< 90 AND Date()-[DateField] => 45, "Group 2", "Group 1")
0

LVL 75

Assisted Solution

DatabaseMX (Joe Anderson - Microsoft Access MVP) earned 100 total points
ID: 20377304
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

LVL 14

Assisted Solution

ldunscombe earned 400 total points
ID: 20377581
DateGroup: IIF(Date()-[DateField] Between 90 AND 45, "Group 2", "Group 1")

Leigh
0

LVL 44

Accepted Solution

GRayL earned 500 total points
ID: 20377870

This function keeps testing until if find the one that fits and that's it.
0

Author Comment

ID: 20378334
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

Author Closing Comment

ID: 31411769
I really like the Switch function, it requires a lot less text
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.