range period of time in a date query access (1 Viewer)
- Thread starter kanxay
- Start date
- Local time
- Today, 07:10
- Joined
- Feb 28, 2001
- Messages
- 27,140
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.
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.
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
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
- Local time
- Today, 05:10
- Joined
- Oct 29, 2018
- Messages
- 21,454
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?
- Local time
- Today, 05:10
- Joined
- Oct 29, 2018
- Messages
- 21,454
Hi. Thanks for the update. Try it this way:
Code:
Between "#" & [Enter Date] & " " & [Enter Start Hour] & "#" And "#" & [Enter Date] & " " & [Enter End Hour] & "#"It might actually be easier to avoid user input error if you use a dropdown on a form to select the times.
- Local time
- Today, 05:10
- Joined
- Oct 29, 2018
- Messages
- 21,454
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.
- Local time
- Today, 07:10
- Joined
- Feb 28, 2001
- Messages
- 27,140
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
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.
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.
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
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
Similar threads
