Report parameters

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I am having a lot of difficulty specifying multiple report
parameters. I want to specify a name, a date range, and
four to six other criteria. I also need the option of
leaving any or all of the fields blank. I have based the
report on a query, and am using a form to enter the report
criteria. The criteria, then, are the names of the
controls on the form rather than the names of the fields.
I can specify the name, but for some reason cannot specify
a date range (or any other criteria, for that matter). A
day or two ago I could specify a date range, but for some
reason that no longer works (I have been experimenting
since then, but as far as I know have not changed that
part of the query). I thought about rebuilding the query,
but decided to seek some outside help first in case that
is a futile direction.
 
The On Open event of the report is
DoCmd.OpenForm "frmSpecifyRecord", , , , , acDialog. This
causes frmSpecifyRecord to appear. frmSpecifyRecord
contains cboLastFirst, a combo box that shows a list of
names from a query. The bound column is EmployeeID (the
PK from the Employee table); the visible column is Last,
First (a concatenated value). The report's record source
is a qryFindRecord. The criteria for the EmployeeID field
is [Forms]![frmSpecifyRecord]![cboLastFirst]. So far so
good. I open the report, frmSpecifyRecord shows up, I
select a name from cboLastFirst, click a command button
that hides frmSpecifyRecord, and that employee's training
record shows up.
Now, I want to limit the date range of the report.
Another field in the query is SessionDate. If I use
Between [Start] And [End] as the criteria, I get the
parameter dialog boxes. This also works, and I can
specify both Name and a date range.
Now I want to specify the date range on frmSpecifyRecord,
so I have text boxes txtStartDate and txtEndDate. The
criteria now is Between [Forms]![frmSpecifyRecord]!
[txtStartDate] And [Forms]![frmSpecifyRecord]!
[txtEndDate]. This does not work, although at one time
something very similar did work.
Once I get that to work, I need to be able to specify a)
one employee and all dates, b) all employees and a date
range, c) all employees and all dates. I can get all
employees and all dates by adding Or Like [Forms]!
[frmSpecifyRecord]![cboLastFirst] & "*" to the criteria
for Employee ID. With this I can ignore the combo box for
selecting the name, and I will see all names and all dates.
If I can get dates to work, I also want to add criteria
for several other fields such as Department, Topic, etc.
So I am trying to figure out the best way to specify
multiple criteria. If I want criteria or all records for
just one field, my choices may be described as A (one
record) and B (all records). For the same thing with a
second (date) field, my choices may also be described as A
(date range) and B (all dates). That is four
combinations, A & A, A & B, B & A, B & B. For three
fields there will be eight combinations, for four fields
sixteen, etc. Writing an expression with that many Or /
Or Like components is daunting. There must be a better
way. In fact, from what I can tell there are about a
dozen ways of doing this. Somebody told me in an earlier
post that I must not use Or, while other postings I have
found say that the solution is to use Or. Right now I am
bewildered by it all.
 
It looks to me like you should add some option boxes on to you criteria
selection form with the options All, Range, Single. and then to dynamically
create an SQL Where clause based on the criteria that is selected.

On the click of the button the dynamic SQL would be generated based upon the
options chosen on the form for eg.

I have a button called cmdfilter, two option boxes - 1 called optDateRange
with 3 options (All, Range, Single) and the other called optEmployee with 2
options (All, Single), a form that is based on the query (this does not have
any criteria set as the SQL will generate it) and my selection form

You can add as many option boxes as you like and gradually build up the
Where clause like so


Private Sub cmdFilter_Click()
Dim strWhere As String
Dim jetdatefmt As String

jetdatefmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l" ' this is so the dates if they
are in english format are translated correctly
strWhere = ""

Select Case optDateRange
Case 1
Case 2
strWhere = "[SessionDate] Between " & Format$(Me.txtStart,
jetdatefmt) & " and " & Format$(Me.txtEnd, jetdatefmt)
Case 3
strWhere = "[SessionDate] = " & Format$(Me.txtStart, jetdatefmt)
End Select

Select Case optEmployee
Case 1
strWhere = strWhere
Case 2
If strWhere = "" Then
strWhere = "[Notes] = '" & Me.txtEmployee & "'"
Else
strWhere = strWhere & " AND [Notes] = '" & Me.txtEmployee & "'"
End If
End Select

DoCmd.OpenForm "frmRecords", acNormal, , strWhere

End Sub

I hope this helps


Bruce said:
The On Open event of the report is
DoCmd.OpenForm "frmSpecifyRecord", , , , , acDialog. This
causes frmSpecifyRecord to appear. frmSpecifyRecord
contains cboLastFirst, a combo box that shows a list of
names from a query. The bound column is EmployeeID (the
PK from the Employee table); the visible column is Last,
First (a concatenated value). The report's record source
is a qryFindRecord. The criteria for the EmployeeID field
is [Forms]![frmSpecifyRecord]![cboLastFirst]. So far so
good. I open the report, frmSpecifyRecord shows up, I
select a name from cboLastFirst, click a command button
that hides frmSpecifyRecord, and that employee's training
record shows up.
Now, I want to limit the date range of the report.
Another field in the query is SessionDate. If I use
Between [Start] And [End] as the criteria, I get the
parameter dialog boxes. This also works, and I can
specify both Name and a date range.
Now I want to specify the date range on frmSpecifyRecord,
so I have text boxes txtStartDate and txtEndDate. The
criteria now is Between [Forms]![frmSpecifyRecord]!
[txtStartDate] And [Forms]![frmSpecifyRecord]!
[txtEndDate]. This does not work, although at one time
something very similar did work.
Once I get that to work, I need to be able to specify a)
one employee and all dates, b) all employees and a date
range, c) all employees and all dates. I can get all
employees and all dates by adding Or Like [Forms]!
[frmSpecifyRecord]![cboLastFirst] & "*" to the criteria
for Employee ID. With this I can ignore the combo box for
selecting the name, and I will see all names and all dates.
If I can get dates to work, I also want to add criteria
for several other fields such as Department, Topic, etc.
So I am trying to figure out the best way to specify
multiple criteria. If I want criteria or all records for
just one field, my choices may be described as A (one
record) and B (all records). For the same thing with a
second (date) field, my choices may also be described as A
(date range) and B (all dates). That is four
combinations, A & A, A & B, B & A, B & B. For three
fields there will be eight combinations, for four fields
sixteen, etc. Writing an expression with that many Or /
Or Like components is daunting. There must be a better
way. In fact, from what I can tell there are about a
dozen ways of doing this. Somebody told me in an earlier
post that I must not use Or, while other postings I have
found say that the solution is to use Or. Right now I am
bewildered by it all.
-----Original Message-----
Could you post your query / code so that we can see what it is you are
trying to do



.
Click to expand...
Click to expand...
 
Thanks for the reply. I will have to study it further, as
I am not skilled with VB code, but I will say that I would
have preferred to avoid the extra step of using option
boxes. In any case, since I cannot get the Between And
criteria to work with fields on the parameter selection
form, I need to find out what is going on there before I
spend too much time with coding. It should work the way I
have it set up, but doesn't. That could foul up a lot of
things later on.
-----Original Message-----
It looks to me like you should add some option boxes on to you criteria
selection form with the options All, Range, Single. and then to dynamically
create an SQL Where clause based on the criteria that is selected.

On the click of the button the dynamic SQL would be generated based upon the
options chosen on the form for eg.

I have a button called cmdfilter, two option boxes - 1 called optDateRange
with 3 options (All, Range, Single) and the other called optEmployee with 2
options (All, Single), a form that is based on the query (this does not have
any criteria set as the SQL will generate it) and my selection form

You can add as many option boxes as you like and gradually build up the
Where clause like so


Private Sub cmdFilter_Click()
Dim strWhere As String
Dim jetdatefmt As String

jetdatefmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l" ' this is so the dates if they
are in english format are translated correctly
strWhere = ""

Select Case optDateRange
Case 1
Case 2
strWhere = "[SessionDate] Between " & Format$(Me.txtStart,
jetdatefmt) & " and " & Format$(Me.txtEnd, jetdatefmt)
Case 3
strWhere = "[SessionDate] = " &
Click to expand...
Format$(Me.txtStart, jetdatefmt)
End Select

Select Case optEmployee
Case 1
strWhere = strWhere
Case 2
If strWhere = "" Then
strWhere = "[Notes] = '" & Me.txtEmployee & "'"
Else
strWhere = strWhere & " AND [Notes] = '" & Me.txtEmployee & "'"
End If
End Select

DoCmd.OpenForm "frmRecords", acNormal, , strWhere

End Sub

I hope this helps


The On Open event of the report is
DoCmd.OpenForm "frmSpecifyRecord", , , , , acDialog. This
causes frmSpecifyRecord to appear. frmSpecifyRecord
contains cboLastFirst, a combo box that shows a list of
names from a query. The bound column is EmployeeID (the
PK from the Employee table); the visible column is Last,
First (a concatenated value). The report's record source
is a qryFindRecord. The criteria for the EmployeeID field
is [Forms]![frmSpecifyRecord]![cboLastFirst]. So far so
good. I open the report, frmSpecifyRecord shows up, I
select a name from cboLastFirst, click a command button
that hides frmSpecifyRecord, and that employee's training
record shows up.
Now, I want to limit the date range of the report.
Another field in the query is SessionDate. If I use
Between [Start] And [End] as the criteria, I get the
parameter dialog boxes. This also works, and I can
specify both Name and a date range.
Now I want to specify the date range on frmSpecifyRecord,
so I have text boxes txtStartDate and txtEndDate. The
criteria now is Between [Forms]![frmSpecifyRecord]!
[txtStartDate] And [Forms]![frmSpecifyRecord]!
[txtEndDate]. This does not work, although at one time
something very similar did work.
Once I get that to work, I need to be able to specify a)
one employee and all dates, b) all employees and a date
range, c) all employees and all dates. I can get all
employees and all dates by adding Or Like [Forms]!
[frmSpecifyRecord]![cboLastFirst] & "*" to the criteria
for Employee ID. With this I can ignore the combo box for
selecting the name, and I will see all names and all dates.
If I can get dates to work, I also want to add criteria
for several other fields such as Department, Topic, etc.
So I am trying to figure out the best way to specify
multiple criteria. If I want criteria or all records for
just one field, my choices may be described as A (one
record) and B (all records). For the same thing with a
second (date) field, my choices may also be described as A
(date range) and B (all dates). That is four
combinations, A & A, A & B, B & A, B & B. For three
fields there will be eight combinations, for four fields
sixteen, etc. Writing an expression with that many Or /
Or Like components is daunting. There must be a better
way. In fact, from what I can tell there are about a
dozen ways of doing this. Somebody told me in an earlier
post that I must not use Or, while other postings I have
found say that the solution is to use Or. Right now I am
bewildered by it all.
-----Original Message-----
Could you post your query / code so that we can see
Click to expand...
what
it is you are
trying to do
I am having a lot of difficulty specifying multiple report
parameters. I want to specify a name, a date range, and
four to six other criteria. I also need the option of
leaving any or all of the fields blank. I have based the
report on a query, and am using a form to enter the report
criteria. The criteria, then, are the names of the
controls on the form rather than the names of the fields.
I can specify the name, but for some reason cannot specify
a date range (or any other criteria, for that
Click to expand...
matter).
A
day or two ago I could specify a date range, but for some
reason that no longer works (I have been experimenting
since then, but as far as I know have not changed that
part of the query). I thought about rebuilding the query,
but decided to seek some outside help first in case that
is a futile direction.


.
Click to expand...
Click to expand...


.
Click to expand...
 
This works (SQL behind the query) It selects all records with a session date
between the txtstart and txtend entries on the selection form (frmDates) NB
the text boxes are formatted as a date format - and where the Notes field =
what has been entered in the employee text box (txtEmployee).

SELECT tblEmployee.EmpID, tblEmployee.SessionDate, tblEmployee.Notes
FROM tblEmployee
WHERE (((tblEmployee.SessionDate) Between [forms]![frmDates]![txtStart] And
[forms]![frmDates]![txtend]) AND
((tblEmployee.Notes)=[forms]![frmDates]![txtEmployee]))

With all the options you are going to have - I think option boxes are
definately the way to go - if there were only two possible choices ie. all
or a range then you could possible add the if(isnull(blah blah) to the query
but I think you will have a lot more flexibility by going the option box
route - after all that's what they are there for ;-)

HTH

Bruce said:
Thanks for the reply. I will have to study it further, as
I am not skilled with VB code, but I will say that I would
have preferred to avoid the extra step of using option
boxes. In any case, since I cannot get the Between And
criteria to work with fields on the parameter selection
form, I need to find out what is going on there before I
spend too much time with coding. It should work the way I
have it set up, but doesn't. That could foul up a lot of
things later on.
-----Original Message-----
It looks to me like you should add some option boxes on to you criteria
selection form with the options All, Range, Single. and then to dynamically
create an SQL Where clause based on the criteria that is selected.

On the click of the button the dynamic SQL would be generated based upon the
options chosen on the form for eg.

I have a button called cmdfilter, two option boxes - 1 called optDateRange
with 3 options (All, Range, Single) and the other called optEmployee with 2
options (All, Single), a form that is based on the query (this does not have
any criteria set as the SQL will generate it) and my selection form

You can add as many option boxes as you like and gradually build up the
Where clause like so


Private Sub cmdFilter_Click()
Dim strWhere As String
Dim jetdatefmt As String

jetdatefmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l" ' this is so the dates if they
are in english format are translated correctly
strWhere = ""

Select Case optDateRange
Case 1
Case 2
strWhere = "[SessionDate] Between " & Format$(Me.txtStart,
jetdatefmt) & " and " & Format$(Me.txtEnd, jetdatefmt)
Case 3
strWhere = "[SessionDate] = " &
Click to expand...
Format$(Me.txtStart, jetdatefmt)
End Select

Select Case optEmployee
Case 1
strWhere = strWhere
Case 2
If strWhere = "" Then
strWhere = "[Notes] = '" & Me.txtEmployee & "'"
Else
strWhere = strWhere & " AND [Notes] = '" & Me.txtEmployee & "'"
End If
End Select

DoCmd.OpenForm "frmRecords", acNormal, , strWhere

End Sub

I hope this helps


The On Open event of the report is
DoCmd.OpenForm "frmSpecifyRecord", , , , , acDialog. This
causes frmSpecifyRecord to appear. frmSpecifyRecord
contains cboLastFirst, a combo box that shows a list of
names from a query. The bound column is EmployeeID (the
PK from the Employee table); the visible column is Last,
First (a concatenated value). The report's record source
is a qryFindRecord. The criteria for the EmployeeID field
is [Forms]![frmSpecifyRecord]![cboLastFirst]. So far so
good. I open the report, frmSpecifyRecord shows up, I
select a name from cboLastFirst, click a command button
that hides frmSpecifyRecord, and that employee's training
record shows up.
Now, I want to limit the date range of the report.
Another field in the query is SessionDate. If I use
Between [Start] And [End] as the criteria, I get the
parameter dialog boxes. This also works, and I can
specify both Name and a date range.
Now I want to specify the date range on frmSpecifyRecord,
so I have text boxes txtStartDate and txtEndDate. The
criteria now is Between [Forms]![frmSpecifyRecord]!
[txtStartDate] And [Forms]![frmSpecifyRecord]!
[txtEndDate]. This does not work, although at one time
something very similar did work.
Once I get that to work, I need to be able to specify a)
one employee and all dates, b) all employees and a date
range, c) all employees and all dates. I can get all
employees and all dates by adding Or Like [Forms]!
[frmSpecifyRecord]![cboLastFirst] & "*" to the criteria
for Employee ID. With this I can ignore the combo box for
selecting the name, and I will see all names and all dates.
If I can get dates to work, I also want to add criteria
for several other fields such as Department, Topic, etc.
So I am trying to figure out the best way to specify
multiple criteria. If I want criteria or all records for
just one field, my choices may be described as A (one
record) and B (all records). For the same thing with a
second (date) field, my choices may also be described as A
(date range) and B (all dates). That is four
combinations, A & A, A & B, B & A, B & B. For three
fields there will be eight combinations, for four fields
sixteen, etc. Writing an expression with that many Or /
Or Like components is daunting. There must be a better
way. In fact, from what I can tell there are about a
dozen ways of doing this. Somebody told me in an earlier
post that I must not use Or, while other postings I have
found say that the solution is to use Or. Right now I am
bewildered by it all.
-----Original Message-----
Could you post your query / code so that we can see what
it is you are
trying to do
message
I am having a lot of difficulty specifying multiple
report
parameters. I want to specify a name, a date range, and
four to six other criteria. I also need the option of
leaving any or all of the fields blank. I have based
the
report on a query, and am using a form to enter the
report
criteria. The criteria, then, are the names of the
controls on the form rather than the names of the
fields.
I can specify the name, but for some reason cannot
specify
a date range (or any other criteria, for that matter).
A
day or two ago I could specify a date range, but for
some
reason that no longer works (I have been experimenting
since then, but as far as I know have not changed that
part of the query). I thought about rebuilding the
query,
but decided to seek some outside help first in case that
is a futile direction.


.
Click to expand...


.
Click to expand...
Click to expand...
 
Thank you for taking the time to reply. I have seen
databases where the user enters multiple criteria, or none
at all, and returns either a filtered set of records or
all records. I know it can be done without an option
group, and if I can avoid unnecessary choices and
clicking, I am determined to do so. Once a workaround is
incorporated into an active database it can be very
difficult to change. I will abandon this for now, and
come back to it after I have had a chance to study the
matter some more.
-----Original Message-----
This works (SQL behind the query) It selects all records with a session date
between the txtstart and txtend entries on the selection form (frmDates) NB
the text boxes are formatted as a date format - and where the Notes field =
what has been entered in the employee text box (txtEmployee).

SELECT tblEmployee.EmpID, tblEmployee.SessionDate, tblEmployee.Notes
FROM tblEmployee
WHERE (((tblEmployee.SessionDate) Between [forms]! [frmDates]![txtStart] And
[forms]![frmDates]![txtend]) AND
((tblEmployee.Notes)=[forms]![frmDates]![txtEmployee]))

With all the options you are going to have - I think option boxes are
definately the way to go - if there were only two possible choices ie. all
or a range then you could possible add the if(isnull(blah blah) to the query
but I think you will have a lot more flexibility by going the option box
route - after all that's what they are there for ;-)

HTH

Thanks for the reply. I will have to study it further, as
I am not skilled with VB code, but I will say that I would
have preferred to avoid the extra step of using option
boxes. In any case, since I cannot get the Between And
criteria to work with fields on the parameter selection
form, I need to find out what is going on there before I
spend too much time with coding. It should work the way I
have it set up, but doesn't. That could foul up a lot of
things later on.
-----Original Message-----
It looks to me like you should add some option boxes on to you criteria
selection form with the options All, Range, Single. and then to dynamically
create an SQL Where clause based on the criteria that
Click to expand...
is
selected.
On the click of the button the dynamic SQL would be generated based upon the
options chosen on the form for eg.

I have a button called cmdfilter, two option boxes - 1 called optDateRange
with 3 options (All, Range, Single) and the other
Click to expand...
called
optEmployee with 2
options (All, Single), a form that is based on the
Click to expand...
query
(this does not have
any criteria set as the SQL will generate it) and my selection form

You can add as many option boxes as you like and gradually build up the
Where clause like so


Private Sub cmdFilter_Click()
Dim strWhere As String
Dim jetdatefmt As String

jetdatefmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l" ' this is so the dates if they
are in english format are translated correctly
strWhere = ""

Select Case optDateRange
Case 1
Case 2
strWhere = "[SessionDate] Between " & Format$(Me.txtStart,
jetdatefmt) & " and " & Format$(Me.txtEnd, jetdatefmt)
Case 3
strWhere = "[SessionDate] = " &
Click to expand...
Format$(Me.txtStart, jetdatefmt)
End Select

Select Case optEmployee
Case 1
strWhere = strWhere
Case 2
If strWhere = "" Then
strWhere = "[Notes] = '" & Me.txtEmployee & "'"
Else
strWhere = strWhere & " AND [Notes] = '" & Me.txtEmployee & "'"
End If
End Select

DoCmd.OpenForm "frmRecords", acNormal, , strWhere

End Sub

I hope this helps


The On Open event of the report is
DoCmd.OpenForm "frmSpecifyRecord", , , , , acDialog. This
causes frmSpecifyRecord to appear. frmSpecifyRecord
contains cboLastFirst, a combo box that shows a list of
names from a query. The bound column is EmployeeID (the
PK from the Employee table); the visible column is Last,
First (a concatenated value). The report's record source
is a qryFindRecord. The criteria for the EmployeeID field
is [Forms]![frmSpecifyRecord]![cboLastFirst]. So far so
good. I open the report, frmSpecifyRecord shows up, I
select a name from cboLastFirst, click a command button
that hides frmSpecifyRecord, and that employee's training
record shows up.
Now, I want to limit the date range of the report.
Another field in the query is SessionDate. If I use
Between [Start] And [End] as the criteria, I get the
parameter dialog boxes. This also works, and I can
specify both Name and a date range.
Now I want to specify the date range on frmSpecifyRecord,
so I have text boxes txtStartDate and txtEndDate. The
criteria now is Between [Forms]![frmSpecifyRecord]!
[txtStartDate] And [Forms]![frmSpecifyRecord]!
[txtEndDate]. This does not work, although at one time
something very similar did work.
Once I get that to work, I need to be able to specify a)
one employee and all dates, b) all employees and a date
range, c) all employees and all dates. I can get all
employees and all dates by adding Or Like [Forms]!
[frmSpecifyRecord]![cboLastFirst] & "*" to the criteria
for Employee ID. With this I can ignore the combo
Click to expand...
box
for
selecting the name, and I will see all names and all dates.
If I can get dates to work, I also want to add criteria
for several other fields such as Department, Topic, etc.
So I am trying to figure out the best way to specify
multiple criteria. If I want criteria or all records for
just one field, my choices may be described as A (one
record) and B (all records). For the same thing with a
second (date) field, my choices may also be described as A
(date range) and B (all dates). That is four
combinations, A & A, A & B, B & A, B & B. For three
fields there will be eight combinations, for four fields
sixteen, etc. Writing an expression with that many Or /
Or Like components is daunting. There must be a better
way. In fact, from what I can tell there are about a
dozen ways of doing this. Somebody told me in an earlier
post that I must not use Or, while other postings I have
found say that the solution is to use Or. Right now
Click to expand...
I
am
bewildered by it all.
-----Original Message-----
Could you post your query / code so that we can see what
it is you are
trying to do
message
I am having a lot of difficulty specifying multiple
report
parameters. I want to specify a name, a date
Click to expand...
range,
and
four to six other criteria. I also need the
Click to expand...
option
of
leaving any or all of the fields blank. I have based
the
report on a query, and am using a form to enter the
report
criteria. The criteria, then, are the names of the
controls on the form rather than the names of the
fields.
I can specify the name, but for some reason cannot
specify
a date range (or any other criteria, for that matter).
A
day or two ago I could specify a date range, but for
some
reason that no longer works (I have been experimenting
since then, but as far as I know have not changed that
part of the query). I thought about rebuilding the
query,
but decided to seek some outside help first in
Click to expand...
case
that
is a futile direction.


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top