Date Falls Within a Range

[citizenprice]

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?

[NeoPa]

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.

[citizenprice]

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.

[mlcampeau]

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:

SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] 
FROM Client_Count_Current_Placement
WHERE [Begin_Date]>=CDate([Enter the beginning date]) AND [End_Date]<=CDate([Enter the ending date])
UNION 
SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] 
FROM Client_Count_Historical_Placement
WHERE [Begin_Date]>=CDate([Enter the beginning date]) AND [End_Date]<=CDate([Enter the ending date]);

[NeoPa]

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.

[mlcampeau]

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!

[NeoPa]

It turns out that the WHERE clause is found WITHIN each SELECT statement so must be duplicated for both tables.

SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] FROM Client_Count_Current_Placement
WHERE [Please enter Date] Between [BEGIN_DATE] And [END_DATE]
UNION SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] FROM Client_Count_Historical_Placement
WHERE [Please enter Date] Between [BEGIN_DATE] And [END_DATE]

[NeoPa]

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.

[mlcampeau]

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!

[NeoPa]

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 :)

[citizenprice]

It turns out that the WHERE clause is found WITHIN each SELECT statement so must be duplicated for both tables.

SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] FROM Client_Count_Current_Placement
WHERE [Please enter Date] Between [BEGIN_DATE] And [END_DATE]
UNION SELECT [CLIENT_ID], [BEGIN_DATE], [END_DATE] FROM Client_Count_Historical_Placement
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.

[NeoPa]

That does surprise me. Can you try saving this SQL in a query and just double-clicking on it to run.
What happens then?

[citizenprice]

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.

[mlcampeau]

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.