Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-17-2019, 09:18 PM   #1
kanxay
Newly Registered User
 
Join Date: May 2019
Posts: 13
Thanks: 16
Thanked 0 Times in 0 Posts
kanxay is on a distinguished road
range period of time in a date query access

How can I range period of time in a date query access
For example search for 8:00 am until 13:00 pm of 15/4/2019

kanxay is offline   Reply With Quote
Old 05-17-2019, 09:19 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,732
Thanks: 33
Thanked 652 Times in 635 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: range period of time in a date query access

What does your data look like?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
kanxay (05-18-2019)
Old 05-17-2019, 09:33 PM   #3
kanxay
Newly Registered User
 
Join Date: May 2019
Posts: 13
Thanks: 16
Thanked 0 Times in 0 Posts
kanxay is on a distinguished road
Re: range period of time in a date query access

Can I write ! [ Enter the date] between [ enter start hours] and [ enter end hours]
Attached Images
File Type: png kkk.png (41.1 KB, 24 views)

kanxay is offline   Reply With Quote
Old 05-17-2019, 11:33 PM   #4
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,654
Thanks: 2
Thanked 2,053 Times in 2,008 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: range period of time in a date query access

I only see a date, no time period!
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
kanxay (05-18-2019)
Old 05-18-2019, 06:00 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,834
Thanks: 78
Thanked 1,540 Times in 1,428 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: range period of time in a date query access

Two issues:

1. The syntax for that type of query would be a WHERE clause with a BETWEEN x AND y criterion. You would probably want to synthesize the query using substitution, or you could have those two dates in form controls, in which case you could reference the form controls in the SQL. Note, however, that the "build via substitution" ends up running better most of the time. We also don't know how you intend to use the dates (i.e. for display, for update, for report generation, for select export...)

2. You need to understand DATE fields (and DATE variables, for that matter). They must be handled specially in any query, particularly when dealing with manually entered date and time taken from ANY text-oriented input source (like a text box or an input box or the automated parameter box associated with SQL's undefined parameters.)

We cannot tell your level of expertise so it is hard to know the best way to frame a more detailed answer.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
kanxay (05-18-2019)
Old 05-18-2019, 06:49 AM   #6
kanxay
Newly Registered User
 
Join Date: May 2019
Posts: 13
Thanks: 16
Thanked 0 Times in 0 Posts
kanxay is on a distinguished road
Re: range period of time in a date query access

Each day we sell a variety of products
And We want to use the query to see and select the morning and evening of each day. I'm sorry I can't explain clearly

I just only know this rule [ enter the date ] .
But I want to know more about morning time and night time if that date
kanxay is offline   Reply With Quote
Old 05-18-2019, 06:54 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,732
Thanks: 33
Thanked 652 Times in 635 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: range period of time in a date query access

Quote:
Originally Posted by kanxay View Post
Each day we sell a variety of products
And We want to use the query to see and select the morning and evening of each day. I'm sorry I can't explain clearly
As JHB said though, how can we tell which records belong in the morning and which ones in the evening if there are no time components in the data? Or is there, and you just haven't shown it to us yet?

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
kanxay (05-18-2019)
Old 05-18-2019, 06:56 AM   #8
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,605
Thanks: 388
Thanked 622 Times in 603 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: range period of time in a date query access

If do not have a time element in the date field of the table, you cannot do that.?
Your heading shows Date and Time, but only the date element is showing.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
kanxay (05-18-2019)
Old 05-18-2019, 07:03 AM   #9
kanxay
Newly Registered User
 
Join Date: May 2019
Posts: 13
Thanks: 16
Thanked 0 Times in 0 Posts
kanxay is on a distinguished road
Re: range period of time in a date query access

Excuse , if I have the date and time like this , can I select the date and the period of time ?
Attached Images
File Type: jpg IMG_20190518_215814.jpg (92.6 KB, 18 views)
File Type: jpg IMG_20190518_215928.jpg (92.1 KB, 15 views)

Last edited by kanxay; 05-18-2019 at 07:27 AM.
kanxay is offline   Reply With Quote
Old 05-18-2019, 07:43 AM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,732
Thanks: 33
Thanked 652 Times in 635 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: range period of time in a date query access

Quote:
Originally Posted by kanxay View Post
Excuse , if I have the date and time like this , can I select the date and the period of time ?
Hi. Thanks for the update. Try it this way:
Code:
Between "#" & [Enter Date] & " " & [Enter Start Hour] & "#" And "#" & [Enter Date] & " " & [Enter End Hour] & "#"
(untested)
It might actually be easier to avoid user input error if you use a dropdown on a form to select the times.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
kanxay (05-18-2019)
Old 05-18-2019, 08:17 AM   #11
kanxay
Newly Registered User
 
Join Date: May 2019
Posts: 13
Thanks: 16
Thanked 0 Times in 0 Posts
kanxay is on a distinguished road
Re: range period of time in a date query access

Dear "theDBguy" I can't use the drop down in form because the lists product are too long for each day
And your suggestion it doesn't work ???
Attached Images
File Type: jpg IMG_20190518_231155.jpg (93.1 KB, 12 views)
kanxay is offline   Reply With Quote
Old 05-18-2019, 08:25 AM   #12
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,732
Thanks: 33
Thanked 652 Times in 635 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: range period of time in a date query access

Quote:
Originally Posted by kanxay View Post
Dear "theDBguy" I can't use the drop down in form because the lists product are too long for each day
And your suggestion it doesn't work ???
Interesting. I just gave it a try and didn't get an error with it. There are only 24 hours in a day, what do you mean the list will be too long? I was saying have the user select the hours instead of typing them to avoid user errors. It would really help if you could post a sample db.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
kanxay (05-18-2019)
Old 05-18-2019, 09:53 AM   #13
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,834
Thanks: 78
Thanked 1,540 Times in 1,428 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: range period of time in a date query access

OK, here is the problem you need to consider: Date/Time are actually not separately testable in the way you are forming the question. However, let's explore you REAL needs. You mentioned "morning" and "evening" times. Is that always the same cutoff time? I.e. is "Morning" ALWAYS anything before Noon and "Evening" ALWAYS anything after Noon? Or is there some other fixed cutoff time? Because if so, then you only need to separately ask for an input date of interest, perhaps with an Input Box, then build something like

Code:
SELECT ..... FROM .....
WHERE Time BETWEEN #" & [InputDate] & " 0:00:00# AND #" & [InputDate] & " 11:59:59# .... ;

or

SELECT ..... FROM .....
WHERE Time BETWEEN #" & [InputDate] & " 12:00:00# AND #" & [InputDate] & " 23:59:59# .... ;
Obviously, I am using Noon as that fixed cutoff point. If you have an input box before you run this query, ask the date once but use it twice. It will lead to less confusion and annoyance.

Something I noted in passing from your posted JPG files... you have committed one of the sins of the inexperienced Access programmer. The field name TIME is a reserved word that overlaps with certain functions. Call it SalesTime or STime or some other name, but the name Time is already taken. When you commit a sin, you must of course do penance. In this case, the penance is tearing your hair out when Access decides to blow up on you (or just do strange stuff) because of that reserved name. We won't impose the penance. Access itself will.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
The Following 2 Users Say Thank You to The_Doc_Man For This Useful Post:
kanxay (05-18-2019), Tera (05-18-2019)
Old 05-18-2019, 10:05 AM   #14
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 614
Thanks: 3
Thanked 124 Times in 119 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: range period of time in a date query access

Other issues may be at play since Time is a reserved word and shouldn't be used to name anything. If you were to attempt something like
WHERE Time BETWEEN #" I would think brackets would be mandatory

WHERE [Time] BETWEEN #"
otherwise you'd be invoking the Time function. The best approach is to NOT use reserved names. Suggest you bookmark

http://allenbrowne.com/AppIssueBadWord.html
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
kanxay (05-18-2019)
Old 05-18-2019, 08:32 PM   #15
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,654
Thanks: 2
Thanked 2,053 Times in 2,008 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: range period of time in a date query access

Run the query in the attached database, input [Start date and start time] and [End date and end time].
Attached Files
File Type: accdb Hospital Invoice 2019.accdb (788.0 KB, 8 views)

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following 2 Users Say Thank You to JHB For This Useful Post:
kanxay (05-19-2019), Tera (05-18-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate percentages over a period of time in a microsoft access query wire_jp Queries 2 08-31-2016 03:00 PM
query with time calculation based on a date range sanych10 Queries 0 06-01-2011 05:14 AM
Date/time period criteria confusion dmc12 Queries 1 01-30-2008 04:40 PM
Repeating date entries for a given time period AdamO Queries 1 08-03-2005 06:15 AM
Date\Time period between last record in table and given Time interval AlLeX_Brd Queries 7 07-06-2005 11:38 AM




All times are GMT -8. The time now is 05:41 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World