Parameter query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Existing database has the Parameter query"
[Forms]![switchboard]![Text60]"What does it mean?Some other existing queries
have used the above criteria. I am using version 2000. I need to specify
start date and end date in my query to find the count of no of days worked by
employees. emmployees and date worked are in two different tables and linked
by employee id.
 
This "[Forms]![switchboard]![Text60]" mean that
On form called "switchboard", you have a field called "Text60", and this
field used as a criteria for the queries.

So and data you insert into this field, while the switchboard form is open,
will be the criteria for the queries.
Remember that the criteria wont work if the form will be closed
 
If you want to use a form to enter the start and end dates you'd first create
an unbound form with two text boxes, txtStart and txtEnd say, which you'd
then refer to as parameters in a query in the same ay as in your example.
With date/time data it’s a good idea to declare the parameters as a date
entered in the form in short date format can otherwise be interpreted by the
query as an arithmetical expression and give the wrong results. So a query
which joins the two tables (called Employees and WorkRecord in this example)
and counts the days worked for each employee between two dates entered in a
form (called YourForm below) would go something like this:

PARAMETERS
Forms!YourForm!txtStart DATETIME,
Forms!YourForm!txtEnd DATETIME;
SELECT Employees.[Employee ID], FirstName, LastName, COUNT(*) AS DaysWorked
FROM Employees INNER JOIN WorkRecord
ON Employees.[Employee ID] = WorkRecord.[Employee ID]
WHERE DateWorked BETWEEN Forms!YourForm!txtStart
AND Forms!YourForm!txtEnd
GROUP BY Employees.[Employee ID], FirstName, LastName;

You can add a button to the form to open the query, or better, a form or
report based on the query.

Ken Sheridan
Stafford, England
 
Ofer and Ken thank you somuch I understood the concept and designed the query
and set the same parameter Between [Forms]![switchboard]![Text60] and
[Forms]![switchboard]![Text62] for total no of days worked. the SWitchboard
form is good in design view but in form view it is not showing anything. If i
remove the parameter query for the SQL query I made it is perfect in both the
views. I checked the prperties and it is yes for forms view allowable. Iwill
appreciate your help. thanks again.

Lalitha
 
I tried to open the switchboard manager and while i am in SB design view and
it displays a message saying SBM was unable to find a valid SB in this
database.

I will appreciate the help.

Lalitha

Lalitha said:
Ofer and Ken thank you somuch I understood the concept and designed the query
and set the same parameter Between [Forms]![switchboard]![Text60] and
[Forms]![switchboard]![Text62] for total no of days worked. the SWitchboard
form is good in design view but in form view it is not showing anything. If i
remove the parameter query for the SQL query I made it is perfect in both the
views. I checked the prperties and it is yes for forms view allowable. Iwill
appreciate your help. thanks again.

Lalitha

Lalitha said:
Existing database has the Parameter query"
[Forms]![switchboard]![Text60]"What does it mean?Some other existing queries
have used the above criteria. I am using version 2000. I need to specify
start date and end date in my query to find the count of no of days worked by
employees. emmployees and date worked are in two different tables and linked
by employee id.
Click to expand...
Click to expand...
 
Lalitha:

I would advise against using a switchboard form for entering the parameters.
While its possible to do so, switchboards operate in a very different way
from other forms and can be a bit difficult to amend when you want to do
something other than the standard operations covered by the switchboard
manager. I'd suggest you create a separate little unbound dialogue form and
put the text boxes and a button on this. You can open this dialogue form
from the switchboard form (you can use the switchboard manager to do this
once you've created the dialogue form).

I'm away for a couple of weeks from tomorrow, so this will be my last log-in
until then. Good luck.

Ken Sheridan
Stafford, England

Lalitha said:
I tried to open the switchboard manager and while i am in SB design view and
it displays a message saying SBM was unable to find a valid SB in this
database.

I will appreciate the help.

Lalitha

Lalitha said:
Ofer and Ken thank you somuch I understood the concept and designed the query
and set the same parameter Between [Forms]![switchboard]![Text60] and
[Forms]![switchboard]![Text62] for total no of days worked. the SWitchboard
form is good in design view but in form view it is not showing anything. If i
remove the parameter query for the SQL query I made it is perfect in both the
views. I checked the prperties and it is yes for forms view allowable. Iwill
appreciate your help. thanks again.

Lalitha

Lalitha said:
Existing database has the Parameter query"
[Forms]![switchboard]![Text60]"What does it mean?Some other existing queries
have used the above criteria. I am using version 2000. I need to specify
start date and end date in my query to find the count of no of days worked by
employees. emmployees and date worked are in two different tables and linked
by employee id.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top