Wrox Programmer Forums
|
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 July 23rd, 2007, 11:05 PM
Registered User
 
Join Date: Mar 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Time Query

I have two fields, a 'Start Time' and an 'End Time'. I am trying to create query that you can enter the start time and the end time and only those records will show.

I have used the between function, but I can only use either the 'Start Time' field or the 'End Time' field. I need to be able to use both fields.

 
Old July 24th, 2007, 07:05 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

The problem you are probably having is that you have two seperate date fields. Between will only work with a single field. You need to use greater than and less than and pass the parameters in a WHERE clause, like (remember to check for Nulls in your code - I am not here):

Dim dtStart As Date
Dim dtEnd As Date
Dim sLink As String

dtStart = Me.cboStart
dtEnd = Me.cboEnd

sLink = "[StartDate] > #" & dtStart & "# AND [EndDate] < #" & dtEnd & "#"

Then put this in the WHERE clause of the DoCmd.OpenReport line.

I am not sure if I got the greater than and less than in the right place. I always get them mixed up (ambidextrous).

Did that help?


mmcdonal
 
Old July 24th, 2007, 07:16 PM
Registered User
 
Join Date: Mar 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry for the ignorance, but I am not sure where to insert these lines. In my query I am just using the basic commands.

 
Old July 25th, 2007, 06:24 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am assuming that you are using a form to launch a report, and that the user can select a range of start and end dates from two combo boxes that look to the actual data and show only actual dates in the data (use the combo box wizard). Then you have a button to launch the report. This code goes on the on click event of that button. If you use the wizard to launch the report, you will get this code:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptYourReportName"

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

You paste in the code to replace stLinkCriteria, and change stLinkCriteria in the DoCmd line to sLink. Make sure to refer to the combo boxes by their proper names, etc. I used sample names.

Did that help?



mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Executed multiple time. rupen Classic ASP Databases 4 January 11th, 2008 07:30 AM
Time Range Query everest SQL Server 2005 1 September 26th, 2007 06:18 AM
SQL Optimization - Query takes a long time jlrolin SQL Language 2 March 20th, 2007 08:01 AM
Need to avg time in a query rseward Access 3 December 8th, 2004 05:19 PM
Time out on insert query Tom_Nguyen SQL Server 2000 0 June 29th, 2003 11:23 PM





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