Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    9

    Unanswered: User-requested Reports

    Hi. I finally finished my dB and now am trying to figure out how to create user-defined reports.

    I have a form with various unbound controls that the user may set to a certain search criteria (i.e. sex: Male, age: between 18-24, etc). Next to each of these controls, I have put a corresponding checkbox. All I want to do is create a Select Query that will only use the criteria in the query if the corresponding checkbox is in the TRUE state; otherwise, there is no restriction. The report is then based on this Query.

    Say I had checkboxes named CheckAge and CheckSex as well as corresponding Age and Sex unbound controls. I had envisioned something like:

    If Me.CheckAge then
    'Somehow include Age criteria
    End If

    If Me.CheckSex then
    'Include sex criteria
    End If

    Can this be done just in the Query Design View, or do I need to use the VB editor? I had thought of the possibility of creating an SQL statement (where each "If" concatenates the appropriate code if the condition is true) but I'm not sure how I would make such a statement the source for the report. You dig?

    Anyway, thanks for any help you can give.

    Chris

  2. #2
    Join Date
    Nov 2002
    Posts
    150

    Re: User-requested Reports

    Try something like this..

    Private Sub OpenTheReport_Click()
    Dim sCriteria As String

    sCriteria = ""
    If chkAge Then
    sCriteria = sCriteria & "[Age] = " & Me.txtAge
    End If

    If chkSex Then
    sCriteria = sCriteria & " AND [Sex] = " & Me.txtSex
    End If

    DoCmd.OpenReport "ReportName", acViewPreview, , sCriteria

    End Sub

  3. #3
    Join Date
    Oct 2002
    Posts
    9

    Talking User-requested Reports SOLVED

    Thanks for the tip, I've got everything working now. Just for future reference, here's exactly how I did it:

    'On clicking the button that opens the report...
    Private Sub OK_Click()

    'Parts of the WHERE expression are concatenated to
    'form sCriteria.
    Dim sCriteria As String
    'Dictates whether sCriteria is still empty (important
    'because every statement after the first needs AND
    'in it).
    Dim bEmpty As Boolean

    sCriteria = ""
    bEmpty = True

    If Me.EventTime Then
    sCriteria = sCriteria & "[Date] >= #" & Me.DateLow & "# AND [Date] <= #" & Me.DateHigh & "#"
    bEmpty = False
    End If

    If Me.EventCity Then
    'If something has already been put into sCriteria,
    'then " AND " is tacked on so that sCriteria is
    'ready to accept the next condition.
    If Not bEmpty Then
    sCriteria = sCriteria & " AND "
    End If

    'Don't forget to put ' marks around strings.
    sCriteria = sCriteria & "[Location] = '" & Me.City & "'"
    bEmpty = False
    End If

    'And so on and so on for each control.

    'If sCriteria has anything in it, then open the
    'report. Otherwise, no criteria was selected and
    'the user is told to select criteria.
    If Not bEmpty Then
    DoCmd.OpenReport "HRV Report", acViewPreview, , , , sCriteria
    Else
    MsgBox "Please select criteria", vbOKOnly, "Error"
    End If

    End Sub

    'Then the sCriteria is used as a filter.
    Private Sub Report_Open(Cancel As Integer)

    Dim sCriteria As String
    sCriteria = Me.OpenArgs

    'Filters the report's data as requested by the user.
    DoCmd.ApplyFilter , sCriteria

    End Sub

    I know, it may not be rocket science or even done all that well, but it's a small personal victory anyway ;)

    L8r

    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •