Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 10th, 2005, 08:23 PM
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date Ranges

Hi

Can an expert PLEASE, PLEASE help?? I'll need detail as I'm relatively new to Access and have difficulty knowing where to place statements . . . codes, etc., if VBA is the solution, I need opening and ending statements.

I have a visitor database. Many visitors will have various start and end dates. We have a DAILY report that is generated at the end of each day with the next day's date entered as a prompt to open the report. This report is required to show individuals registered to visit each day.

My Visitor db has a start date and end date field. Example start date for visitor #1 could be 10/25/05 with an end date of 10/31/05. Visitor #2 might also have a start date of 10/25/05 but an end date of 11/20/05.

When I open my DAILY visitor report and enter the next day's date.I will need all visitors to appear on the DAILY report with an increment of 1 day added to each subsequent visit day but drop off the report once it is greater than the end date. Ex: when the original start date of 10/25/05 is entered, both visitors #1 and #2 appear on the report. When the second date arrives and I enter 10/26/05 as the report prompt date, these two visitors appear again and so on and so on until the start date is greater than the end date, at which time the appropriate visitor will drop off the report.

Hope I explained this clearly . . . not real familiar with the appropriate jargon.

Regards,
NB

 
Old December 12th, 2005, 12:45 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Don't enter the date in the report. Enter the date in the FORM. When you do that, open the report (via a button, for example) with this on the button's ON CLICK event. Assuming the report date is a textbox called txtRptDate and the start and end dates are called dtmStartDate and dtmEndDate in the report's underlying table or query:

Dim strCriteria as String

strCriteria = "[dtmStartDate] <= #" & Me.txtRptDate & _
"# And [dtmEndDate] >= #" & Me.txtRptDate & "#"

DoCmd.OpenReport "Your Real Report Name Here", acPreview, , strCriteria


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division




Similar Threads
Thread Thread Starter Forum Replies Last Post
puzzle for date ranges navjot C# 1 October 16th, 2008 02:21 PM
Copying ranges mtowle Excel VBA 2 November 2nd, 2005 06:35 AM
date ranges yuqlin BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 December 23rd, 2004 05:23 PM
Query regarding Date Ranges spraveens SQL Language 7 January 13th, 2004 08:11 AM
Query Returning Date Ranges rstelma SQL Server 2000 4 October 28th, 2003 08:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.