Improve company productivity with a Business Account.Sign Up

x
?
Solved

Is there a If Between function in Access

Posted on 2007-11-29
7
Medium Priority
?
4,889 Views
Last Modified: 2010-04-21
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
Comment
Question by:Jon Bredensteiner
7 Comments
 
LVL 75
ID: 20376589
In a query you can use BETWEEN aaa AND bbb

mx
0
 

Author Comment

by:Jon Bredensteiner
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

by:DatabaseMX (Joe Anderson - Microsoft Access MVP)
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
LVL 14

Assisted Solution

by:ldunscombe
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

by:
GRayL earned 500 total points
ID: 20377870
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
 

Author Comment

by:Jon Bredensteiner
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

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

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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.

Join & Write a Comment

As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
This following write-up describes a different way to copy Lotus Notes Calendar to Outlook. Along with this, we will also learn the reason behind this NSF to PST migration. Users can prefer different procedures as per their convenience.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

584 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question