User Supplied Parameter Query

[Carla Gilless]

I've created a employee training database and am having trouble with the user
supplied parameter query. A couple of issues: I have an unbound form
(AdHoc) for user to select parameters to pass to the underlying query to open
report. The form, query, report open correctly, but only 2 of the supplied
parameters return data. (BegDat and End/Date work as does ClassName.
ClassName is a combo box on the unbound form tied to a simple query of
ClassName.) Sql code is below, but first a couple of questions.

1) Loc and BU don't supply data. Is something off in the code below?
2) I want Loc and BU to be combo boxes, however since multiple instances of
both can be from one class, how do I restrict the dropdown list to only one?
In other words, 5 students from loc SF are in the class I've chosen, so the
dropdown list for LOC shows 5 SF's. I want only 1 SF to show up. Is this
possible and how do I code the combo box? Same issue with BU--multiple
students from FIN may attend the class, but I want the combo box to only show
1 FIN.
3) I have 3 checkboxes in the database from tblStudents and Classes that
are lookups to tblResults--Enrolled, Attended and Cancelled. I would like
these to be choices on the unbound form too. How do I code for a checkbox on
the form so that if it is true (or yes value), the report will then find only
those students who enrolled or attended or cancelled for a particular class?
Any help would be greatly appreciated. Thanks.

SELECT Classes.Date, Classes.ClassName, Classes.Time, [Students And
Classes].EmplID, Students.Loc, [Students And Classes].EmplID, [Students And
Classes].Enrolled, [Students And Classes].Attended, [Students And
Classes].Cancellation, Students.BU
FROM Students INNER JOIN (Classes INNER JOIN [Students And Classes] ON
Classes.ClassID = [Students And Classes].ClassID) ON Students.EmplID =
[Students And Classes].EmplID
WHERE (((Classes.Date) Between [Forms]![AdHoc]![BegDate] And
[Forms]![AdHoc]![EndDate]) AND
((Classes.ClassName)=[Forms]![AdHoc]![ClassName]) AND
((Students.Loc)=[Forms]![AdHoc]![Loc] Or (Students.Loc) Like
[Forms]![AdHoc]![Loc] Is Null) AND ((Students.BU)=[Forms]![AdHoc]![BU] Or
(Students.BU) Like [Forms]![AdHoc]![BU] Is Null)) OR
(((Classes.ClassName)=[Forms]![AdHoc]![ClassName])) OR (((Classes.Date)
Between [Forms]![AdHoc]![BegDate] And [Forms]![AdHoc]![EndDate]));

 

[SteveS]

Comments in-line

I've created a employee training database and am having trouble with the user
supplied parameter query. A couple of issues: I have an unbound form
(AdHoc) for user to select parameters to pass to the underlying query to open
report. The form, query, report open correctly, but only 2 of the supplied
parameters return data. (BegDat and End/Date work as does ClassName.
ClassName is a combo box on the unbound form tied to a simple query of
ClassName.) Sql code is below, but first a couple of questions.

1) Loc and BU don't supply data. Is something off in the code below?

I think you should remove "(Students.BU) Like [Forms]![AdHoc]![Bu] Is Null"
and "(Students.Loc) Like [Forms]![AdHoc]![Loc] Is Null".

Just use
------ SNIP --------
((Classes.ClassName)=[Forms]![AdHoc]![ClassName]) AND
((Students.Loc)=[Forms]![AdHoc]![Loc] Or [Forms]![AdHoc]![Loc] Is Null)
AND ((Students.BU)=[Forms]![AdHoc]![BU] Or [Forms]![AdHoc]![BU] Is Null)) OR
------ SNIP ------

If you evaluate this

(Students.Loc) Like [Forms]![AdHoc]![Loc] Is Null

the result of ([Forms]![AdHoc]![Loc] Is Null) is either TRUE or FALSE.
So, it evaluates to (Students.Loc) Like TRUE, which will returns 0 records
(assuming LOC is a text field).

you don't want to see if Students.Loc is true, you want a TRUE if the
unbound control ([Forms]![AdHoc]![Loc]) is NULL. (Same for [BU])


Look at this site:

http://www.mvps.org/access/queries/qry0001.htm

2) I want Loc and BU to be combo boxes, however since multiple instances of
both can be from one class, how do I restrict the dropdown list to only one?
In other words, 5 students from loc SF are in the class I've chosen, so the
dropdown list for LOC shows 5 SF's. I want only 1 SF to show up. Is this
possible and how do I code the combo box? Same issue with BU--multiple
students from FIN may attend the class, but I want the combo box to only show
1 FIN.

Open the properties for the combo box. Open the ROW SOURCE query. Switch to
SQL View. Change "Select ..." to "Select Distinct..."

Save the query.

3) I have 3 checkboxes in the database from tblStudents and Classes that
are lookups to tblResults--Enrolled, Attended and Cancelled. I would like
these to be choices on the unbound form too. How do I code for a checkbox on
the form so that if it is true (or yes value), the report will then find only
those students who enrolled or attended or cancelled for a particular class?
Any help would be greatly appreciated. Thanks.

This is a tuff one. I would have used a text field and used an option group
on a form to select the options. (but I don't have any idea why you used
three check boxes, so I might be all wet )

These are the combinations you have to check:

ENROLLED ATTENDED CANCELLED
---------------------------------------------
F F F
F F T
F T F
F T T
T F F
T F T
T T F
T T T

Maybe you could do something like:

([tblResults].[Enrolled] = [Forms]![AdHoc]![chkEnrolled] OR
[tblResults].[Attended]= [Forms]![AdHoc]![chkAttended] OR
[tblResults].[Cancelled]= [Forms]![AdHoc]![chkCancel] )

Would you ever have a case where a student didn't enroll and didn't attend
and didn't cancel?


Also, in the SQL below, did you know you have [Students And Classes].EmplID
twice in the Select clause?

SELECT Classes.Date, Classes.ClassName, Classes.Time, [Students And
Classes].EmplID, Students.Loc, [Students And Classes].EmplID, [Students And
Classes].Enrolled, [Students And Classes].Attended, [Students And
Classes].Cancellation, Students.BU
FROM Students INNER JOIN (Classes INNER JOIN [Students And Classes] ON
Classes.ClassID = [Students And Classes].ClassID) ON Students.EmplID =
[Students And Classes].EmplID
WHERE (((Classes.Date) Between [Forms]![AdHoc]![BegDate] And
[Forms]![AdHoc]![EndDate]) AND
((Classes.ClassName)=[Forms]![AdHoc]![ClassName]) AND
((Students.Loc)=[Forms]![AdHoc]![Loc] Or (Students.Loc) Like
[Forms]![AdHoc]![Loc] Is Null) AND ((Students.BU)=[Forms]![AdHoc]![BU] Or
(Students.BU) Like [Forms]![AdHoc]![BU] Is Null)) OR
(((Classes.ClassName)=[Forms]![AdHoc]![ClassName])) OR (((Classes.Date)
Between [Forms]![AdHoc]![BegDate] And [Forms]![AdHoc]![EndDate]));

If I express myself clearly enough, let me know; I'll try again.

HTH

 

[Carla Gilless]

SteveS,
Thanks for your reply. I did catch the extra EmplID in the Select Clause
and figured out how to use the Select Distinct after I posted my question.
On the query, I've tried your code, but now I only get the data back if all 4
of the fields in the AdHoc form are filled out.
What I'm attempting is to let the user choose 1, 2, 3 or 4 of the fields in
any combination. Beg and End date alone, or in combo with any of the other
fields, and the same with each of the fields. With your suggested code
below, what do I need to change to allow for this?

SELECT Classes.Date, Classes.ClassName, Classes.Time, [Students And
Classes].EmplID, Students.Loc, [Students And Classes].Enrolled, [Students And
Classes].Attended, [Students And Classes].Cancellation, Students.BU
FROM Students INNER JOIN (Classes INNER JOIN [Students And Classes] ON
Classes.ClassID = [Students And Classes].ClassID) ON Students.EmplID =
[Students And Classes].EmplID
WHERE (((Classes.Date) Between [Forms]![AdHoc]![BegDate] And
[Forms]![AdHoc]![EndDate]) AND
((Classes.ClassName)=[Forms]![AdHoc]![ClassName]) AND
((Students.Loc)=[Forms]![AdHoc]![Loccbo] or [Forms]![AdHoc]![Loccbo] Is Null)
AND ((Students.BU)=[Forms]![AdHoc]![BUcbo] or [Forms]![AdHoc]![BUcbo] Is
Null));

On the checkboxes, I thought of option groups, but again I will want the
user to chose 1 or 2 of the three options--either enrolled and cancelled,
enrolled and attended, or cancelled alone. As I understand option groups,
only 1 option can be true. I haven't tried your suggested code for this yet,
as I am still stuck on getting the AdHoc report to run correctly.

Thanks for your help.
--
Carla

Comments in-line

I've created a employee training database and am having trouble with the user
supplied parameter query. A couple of issues: I have an unbound form
(AdHoc) for user to select parameters to pass to the underlying query to open
report. The form, query, report open correctly, but only 2 of the supplied
parameters return data. (BegDat and End/Date work as does ClassName.
ClassName is a combo box on the unbound form tied to a simple query of
ClassName.) Sql code is below, but first a couple of questions.

1) Loc and BU don't supply data. Is something off in the code below?

I think you should remove "(Students.BU) Like [Forms]![AdHoc]![Bu] Is Null"
and "(Students.Loc) Like [Forms]![AdHoc]![Loc] Is Null".

Just use
------ SNIP --------
((Classes.ClassName)=[Forms]![AdHoc]![ClassName]) AND
((Students.Loc)=[Forms]![AdHoc]![Loc] Or [Forms]![AdHoc]![Loc] Is Null)
AND ((Students.BU)=[Forms]![AdHoc]![BU] Or [Forms]![AdHoc]![BU] Is Null)) OR
------ SNIP ------

If you evaluate this

(Students.Loc) Like [Forms]![AdHoc]![Loc] Is Null

the result of ([Forms]![AdHoc]![Loc] Is Null) is either TRUE or FALSE.
So, it evaluates to (Students.Loc) Like TRUE, which will returns 0 records
(assuming LOC is a text field).

you don't want to see if Students.Loc is true, you want a TRUE if the
unbound control ([Forms]![AdHoc]![Loc]) is NULL. (Same for [BU])


Look at this site:

http://www.mvps.org/access/queries/qry0001.htm

2) I want Loc and BU to be combo boxes, however since multiple instances of
both can be from one class, how do I restrict the dropdown list to only one?
In other words, 5 students from loc SF are in the class I've chosen, so the
dropdown list for LOC shows 5 SF's. I want only 1 SF to show up. Is this
possible and how do I code the combo box? Same issue with BU--multiple
students from FIN may attend the class, but I want the combo box to only show
1 FIN.

Open the properties for the combo box. Open the ROW SOURCE query. Switch to
SQL View. Change "Select ..." to "Select Distinct..."

Save the query.

3) I have 3 checkboxes in the database from tblStudents and Classes that
are lookups to tblResults--Enrolled, Attended and Cancelled. I would like
these to be choices on the unbound form too. How do I code for a checkbox on
the form so that if it is true (or yes value), the report will then find only
those students who enrolled or attended or cancelled for a particular class?
Any help would be greatly appreciated. Thanks.

This is a tuff one. I would have used a text field and used an option group
on a form to select the options. (but I don't have any idea why you used
three check boxes, so I might be all wet )

These are the combinations you have to check:

ENROLLED ATTENDED CANCELLED
---------------------------------------------
F F F
F F T
F T F
F T T
T F F
T F T
T T F
T T T

Maybe you could do something like:

([tblResults].[Enrolled] = [Forms]![AdHoc]![chkEnrolled] OR
[tblResults].[Attended]= [Forms]![AdHoc]![chkAttended] OR
[tblResults].[Cancelled]= [Forms]![AdHoc]![chkCancel] )

Would you ever have a case where a student didn't enroll and didn't attend
and didn't cancel?


Also, in the SQL below, did you know you have [Students And Classes].EmplID
twice in the Select clause?

SELECT Classes.Date, Classes.ClassName, Classes.Time, [Students And
Classes].EmplID, Students.Loc, [Students And Classes].EmplID, [Students And
Classes].Enrolled, [Students And Classes].Attended, [Students And
Classes].Cancellation, Students.BU
FROM Students INNER JOIN (Classes INNER JOIN [Students And Classes] ON
Classes.ClassID = [Students And Classes].ClassID) ON Students.EmplID =
[Students And Classes].EmplID
WHERE (((Classes.Date) Between [Forms]![AdHoc]![BegDate] And
[Forms]![AdHoc]![EndDate]) AND
((Classes.ClassName)=[Forms]![AdHoc]![ClassName]) AND
((Students.Loc)=[Forms]![AdHoc]![Loc] Or (Students.Loc) Like
[Forms]![AdHoc]![Loc] Is Null) AND ((Students.BU)=[Forms]![AdHoc]![BU] Or
(Students.BU) Like [Forms]![AdHoc]![BU] Is Null)) OR
(((Classes.ClassName)=[Forms]![AdHoc]![ClassName])) OR (((Classes.Date)
Between [Forms]![AdHoc]![BegDate] And [Forms]![AdHoc]![EndDate]));

If I express myself clearly enough, let me know; I'll try again.

HTH

 

[SteveS]

SteveS,
Thanks for your reply. I did catch the extra EmplID in the Select Clause
and figured out how to use the Select Distinct after I posted my question.
On the query, I've tried your code, but now I only get the data back if all 4
of the fields in the AdHoc form are filled out.
What I'm attempting is to let the user choose 1, 2, 3 or 4 of the fields in
any combination. Beg and End date alone, or in combo with any of the other
fields, and the same with each of the fields. With your suggested code
below, what do I need to change to allow for this?

SELECT Classes.Date, Classes.ClassName, Classes.Time, [Students And
Classes].EmplID, Students.Loc, [Students And Classes].Enrolled, [Students And
Classes].Attended, [Students And Classes].Cancellation, Students.BU
FROM Students INNER JOIN (Classes INNER JOIN [Students And Classes] ON
Classes.ClassID = [Students And Classes].ClassID) ON Students.EmplID =
[Students And Classes].EmplID
WHERE (((Classes.Date) Between [Forms]![AdHoc]![BegDate] And
[Forms]![AdHoc]![EndDate]) AND
((Classes.ClassName)=[Forms]![AdHoc]![ClassName]) AND
((Students.Loc)=[Forms]![AdHoc]![Loccbo] or [Forms]![AdHoc]![Loccbo] Is Null)
AND ((Students.BU)=[Forms]![AdHoc]![BUcbo] or [Forms]![AdHoc]![BUcbo] Is
Null));

On the checkboxes, I thought of option groups, but again I will want the
user to chose 1 or 2 of the three options--either enrolled and cancelled,
enrolled and attended, or cancelled alone. As I understand option groups,
only 1 option can be true. I haven't tried your suggested code for this yet,
as I am still stuck on getting the AdHoc report to run correctly.

Thanks for your help.

Carla,


First ,try using this clause:

WHERE (Students.Loc=[Forms]![AdHoc]![Loccbo] or [Forms]![AdHoc]![Loccbo] Is
Null) AND (Students.BU =[Forms]![AdHoc]![BUcbo] or [Forms]![AdHoc]![BUcbo] Is
Null);

If it works for selecting BU or Loc, then try this clause:

WHERE (Classes.ClassName = [Forms]![AdHoc]![ClassName] OR
[Forms]![AdHoc]![ClassName] Is Null) AND
(Students.Loc=[Forms]![AdHoc]![Loccbo] or [Forms]![AdHoc]![Loccbo] Is Null)
AND (Students.BU =[Forms]![AdHoc]![BUcbo] or [Forms]![AdHoc]![BUcbo] Is Null);

Test by selecting combinations of: Classes, Bu and/or Loc.


When I need to limit records in a report, I use code to create a string
(strWhere) "on the fly" for use with the DoCmd.OpenReport command.

Example (AIR CODE):

'----- snip -------
Dim stDocName As String
Dim strWhere as string

' check for both dates entered, else ignore dates
If not IsNull([Forms]![AdHoc]![BegDate]) And Not
IsNull([Forms]![AdHoc]![EndDate]) Then
strWhere = "Classes.Date Between [Forms]![AdHoc]![BegDate] And
[Forms]![AdHoc]![EndDate] AND "
End If

' check for classes entry
If Not IsNull([Forms]![AdHoc]![ClassName]) Then
strWhere = strWhere & "Classes.ClassName = [Forms]![AdHoc]![ClassName] AND "
End If

'check for BUcbo entry
If Not IsNull(Students.Loc=[Forms]![AdHoc]![BUcbo]) Then
strWhere = strWhere & "Students.BU =[Forms]![AdHoc]![BUcbo] AND "
End IF

' check for Loccbo entry
If Not IsNull(Students.Loc=[Forms]![AdHoc]![Loccbo]) Then
strWhere = strWhere & "Students.BU =[Forms]![AdHoc]![Loccbo] AND "
End IF

' remove the " AND "
If Len(Trim(strWhere))>0 Then
strWhere = Left(strWhere, Len(Trim(strWhere))-5)

' change to name of your report
stDocName = "rptexcelClients"
DoCmd.OpenReport stDocName, acPreview, , strWhere

' to print without preview, delete "preview" but leave the preceeding comma
'DoCmd.OpenReport stDocName,, , strWhere

'------ snip ------

In the report row source query, delete the Where clause.




I think this is easier than trying to put all the options in the query.

 

[Carla Gilless]

SteveS,
Wonderful! The last coding worked fine--at least until I added back in the
Beg and End Dates. But, rather than spend more time with this, I've broken
it up into 2 separate User Reports. One that uses your code below for the
combinations of Class Name, Loc or BU and another that just does the date. A
little awkward but it works. I may fiddle with the date form and add in the
check boxes there. Or just move on to the next difficulty - tying in
prerequsite and mandatory classes to the employee level.
In any case, thank you so much for your helpful suggestions. This forum
always supplies me with answers in one way or another.
--
Carla

SteveS,
Thanks for your reply. I did catch the extra EmplID in the Select Clause
and figured out how to use the Select Distinct after I posted my question.
On the query, I've tried your code, but now I only get the data back if all 4
of the fields in the AdHoc form are filled out.
What I'm attempting is to let the user choose 1, 2, 3 or 4 of the fields in
any combination. Beg and End date alone, or in combo with any of the other
fields, and the same with each of the fields. With your suggested code
below, what do I need to change to allow for this?

SELECT Classes.Date, Classes.ClassName, Classes.Time, [Students And
Classes].EmplID, Students.Loc, [Students And Classes].Enrolled, [Students And
Classes].Attended, [Students And Classes].Cancellation, Students.BU
FROM Students INNER JOIN (Classes INNER JOIN [Students And Classes] ON
Classes.ClassID = [Students And Classes].ClassID) ON Students.EmplID =
[Students And Classes].EmplID
WHERE (((Classes.Date) Between [Forms]![AdHoc]![BegDate] And
[Forms]![AdHoc]![EndDate]) AND
((Classes.ClassName)=[Forms]![AdHoc]![ClassName]) AND
((Students.Loc)=[Forms]![AdHoc]![Loccbo] or [Forms]![AdHoc]![Loccbo] Is Null)
AND ((Students.BU)=[Forms]![AdHoc]![BUcbo] or [Forms]![AdHoc]![BUcbo] Is
Null));

On the checkboxes, I thought of option groups, but again I will want the
user to chose 1 or 2 of the three options--either enrolled and cancelled,
enrolled and attended, or cancelled alone. As I understand option groups,
only 1 option can be true. I haven't tried your suggested code for this yet,
as I am still stuck on getting the AdHoc report to run correctly.

Thanks for your help.

Carla,


First ,try using this clause:

WHERE (Students.Loc=[Forms]![AdHoc]![Loccbo] or [Forms]![AdHoc]![Loccbo] Is
Null) AND (Students.BU =[Forms]![AdHoc]![BUcbo] or [Forms]![AdHoc]![BUcbo] Is
Null);

If it works for selecting BU or Loc, then try this clause:

WHERE (Classes.ClassName = [Forms]![AdHoc]![ClassName] OR
[Forms]![AdHoc]![ClassName] Is Null) AND
(Students.Loc=[Forms]![AdHoc]![Loccbo] or [Forms]![AdHoc]![Loccbo] Is Null)
AND (Students.BU =[Forms]![AdHoc]![BUcbo] or [Forms]![AdHoc]![BUcbo] Is Null);

Test by selecting combinations of: Classes, Bu and/or Loc.


When I need to limit records in a report, I use code to create a string
(strWhere) "on the fly" for use with the DoCmd.OpenReport command.

Example (AIR CODE):

'----- snip -------
Dim stDocName As String
Dim strWhere as string

' check for both dates entered, else ignore dates
If not IsNull([Forms]![AdHoc]![BegDate]) And Not
IsNull([Forms]![AdHoc]![EndDate]) Then
strWhere = "Classes.Date Between [Forms]![AdHoc]![BegDate] And
[Forms]![AdHoc]![EndDate] AND "
End If

' check for classes entry
If Not IsNull([Forms]![AdHoc]![ClassName]) Then
strWhere = strWhere & "Classes.ClassName = [Forms]![AdHoc]![ClassName] AND "
End If

'check for BUcbo entry
If Not IsNull(Students.Loc=[Forms]![AdHoc]![BUcbo]) Then
strWhere = strWhere & "Students.BU =[Forms]![AdHoc]![BUcbo] AND "
End IF

' check for Loccbo entry
If Not IsNull(Students.Loc=[Forms]![AdHoc]![Loccbo]) Then
strWhere = strWhere & "Students.BU =[Forms]![AdHoc]![Loccbo] AND "
End IF

' remove the " AND "
If Len(Trim(strWhere))>0 Then
strWhere = Left(strWhere, Len(Trim(strWhere))-5)

' change to name of your report
stDocName = "rptexcelClients"
DoCmd.OpenReport stDocName, acPreview, , strWhere

' to print without preview, delete "preview" but leave the preceeding comma
'DoCmd.OpenReport stDocName,, , strWhere

'------ snip ------

In the report row source query, delete the Where clause.




I think this is easier than trying to put all the options in the query.

 

[SteveS]

SteveS,
Wonderful! The last coding worked fine--at least until I added back in the
Beg and End Dates. But, rather than spend more time with this, I've broken
it up into 2 separate User Reports. One that uses your code below for the
combinations of Class Name, Loc or BU and another that just does the date. A
little awkward but it works. I may fiddle with the date form and add in the
check boxes there. Or just move on to the next difficulty - tying in
prerequsite and mandatory classes to the employee level.
In any case, thank you so much for your helpful suggestions. This forum
always supplies me with answers in one way or another.

Caela,

Glad you are making progress. If you use code to make the strWhere clause,
you should be able to add code to add the check box selection. It would look
like

If [Forms]![AdHoc]![chkEnrolled]= TRUE Then
strWhere = strWhere & "[tblResults].[Enrolled] = TRUE AND "
End If

{same for chkAttended}

{same for chkCancel}


They would be before the IF() where you delete the last 5 characters (" AND ")

More air code ..... But I you'll learn more doing the debugging I know I
did..


HTH