A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Changing constants in a query to user input values



 
 
Thread Tools Display Modes
  #1  
Old June 3rd, 2010, 04:59 AM posted to microsoft.public.access.queries
PamB
external usenet poster
 
Posts: 15
Default Changing constants in a query to user input values

Access 2007
I have a table that contains (among other things)
Start Date
TimeDown
End Date
TimeUp

All of these are Date/Time type

Example data:

Start Date TimeDown End Date TimeUp
16/3/10 02:00 16/3/10 04:00
16/3/10 06:00 16/3/10 09:00
16/3/10 19:00 16/3/10 20:30
16/3/10 23:00 17/3/10 01:00
17/3/10 03:00 17/3/10 04:00
17/3/10 08:45 17/3/10 09:30

I need to be able to return only those Start Date & Time Values that occur
between 5pm on the first date and 5am on the second date. In the above
example data, this would be the 3rd, 4th and 5th lines. This is for a report
that covers Night shift.

I have a select query with constants:
Field:
NightShift: [Start Date]+[TimeDown]
Criteria:
Between #16/03/2010 5:00:00 PM# And #17/03/2010 5:00:00 AM#

and this seems to work.

What I need to be able to do is have a user input a date range (using a form
for Start Date and End Date - which I can do) but have only the time frame
5pm on the Start Date to 5am on the End Date.

I would really appreciate any assistance as I'm still muddling my way
through...

TIA

--
cheers
pamb
  #2  
Old June 3rd, 2010, 01:21 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Changing constants in a query to user input values

If I understand correctly, you want something like

Between DateAdd("h",17,[Forms]![FormName]![StartDate]) and
DateAdd("h",5,[Forms]![FormName]![EndDate])

Since that would only work for a one day range you could use

Between DateAdd("h",17,[Forms]![FormName]![StartDate]) and
DateAdd("h",29,[Forms]![FormName]![StartDate])

If you wanted multiple days you would need something more complex.
Criteria Under [Start Date]
Between [Forms]![FormName]![StartDate] and [Forms]![FormName]![EndDate]

Criteria under TimeDown
(TimeDown = #05:00:00# OR TimeDown =#19:00:00#)

Alternative criteria for TimeDown
NOT BETWEEN #05:00:01# and #16:59:59#


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

PamB wrote:
Access 2007
I have a table that contains (among other things)
Start Date
TimeDown
End Date
TimeUp

All of these are Date/Time type

Example data:

Start Date TimeDown End Date TimeUp
16/3/10 02:00 16/3/10 04:00
16/3/10 06:00 16/3/10 09:00
16/3/10 19:00 16/3/10 20:30
16/3/10 23:00 17/3/10 01:00
17/3/10 03:00 17/3/10 04:00
17/3/10 08:45 17/3/10 09:30

I need to be able to return only those Start Date & Time Values that occur
between 5pm on the first date and 5am on the second date. In the above
example data, this would be the 3rd, 4th and 5th lines. This is for a report
that covers Night shift.

I have a select query with constants:
Field:
NightShift: [Start Date]+[TimeDown]
Criteria:
Between #16/03/2010 5:00:00 PM# And #17/03/2010 5:00:00 AM#

and this seems to work.

What I need to be able to do is have a user input a date range (using a form
for Start Date and End Date - which I can do) but have only the time frame
5pm on the Start Date to 5am on the End Date.

I would really appreciate any assistance as I'm still muddling my way
through...

TIA

  #3  
Old June 4th, 2010, 05:52 AM posted to microsoft.public.access.queries
PamB
external usenet poster
 
Posts: 15
Default Changing constants in a query to user input values

Thank you John.

Between DateAdd("h",17,[Forms]![FormName]![StartDate]) and
DateAdd("h",29,[Forms]![FormName]![StartDate])

works perfectly for what I'm doing!

You guys who answer these questions are Legends!

--
cheers
pamb


"John Spencer" wrote:

If I understand correctly, you want something like

Between DateAdd("h",17,[Forms]![FormName]![StartDate]) and
DateAdd("h",5,[Forms]![FormName]![EndDate])

Since that would only work for a one day range you could use

Between DateAdd("h",17,[Forms]![FormName]![StartDate]) and
DateAdd("h",29,[Forms]![FormName]![StartDate])

If you wanted multiple days you would need something more complex.
Criteria Under [Start Date]
Between [Forms]![FormName]![StartDate] and [Forms]![FormName]![EndDate]

Criteria under TimeDown
(TimeDown = #05:00:00# OR TimeDown =#19:00:00#)

Alternative criteria for TimeDown
NOT BETWEEN #05:00:01# and #16:59:59#


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

PamB wrote:
Access 2007
I have a table that contains (among other things)
Start Date
TimeDown
End Date
TimeUp

All of these are Date/Time type

Example data:

Start Date TimeDown End Date TimeUp
16/3/10 02:00 16/3/10 04:00
16/3/10 06:00 16/3/10 09:00
16/3/10 19:00 16/3/10 20:30
16/3/10 23:00 17/3/10 01:00
17/3/10 03:00 17/3/10 04:00
17/3/10 08:45 17/3/10 09:30

I need to be able to return only those Start Date & Time Values that occur
between 5pm on the first date and 5am on the second date. In the above
example data, this would be the 3rd, 4th and 5th lines. This is for a report
that covers Night shift.

I have a select query with constants:
Field:
NightShift: [Start Date]+[TimeDown]
Criteria:
Between #16/03/2010 5:00:00 PM# And #17/03/2010 5:00:00 AM#

and this seems to work.

What I need to be able to do is have a user input a date range (using a form
for Start Date and End Date - which I can do) but have only the time frame
5pm on the Start Date to 5am on the End Date.

I would really appreciate any assistance as I'm still muddling my way
through...

TIA

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.