By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

Date Falls Within a Range

P: 4
I have three fields in a Union Access Query (ID, Begin_Date, End_Date). I want to modify the query to include a parameter date to be entered by the user and produce a result if the parameter date falls within the range of Begin_Date to End_Date. Any ideas?
Sep 20 '07 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,186
If you post your current SQL for the query we not only get a bit of context within which to understand the request, but you also may end up with an answer more tailored to your needs.
Sep 20 '07 #2

P: 4
Sorry. Must be the Newbie in me. Here is the union query code:
Expand|Select|Wrap|Line Numbers
  1. SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] FROM Client_Count_Current_Placement
  2. UNION SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] FROM Client_Count_Historical_Placement;
Now I want to have a user input a parameter date, and if this parameter date falls within the range of BEGIN_DATE to END_DATE, only those records would show. This would all be done within a query.

Thanks for any help and sorry for the growing pains.
Sep 20 '07 #3

Expert 100+
P: 296
Sorry. Must be the Newbie in me. Here is the union query code:

SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] FROM Client_Count_Current_Placement
UNION SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] FROM Client_Count_Historical_Placement;

Now I want to have a user input a parameter date, and if this parameter date falls within the range of BEGIN_DATE to END_DATE, only those records would show. This would all be done within a query.

Thanks for any help and sorry for the growing pains.
I have very little experience with union queries but this should work ...NeoPa, correct me if I'm wrong.
Try this:

Expand|Select|Wrap|Line Numbers
  1. SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] 
  2. FROM Client_Count_Current_Placement
  3. WHERE [Begin_Date]>=CDate([Enter the beginning date]) AND [End_Date]<=CDate([Enter the ending date])
  4. UNION 
  5. SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] 
  6. FROM Client_Count_Historical_Placement
  7. WHERE [Begin_Date]>=CDate([Enter the beginning date]) AND [End_Date]<=CDate([Enter the ending date]);
Sep 20 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
I have very little experience with union queries but this should work ...NeoPa, correct me if I'm wrong.
Of course Michelle ;)
I want to check up on where the WHERE clause comes for a UNION query so will be back shortly, but the OP is looking for a single date parameter which must fall between his two dates rather than a date range. I will be using the Between [A] And [b] construct for this but let me check the details in the help first. BRB.

PS. Don't be put off by this M. You were logically on the right lines and you were also very quick.
Sep 20 '07 #5

Expert 100+
P: 296
Of course Michelle ;)
I want to check up on where the WHERE clause comes for a UNION query so will be back shortly, but the OP is looking for a single date parameter which must fall between his two dates rather than a date range. I will be using the Between [A] And [b] construct for this but let me check the details in the help first. BRB.

PS. Don't be put off by this M. You were logically on the right lines and you were also very quick.
ahh..it helps if I read the Ops question properly!
Sep 20 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
It turns out that the WHERE clause is found WITHIN each SELECT statement so must be duplicated for both tables.
Expand|Select|Wrap|Line Numbers
  1. SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] FROM Client_Count_Current_Placement
  2. WHERE [Please enter Date] Between [BEGIN_DATE] And [END_DATE]
  3. UNION SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] FROM Client_Count_Historical_Placement
  4. WHERE [Please enter Date] Between [BEGIN_DATE] And [END_DATE]
Sep 20 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
If it is possible for the same [CLIENT_ID] to be in both tables then I suggest you use the ALL predicate of the UNION clause. This will ensure that all entries are returned. Otherwise (full) duplicates will be stripped automatically.

Michelle, don't worry about it. We're all learning lessons as we go.
Sep 20 '07 #8

Expert 100+
P: 296
If it is possible for the same [CLIENT_ID] to be in both tables then I suggest you use the ALL predicate of the UNION clause. This will ensure that all entries are returned. Otherwise (full) duplicates will be stripped automatically.

Michelle, don't worry about it. We're all learning lessons as we go.
I was actually about to post the same solution you just did, but saw you beat me to it! I'm not worried at all. I'm so new to Access and I know I have lots to learn!
Sep 20 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
I should have made that clearer. That last comment was a direct response to your previous comment.
ahh..it helps if I read the Ops question properly!
I wasn't commenting on your SQL offering as there was nothing to worry about there in the first place :)
Sep 20 '07 #10

P: 4
It turns out that the WHERE clause is found WITHIN each SELECT statement so must be duplicated for both tables.
Expand|Select|Wrap|Line Numbers
  1. SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] FROM Client_Count_Current_Placement
  2. WHERE [Please enter Date] Between [BEGIN_DATE] And [END_DATE]
  3. UNION SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] FROM Client_Count_Historical_Placement
  4. WHERE [Please enter Date] Between [BEGIN_DATE] And [END_DATE]
I appreciate everyone's help. It appears that the above SQL does work and produces the result I desire. Can you help me with this next question: Why am I prompted to enter the [Please enter Date] four times before the code will execute? I would like to just enter the [Please enter Date] once. Any help would be appreciated and thanks for the replies.
Sep 21 '07 #11

NeoPa
Expert Mod 15k+
P: 31,186
That does surprise me. Can you try saving this SQL in a query and just double-clicking on it to run.
What happens then?
Sep 21 '07 #12

P: 4
That does surprise me. Can you try saving this SQL in a query and just double-clicking on it to run.
What happens then?
Same occurrence. I have to input the same date within the same dialog box four times before the query runs. Not sure if this has to do with the underlying union query.
Sep 21 '07 #13

Expert 100+
P: 296
I don't know what would be causing that, but with my own database, I have noticed that if I create a query with a prompt and then make a bunch of changes, it sometimes requests the prompt more than once. What I do to fix it is to delete the prompt code, run the query without a prompt, then put the prompt code back in. It's worked for me every time, I just don't understand why it does it.
Sep 21 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.