Date Range Combo Box in Unbound Form : VBA

May 17, 2008 12:56pm pdt

1. LSMConsu... 239,989
2. capricorn1 211,211
3. DatabaseMX 165,460
4. boag2000 156,742
5. peter57r 127,408
6. harfang 116,951
7. wiswalld 78,816
8. TextReport 75,673
9. ldunscombe 56,236
10. jimhorn 55,536
11. jefftwilley 52,798
12. frankytee 42,800
13. JezWalters 42,325
14. eghtebas 41,527
15. mbizup 40,472

1. capricorn1 699,453
2. DatabaseMX 516,173
3. LSMConsu... 417,357
4. peter57r 344,377
5. jefftwilley 337,865
6. harfang 259,472
7. mbizup 243,089
8. boag2000 205,832
9. jimhorn 159,083
10. frankytee 151,041
11. stevbe 137,872
12. wiswalld 119,030
13. LPurvis 113,420
14. GRayL 102,784
15. MikeToole 94,268

05.08.2008 at 12:17PM PDT, ID: 23387316 | Points: 500

	[x] Attachment Details

Date Range Combo Box in Unbound Form

Zones: Microsoft Access Database, Access Forms

Tags: VBA

I have an unbound form that I use to select records. The date field(s) are comboboxes and you can select a DateRange and it calculates which records to return

I've got these working so far

DateRange DateRangeCalc
Today Date()
This Month Between DateSerial(Year(Now()), Month(Now()), 1) And DateSerial(Year(Now()), Month(Now()) + 1, 0)
Last Month Between DateSerial(Year(Now()), Month(Now()) - 1, 1) And DateSerial(Year(Now()), Month(Now()), 0)
Next Month Between DateSerial(Year(Now()), Month(Now()) + 1, 1) And DateSerial(Year(Now()), Month(Now()) + 2, 0)

' Thanks to matthewspatrick Date:09.14.2006 ID:17516636 for those month ones

How do I do this/last/next Year or Week or Quarter?
The next 90 days?
Is it possible to let the user enter a date/daterange themselves in the same box eg.
between 01/01/2008 and 04/05/2008 or do I need a separate box?

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: Ludique

Question Asked On: 05.08.2008

Participating Experts: 1

Points: 500

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

05.08.2008 at 01:53PM PDT, ID: 21528372

Rank: Genius

GRayL:

Last Year: Year(myDate)=Year(Date())-1
This Year: Year(myDate)=Year(Date())
Next Year: Year(myDate)=Year(Date())+1

Week and Quarter are a bit trickier. I'll get back to you

05.08.2008 at 02:13PM PDT, ID: 21528518

Rank: Genius

GRayL:

LastWeek: Format(myDate,"yyyyww") = Format(DateAdd("ww",-1,Date()),"yyyyww")
ThisWeek: Format(myDate,"yyyyww") = Format(DateAdd("ww",0,Date()),"yyyyww")
NextWeek: Format(myDate,"yyyyww") = Format(DateAdd("ww",1,Date()),"yyyyww")

for quarters replace 'ww' with 'q'

Next 90

myDate Between Date() and DateAdd("dd",90,Date())

Better off using two textboxes formatted with the correct date format for your data.

05.08.2008 at 02:37PM PDT, ID: 21528694

Ludique:

Sorry to be a bit of a thickie but what do I put where it says myDate - does it need to have something else there? do I need to put all the Between DateSerial stuff as well like the ones above?

05.08.2008 at 02:45PM PDT, ID: 21528737

Rank: Genius

GRayL:

myDate is the date that you want to test. It could be any date field in a table. If that were the field name in a table named myTable, if you ran this query it would identify all the records which were Last Week:

SELECT * FROM myTable WHERE Format(myDate,"yyyyww") = Format(DateAdd("ww", -1, Date()), "yyyyww")

05.08.2008 at 03:40PM PDT, ID: 21529065

Ludique:

Righto. Thank you very much, that's much clearer now. Lots of things to test over the weekend...

05.14.2008 at 05:54PM PDT, ID: 21569867

Ludique:

TimeCalculation      Time
Is Null      _No Date
Date()      Today
Between DateSerial(Date()),1 ) and DateSerial(Date()),+7 )      xLast 7 Days
"Between Date() and DateAdd(""dd"",+7,Date())"      xNext 7 Days
Between DateSerial(Year(Now()), Month(Now()), 1) And DateSerial(Year(Now()), Month(Now()) + 1, 0)      This Month
Between DateSerial(Year(Now()), Month(Now()) - 1, 1) And DateSerial(Year(Now()), Month(Now()), 0)      Last Month
Between DateSerial(Year(Now()), Month(Now()) + 1, 1) And DateSerial(Year(Now()), Month(Now()) + 2, 0)      Next Month
Between DateSerial(Year(Now()), Month(Now()) - 6, 1) And DateSerial(Year(Now()), Month(Now()), 0)      Last 6 Months
Between DateSerial(Year(Now()), Month(Now()) + 0, 1) And DateSerial(Year(Now()), Month(Now()) + 6, 0)      Next 6 Months
"Between Date() and DateAdd(""dd"",-90,Date())"      xLast 90 Days
"Between Date() and DateAdd(""dd"",90,Date())"      xNext 90 Days
Between DateSerial(Year(Now()),1,1) And Now()      This Year
Between DateSerial(Year(Now())-1,1,1) And DateSerial(Year(Now())-1,12,31)      Last Year
Between DateSerial(Year(Now())+1,1,1) And DateSerial(Year(Now())+1,12,31)      Next Year
Between [Enter Start Date] and [Enter End Date]      _Enter Dates

                       
                          
                             
                             
                                Time	TimeCalculation
_No Date	Is Null
Today	Date()
xLast 7 Days	Between DateSerial(Date()),1 ) and DateSerial(Date()),+7 )
xNext 7 Days	"Between Date() and DateAdd(""dd"",+7,Date())"
This Month	Between DateSerial(Year(Now()), Month(Now()), 1) And DateSerial(Year(Now()), Month(Now()) + 1, 0)
Last Month	Between DateSerial(Year(Now()), Month(Now()) - 1, 1) And DateSerial(Year(Now()), Month(Now()), 0)
Next Month	Between DateSerial(Year(Now()), Month(Now()) + 1, 1) And DateSerial(Year(Now()), Month(Now()) + 2, 0)
Last 6 Months	Between DateSerial(Year(Now()), Month(Now()) - 6, 1) And DateSerial(Year(Now()), Month(Now()), 0)
Next 6 Months	Between DateSerial(Year(Now()), Month(Now()) + 0, 1) And DateSerial(Year(Now()), Month(Now()) + 6, 0)
xLast 90 Days	"Between Date() and DateAdd(""dd"",-90,Date())"
xNext 90 Days	"Between Date() and DateAdd(""dd"",90,Date())"
This Year	Between DateSerial(Year(Now()),1,1) And Now()
Last Year	Between DateSerial(Year(Now())-1,1,1) And DateSerial(Year(Now())-1,12,31)
Next Year	Between DateSerial(Year(Now())+1,1,1) And DateSerial(Year(Now())+1,12,31)
_Enter Dates	Between [Enter Start Date] and [Enter End Date]

                             
                          

                    

Open in New Window

05.14.2008 at 06:10PM PDT, ID: 21569947

Ludique:

OOOpppps! Just nearly spilt my coffee and pressed Submit before I meant to.

Thanks for the pointers so far - it's going pretty well. I've had a bit of a re-think and been able better clarify the ranges of time the users are likely to want.

The created a table that looks like the code snippet below (it's not really code - it was easier to see the columns/rows in the code snippet box than in the accidentally submitted comment above). The beauty of it being in a table is that I can re-use it over and over again as the row source for combo boxes in unbound forms that are used to filter records without having to re-write it for each of the (dozens of) date fields.

All of them work apart from the Times shown below with an x at the front - and to be honest I've messed about with them so much I can't remember where I started.

Is there a similar way to write the Calculations for
Last 7 days (up to yesterday)
Next 7 days (including today)
Last 90 days (up to yesterday)
Next 90 days (including today)?

                       
                          
                             
                             
                                Time            TimeCalculation
_No Date        Is Null
Today           Date()
_Enter Dates    Between [Enter Start Date] and [Enter End Date]
xLast 7 Days    Between DateSerial(Date()),1 ) and DateSerial(Date()),+7 )
xNext 7 Days    "Between Date() and DateAdd(""dd"",+7,Date())"
This Month      Between DateSerial(Year(Now()), Month(Now()), 1) And DateSerial(Year(Now()), Month(Now()) + 1, 0)
Last Month      Between DateSerial(Year(Now()), Month(Now()) - 1, 1) And DateSerial(Year(Now()), Month(Now()), 0)
Next Month      Between DateSerial(Year(Now()), Month(Now()) + 1, 1) And DateSerial(Year(Now()), Month(Now()) + 2, 0)
Last 6 Months   Between DateSerial(Year(Now()), Month(Now()) - 6, 1) And DateSerial(Year(Now()), Month(Now()), 0)
Next 6 Months   Between DateSerial(Year(Now()), Month(Now()) + 0, 1) And DateSerial(Year(Now()), Month(Now()) + 6, 0)
xLast 90 Days   "Between Date() and DateAdd(""dd"",-90,Date())"
xNext 90 Days   "Between Date() and DateAdd(""dd"",90,Date())"
This Year       Between DateSerial(Year(Now()),1,1) And Now()
Last Year       Between DateSerial(Year(Now())-1,1,1) And DateSerial(Year(Now())-1,12,31)
Next Year       Between DateSerial(Year(Now())+1,1,1) And DateSerial(Year(Now())+1,12,31)
                             
                          

                    

Open in New Window

05.15.2008 at 09:49AM PDT, ID: 21575690

Rank: Genius

GRayL:

Try these

Last 7 Days - Between DateAdd("d",-7,Now()) And DateAdd("d",-1,Now())
Next 7 Days - Between Now() And DateAdd("d",6,Now())
Last 90 Days - Between DateAdd("d",90, Now()) And DateAdd("d",-1,Now())
Next 90 Days - Between Now() And DateAdd("d",89, Now())

20080236-EE-VQP-29 / EE_QW_2_20070628