Solved

Is there a If Between function in Access

Posted on 2007-11-29
7
4,782 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:JBredensteiner
7 Comments
 
LVL 75
ID: 20376589
In a query you can use BETWEEN aaa AND bbb

mx
0
 

Author Comment

by:JBredensteiner
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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 25 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 14

Assisted Solution

by:ldunscombe
ldunscombe earned 100 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 125 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:JBredensteiner
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:JBredensteiner
ID: 31411769
I really like the Switch function, it requires a lot less text
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Manipulate Range in Excel VBA 6 23
Excel Charts: How is this Chart made? 8 28
Creating An Intelligent Dropdown 8 30
Display <> "Yes" records 10 7
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…

772 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