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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|