Re: Report parameters

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Newbie (noidea_at_nospam.com)
Date: 04/01/04 

Date: Thu, 1 Apr 2004 16:08:54 +0100

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" <anonymous@discussions.microsoft.com> wrote in message
news:16c3b01c417f0$47e44a70$a501280a@phx.gbl...
> 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
> >"Bruce" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:16ce001c41762$5b83e1a0$a101280a@phx.gbl...
> >> 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.
> >
> >
> >.
> >

Quantcast