469,632 Members | 1,639 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,632 developers. It's quick & easy.

Date Falls Within a Range

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
13 4309
NeoPa
32,202 Expert Mod 16PB
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
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
mlcampeau
296 Expert 100+
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
32,202 Expert Mod 16PB
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
mlcampeau
296 Expert 100+
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
32,202 Expert Mod 16PB
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
32,202 Expert Mod 16PB
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
mlcampeau
296 Expert 100+
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
32,202 Expert Mod 16PB
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
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
32,202 Expert Mod 16PB
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
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
mlcampeau
296 Expert 100+
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.

Similar topics

2 posts views Thread by androtech | last post: by
4 posts views Thread by Stewart Allen | last post: by
6 posts views Thread by alexanderpope11 | last post: by
18 posts views Thread by dfetrow410 | last post: by
10 posts views Thread by Andrew | last post: by
2 posts views Thread by =?Utf-8?B?TWlrZQ==?= | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.