?
Solved

Is there a If Between function in Access

Posted on 2007-11-29
7
Medium Priority
?
4,827 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

649 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