Use Prompted variable more than once

C

CR

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.
 
A

Albert D. Kallal

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.
 
J

John Spencer

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
 
J

Jeff Boyce

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!txtYourUserEnteredDateControlName


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

CR

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
Hi,
Click to expand...
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.
Click to expand...
Query is
Click to expand...
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;
Click to expand...
This query doesn't actually work, errors with too complex, but
hopefully you get the jist of what I'm after.
Click to expand...
Thanks any help.
Click to expand...
Click to expand...

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
 
C

CR

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.
Click to expand...
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;
Click to expand...
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#
Click to expand...
And then your query could use that as if it were a table
Click to expand...
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;
Click to expand...
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Click to expand...
CR said:
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.
Click to expand...
Click to expand...

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
Click to expand...

Sorted this out. Thanks for the help
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax Error in From Clause 2
Enter Parameter Value Message Pops Up 4
Overlapping timeframe 3
Parameter/Criteria settings for percentage expression field 2
retain entry from parameter query (2 entries) for report display 3
More Help withthe WHERE Clause 2
Problem Using Dates in a Query 6
Crosstab query and report 14

Top