Results 1 to 4 of 4
  1. #1

    Report with dynamic titles

    Hello. Just discovered the Lounge. Thought I'd try my luck here!

    Using Access 2000. Have a single table with doctors in 15 counties. I want to set it up so non-Access users can pick, at the time they choose a report, any combination of the 15 counties. Then I'd like whichever counties they select to also appear in the Title. Exam: Report for doctors in County A, County C, County X

    Not being a programmer, is there any way to set this up using relatively basic queries or forms to feed the report.

    Hope this is clear enough.


  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Youngstown, Ohio, USA
    Thanked 0 Times in 0 Posts

    Re: Report with dynamic titles

    You can have the report title be a formula that can list one or all of the counties selected. I'd use something similar to this:

    <font color=blue>"Report for Doctors in the following counties: "&[County.Select1]&Nz(", "&[County.Select2])&Nz(", "[County.Select3]).....</font color=blue>

    You will always have at least one county in the report. Since you can have anywhere up to 15 counties, expand this formula to the full 15 selections. The Nz function will return ", County#" if that county was selected, or a zero-length string if that county was not selected. You will end up with one continuous string that will only show the selected counties.

    Now, how to get the counties selected? You could use a form with a checkbox beside each county's name, to save a "Yes/No" choice to a table, and use this table as the source for your report title.

    Clear as mud? <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3

    Re: Report with dynamic titles

    Thanks. The idea for the formula looks pretty straightforward. But I'm not clear on how the check boxes on the form would work. That would be an ideal solution though, as any of the clerks could handle clicking what they wanted! But how would the boxes be able to indicate an "or" selection in the county field.

    Any clues on what I could look up in one of the Access books?

    Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Report with dynamic titles

    The formula
    <hr>Nz(", "&[County.Select2])<hr>
    doesn't do what you probably intended it to do. In that expression, Nz will never return an empty string because you haven't told it to, and it will never encounter a null because you start the expression with ", ". The brackets around County.Select2 will cause a syntax error, since you can't have a period in a field or control name.

    If you put checkboxes on a form and allow a user to select any or all of them, you still have to find a way to get the county names into the report title and, in fact, into the criteria for the report. If you based the form on a table set up specifically for this purpose as David suggested, you can use that table in a SQL select statement to provide the recordsource your report.

    If you called the table tblSelectCounties then your SQL for the report might look something like this:

    <pre>SELECT * FROM tblExams INNER JOIN tblSelectCounties
    ON tblExams.County = tblSelectCounties.County
    WHERE tblSelectCounties.Selected = True;</pre>

    You could save this as a query and make that query the recordsource for the report. Then your report would automatically return the records for the selected counties.

    Getting the county names into the title would be a little trickier. Here's a code routine you could call in the Open event of the report and use to populate your title.

    <pre>Public Function GetSelected() As String
    Dim strList As String 'holds the list of counties
    Dim rst As ADODB.Recordset 'holds the recordset

    'initialize the recordset object
    Set rst = New ADODB.Recordset

    'open and use the recordset
    With rst
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT DISTINCT [County] FROM " _
    & "tblSelectCounties WHERE [Selected]=True"
    .Open , , adOpenForwardOnly, adLockReadOnly, adCmdText
    'if there are counties selected
    'build the list
    Do While Not .EOF
    'add the current county to the list
    strList = !County
    'move to the next county selected
    'if it isn't the last county
    'add a comma to the end of the string
    If Not .EOF Then
    strList = strList & ", "
    End If 'Not .EOF
    Loop 'While Not .EOF
    End With 'rst
    'close the recordset and
    'return the value
    Set rst = Nothing
    GetSelected = strList
    End Function ' GetSelected() As String</pre>

    Your report open event procedure would look something like this:

    <pre>Private Sub Report_Open(Cancel As Integer)
    Me![txtTitle] = GetSelected()
    End Sub</pre>


Posting Permissions

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