Use Prompted variable more than once

Hi,

I have a query which prompts the user for a date. I want to then use
the entered date twice without calling for the variable to be entered
again.

Query is

SELECT Schools.SchoolName, BackupTracking.ServerName,
SchoolServers.ServerID, BackupTracking.BackupStatus,
BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers LEFT JOIN [SELECT
BackupTracking.* FROM BackupTracking
WHERE BackupTracking.Date BETWEEN [Enter Date] & " 18:00:00" AND
[Enter Date] & " 17:59:59) AS BackupTracking ON SchoolServers.ServerID
= BackupTracking.ServerID) ON Schools.SchoolID =
SchoolServers.SchoolID
ORDER BY Schools.SchoolName;

This query doesn't actually work, errors with too complex, but
hopefully you get the jist of what I'm after.

Thanks any help.

Ok, your problem here is two fold.

1 )You want to use the entered date more than once.

2) you also want at a time component to the date.

However your sample is somewhat confusing, because your second date time is
actually less than the first date time, and what happens if you're only
looking for today's data date?

Anyway what I would do is build a nice prompt form that allows you to enter
the date values into two *unbound* text boxes you place on the form called
txtStart and txtEnd date.

The problem here is your situations a little bit more complex, because
you're adding time components to what the user is actually going to enter.

I would then place a button on this prompt form that launches your report
you want, and at the same time who builds a "where" clause in code to limits
the data in the report.

(by the way while are at this, avoid the use of date in your tables, because
access gets confused between date values the date function, and the keyword
date -- all of these keywords are reserved, and MS access will really
struggle to understand if you actually mean the date function, or delete
field you're trying to use...

Anway, that we can put square brackets around the date field to limit this
issue, but for future reference I do suggest you avoid "date".

here is how the code behind the button that would build your filter and also
launched the report:

dim strWhere as string


strWhere = "[Date] between #" & format(me.txtDate,"mm/dd/yyyy") & "
18:00#" & _
" and #" & format(me.txtEnd,"mm/dd/yyyy") & " 17:59#"


docmd.OpenReport "nameOfReport",acViewPreview,,strWhere

Also, keep in mind that you must remove all the date props and parameters
and the actual query for the above to work. On the other hand removing all
this parameter crap from the SQL who will make your SQL a lot more readable,
and further you be able to use that SQL in more than one report when you
don't need those particular date ranges.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

You can't use a parameter in a subquery in the from clause. Well you might be
able to if it doesn't require the square brackets.

You can try the following
Parameters [Enter_Date] DateTime;
SELECT Schools.SchoolName
, BackupTracking.ServerName
, SchoolServers.ServerID
, BackupTracking.BackupStatus
, BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers
LEFT JOIN [SELECT BackupTracking.* FROM BackupTracking
WHERE BackupTracking.Date BETWEEN Enter_Date + #18:00:00# AND
Enter_Date + #17:59:59#]. AS BackupTracking
ON SchoolServers.ServerID= BackupTracking.ServerID)
ON Schools.SchoolID =SchoolServers.SchoolID
ORDER BY Schools.SchoolName;

You many need to use a two-query approach. Query one saved as (qOne - or
whatever name you choose)
Parameters [Enter Date] DateTime;
SELECT BackupTracking.*
FROM BackupTracking
WHERE BackupTracking.Date BETWEEN [Enter Date] + #18:00:00# AND
[Enter Date] + #17:59:59#

And then your query could use that as if it were a table

SELECT Schools.SchoolName
, BackupTracking.ServerName
, SchoolServers.ServerID
, BackupTracking.BackupStatus
, BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers
LEFT JOIN qOne AS BackupTracking
ON SchoolServers.ServerID= BackupTracking.ServerID)
ON Schools.SchoolID =SchoolServers.SchoolID
ORDER BY Schools.SchoolName;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

CR wrote:
Hi,

I have a query which prompts the user for a date. I want to then use
the entered date twice without calling for the variable to be entered
again.

Query is

SELECT Schools.SchoolName, BackupTracking.ServerName,
SchoolServers.ServerID, BackupTracking.BackupStatus,
BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers LEFT JOIN [SELECT
BackupTracking.* FROM BackupTracking
WHERE BackupTracking.Date BETWEEN [Enter Date] & " 18:00:00" AND
[Enter Date] & " 17:59:59) AS BackupTracking ON SchoolServers.ServerID
= BackupTracking.ServerID) ON Schools.SchoolID =
SchoolServers.SchoolID
ORDER BY Schools.SchoolName;

This query doesn't actually work, errors with too complex, but
hopefully you get the jist of what I'm after.

Thanks any help.

If you want to collect a parameter from a user and use it more than once,
the easiest way I've found is to use a form to collect the value. Then,
modify the query/ies to "look at" the form for their selection criterion,
with something like:

Forms!YourCollectionFormName!txtYourUserEnteredDat eControlName


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"CR" wrote in message
...
Hi,

I have a query which prompts the user for a date. I want to then use
the entered date twice without calling for the variable to be entered
again.

Query is

SELECT Schools.SchoolName, BackupTracking.ServerName,
SchoolServers.ServerID, BackupTracking.BackupStatus,
BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers LEFT JOIN [SELECT
BackupTracking.* FROM BackupTracking
WHERE BackupTracking.Date BETWEEN [Enter Date] & " 18:00:00" AND
[Enter Date] & " 17:59:59) AS BackupTracking ON SchoolServers.ServerID
= BackupTracking.ServerID) ON Schools.SchoolID =
SchoolServers.SchoolID
ORDER BY Schools.SchoolName;

This query doesn't actually work, errors with too complex, but
hopefully you get the jist of what I'm after.

Thanks any help.

On 14 May, 13:20, John Spencer wrote:
You can't use a parameter in a subquery in the from clause. Well you might be
able to if it doesn't require the square brackets.

You can try the following
Parameters [Enter_Date] DateTime;
SELECT Schools.SchoolName
, BackupTracking.ServerName
, SchoolServers.ServerID
, BackupTracking.BackupStatus
, BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers
LEFT JOIN [SELECT BackupTracking.* FROM BackupTracking
WHERE BackupTracking.Date BETWEEN Enter_Date + #18:00:00# AND
Enter_Date + #17:59:59#]. AS BackupTracking
ON SchoolServers.ServerID= BackupTracking.ServerID)
ON Schools.SchoolID =SchoolServers.SchoolID
ORDER BY Schools.SchoolName;

You many need to use a two-query approach. Query one saved as (qOne - or
whatever name you choose)
Parameters [Enter Date] DateTime;
SELECT BackupTracking.*
FROM BackupTracking
WHERE BackupTracking.Date BETWEEN [Enter Date] + #18:00:00# AND
[Enter Date] + #17:59:59#

And then your query could use that as if it were a table

SELECT Schools.SchoolName
, BackupTracking.ServerName
, SchoolServers.ServerID
, BackupTracking.BackupStatus
, BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers
LEFT JOIN qOne AS BackupTracking
ON SchoolServers.ServerID= BackupTracking.ServerID)
ON Schools.SchoolID =SchoolServers.SchoolID
ORDER BY Schools.SchoolName;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

CR wrote:
Hi,

I have a query which prompts the user for a date. I want to then use
the entered date twice without calling for the variable to be entered
again.

Query is

SELECT Schools.SchoolName, BackupTracking.ServerName,
SchoolServers.ServerID, BackupTracking.BackupStatus,
BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers LEFT JOIN [SELECT
BackupTracking.* FROM BackupTracking
WHERE BackupTracking.Date BETWEEN [Enter Date] & " 18:00:00" AND
[Enter Date] & " 17:59:59) AS BackupTracking ON SchoolServers.ServerID
= BackupTracking.ServerID) ON Schools.SchoolID =
SchoolServers.SchoolID
ORDER BY Schools.SchoolName;

This query doesn't actually work, errors with too complex, but
hopefully you get the jist of what I'm after.

Thanks any help.

Thanks for replies.

Here is where I'm at now..

I have created forms to collect parameters, one for collects a
specific date for the parameter query.

Parameter query as follows
SELECT Schools.SchoolName, BackupTracking.ServerName,
SchoolServers.ServerID, BackupTracking.BackupStatus,
BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers LEFT JOIN (SELECT
BackupTracking.* FROM BackupTracking
WHERE BackupTracking.Date stWhere) AS BackupTracking ON
SchoolServers.ServerID = BackupTracking.ServerID) ON Schools.SchoolID
= SchoolServers.SchoolID
ORDER BY Schools.SchoolName;

The control button on the collection form has the following code:

Private Sub RunASSpecificDateQ_Click()
On Error GoTo Err_RunASSpecificDateQ_Click

Dim stDocName As String

stDocName = "BackupAnalysis_EnterDate"
stWhere = "BETWEEN #" & Me.SpecificDate & " 18:00:00#" & " AND #"
& Me.SpecificDate & " 11:59:59#"
DoCmd.OpenQuery stDocName, acViewNormal, acReadOnly
DoCmd.Maximize

Exit_RunASSpecificDateQ_Click:
Exit Sub

Err_RunASSpecificDateQ_Click:
MsgBox Err.Description
Resume Exit_RunASSpecificDateQ_Click

End Sub

This errors with property not found. I think it's due to not passing
the string stWhere to the query. So how do I do that?
Also I would actually like to add 1 day to the entered date for the
latter half of the where so, AND #" & Me.SpecificDate & " 11:59:59#
would be AND #" & Me.SpecificDate +1 & " 17:59:59#. So if use entered
date 07/05/2008 they would get records from 07/05/2008 18:00:00 to
08/05/2008 17:59:59. How do I do that?

Thanks agian

On 14 May, 15:31, CR wrote:
On 14 May, 13:20, John Spencer wrote:



You can't use a parameter in a subquery in the from clause. Well you might be
able to if it doesn't require the square brackets.

You can try the following
Parameters [Enter_Date] DateTime;
SELECT Schools.SchoolName
, BackupTracking.ServerName
, SchoolServers.ServerID
, BackupTracking.BackupStatus
, BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers
LEFT JOIN [SELECT BackupTracking.* FROM BackupTracking
WHERE BackupTracking.Date BETWEEN Enter_Date + #18:00:00# AND
Enter_Date + #17:59:59#]. AS BackupTracking
ON SchoolServers.ServerID= BackupTracking.ServerID)
ON Schools.SchoolID =SchoolServers.SchoolID
ORDER BY Schools.SchoolName;

You many need to use a two-query approach. Query one saved as (qOne - or
whatever name you choose)
Parameters [Enter Date] DateTime;
SELECT BackupTracking.*
FROM BackupTracking
WHERE BackupTracking.Date BETWEEN [Enter Date] + #18:00:00# AND
[Enter Date] + #17:59:59#

And then your query could use that as if it were a table

SELECT Schools.SchoolName
, BackupTracking.ServerName
, SchoolServers.ServerID
, BackupTracking.BackupStatus
, BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers
LEFT JOIN qOne AS BackupTracking
ON SchoolServers.ServerID= BackupTracking.ServerID)
ON Schools.SchoolID =SchoolServers.SchoolID
ORDER BY Schools.SchoolName;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

CR wrote:
Hi,

I have a query which prompts the user for a date. I want to then use
the entered date twice without calling for the variable to be entered
again.

Query is

SELECT Schools.SchoolName, BackupTracking.ServerName,
SchoolServers.ServerID, BackupTracking.BackupStatus,
BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers LEFT JOIN [SELECT
BackupTracking.* FROM BackupTracking
WHERE BackupTracking.Date BETWEEN [Enter Date] & " 18:00:00" AND
[Enter Date] & " 17:59:59) AS BackupTracking ON SchoolServers.ServerID
= BackupTracking.ServerID) ON Schools.SchoolID =
SchoolServers.SchoolID
ORDER BY Schools.SchoolName;

This query doesn't actually work, errors with too complex, but
hopefully you get the jist of what I'm after.

Thanks any help.

Thanks for replies.

Here is where I'm at now..

I have created forms to collect parameters, one for collects a
specific date for the parameter query.

Parameter query as follows
SELECT Schools.SchoolName, BackupTracking.ServerName,
SchoolServers.ServerID, BackupTracking.BackupStatus,
BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers LEFT JOIN (SELECT
BackupTracking.* FROM BackupTracking
WHERE BackupTracking.Date stWhere) AS BackupTracking ON
SchoolServers.ServerID = BackupTracking.ServerID) ON Schools.SchoolID
= SchoolServers.SchoolID
ORDER BY Schools.SchoolName;

The control button on the collection form has the following code:

Private Sub RunASSpecificDateQ_Click()
On Error GoTo Err_RunASSpecificDateQ_Click

Dim stDocName As String

stDocName = "BackupAnalysis_EnterDate"
stWhere = "BETWEEN #" & Me.SpecificDate & " 18:00:00#" & " AND #"
& Me.SpecificDate & " 11:59:59#"
DoCmd.OpenQuery stDocName, acViewNormal, acReadOnly
DoCmd.Maximize

Exit_RunASSpecificDateQ_Click:
Exit Sub

Err_RunASSpecificDateQ_Click:
MsgBox Err.Description
Resume Exit_RunASSpecificDateQ_Click

End Sub

This errors with property not found. I think it's due to not passing
the string stWhere to the query. So how do I do that?
Also I would actually like to add 1 day to the entered date for the
latter half of the where so, AND #" & Me.SpecificDate & " 11:59:59#
would be AND #" & Me.SpecificDate +1 & " 17:59:59#. So if use entered
date 07/05/2008 they would get records from 07/05/2008 18:00:00 to
08/05/2008 17:59:59. How do I do that?

Thanks agian

Sorted this out. Thanks for the help