Results 1 to 4 of 4

Thread: Reports (03)

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reports (03)

    I have a report that refers to a query. Within the query there is a field whose contents is either a 1 or 0. What I would like to do in the report is reflect only those records which are 0.

    I know I can create a new query and set the criteria to 0 and report on the new query but I am curious as how to do it in the original query.

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports (03)

    If you don't want to set 0 as criteria in the query, you can use VBA code to set a filter on the report when opening it from a form:
    <code>
    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, WhereCondition:="[FieldName]=0"
    </code>
    Substitute the correct names. You can replace the 0 with a reference to a control on the form:
    <code>
    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, WhereCondition:="[FieldName]=" & Me!SomeControl
    </code>
    where SomeControl is the name of a control that holds either 0 or 1.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports (03)

    Hans,

    I got it to work on the report and tried the concept on another report where a field named "Status" is either Active or Term without success.

    The code I am using is:

    Private Sub Report_Open(Cancel As Integer)
    DoCmd.OpenReport ReportName:="Membership", View:=acViewPreview, WhereCondition:="[Status]=" & "Active"
    End Sub


    I tried changing the condition to WhereCondition:="[Status]=Active" but that did not work either. Apparently the code treats numbers and text differently.

    Thanks,
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports (03)

    You have to enclose text values in quotes. Since the criteria string itself is enclosed in double quotes, you cannot simply use them. One way around this is to use single quotes:
    <code>
    ..., WhereCondition:="[Status]='Active'"
    </code>
    Another is to double the double quotes:
    <code>
    ..., WhereCondition:="[Status]=""Active"""</code>

Posting Permissions

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