Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Double use of same field in report (2000 SR-1)

    I'm trying to create a report that uses the same value twice--once by itself and once as part of a larger list. The query seems to work OK with manually entered test criteria values. The single usage of the value comes from a table, and the list usage of the value comes from another query.

    I'm trying to construct some VBA code that sorts on the single usage of the value:

    'Get single usage of value
    PrimaryCR = Forms![CRSelect]!CorrCR.Value

    'Construct report filter that grabs list usage of value using single usage of value
    RptFilter = "[Correction CRs Query].CorrCR='" & PrimaryCR & "'"

    'Get report
    DoCmd.OpenReport "AdHocCorrectionsListing -- New", acViewPreview, , RptFilter

    When I run this code I get VB run-time error 3079 and the following message:

    <<The specified field 'CorrectionCRs.CorrCR' could refer to more than one table listed in the FROM clause of your SQL statement>>

    I looked in the SQL code but that didn't help any. I'm wondering if this is a problem with my VB syntax or a problem with the usage of the same value twice in a report.

    Thanks,
    Bob

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

    Re: Double use of same field in report (2000 SR-1)

    Can you post the SQL of the query that acts as Record Source of the report? The error message suggests that the problem has to do with that.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Double use of same field in report (2000 SR-1)

    SELECT [CorrectionCRs].[CorrCR], [CorrectionCRs].[CorrID], [Corrections].[CorrDesc], [Screens].[ScreenID], [Correction CRs Query].[CorrCR]
    FROM ((Corrections INNER JOIN [Correction CRs Query] ON [Corrections].[CorrID]=[Correction CRs Query].[CorrID]) LEFT JOIN CorrectionCRs ON [Corrections].[CorrID]=[CorrectionCRs].[CorrID]) LEFT JOIN Screens ON [Corrections].[CorrID]=[Screens].[CorrID]
    WHERE ((([Correction CRs Query].[CorrCR])="CR035498"));

    *********************

    I'm not sure why there's a value at the end (CR035498). Also, I noticed that the Filter value in the design view of the report has a value.

    Thanks,
    Bob

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

    Re: Double use of same field in report (2000 SR-1)

    1. The value at the end of the SQL means that the query returns only those records for which [Correction CRs Query].[CorrCR] is equal to "CR035498". If that is not what you want, remove the part beginning with WHERE.

    2. You can clear the Filter property of the report.

    3. Since the CorrCR field is returned twice in the query (once from CorrectionCRs and once from Correction CRs Query, it might be a good idea to give one of them an alias. This makes it easier to distinguish them. For example, add AS QCorrCR after [Correction CRs Query].[CorrCR]:

    SELECT [CorrectionCRs].[CorrCR], [CorrectionCRs].[CorrID], [Corrections].[CorrDesc], [Screens].[ScreenID], [Correction CRs Query].[CorrCR] As QCorrCR
    FROM ...

    You will have to change the control source of the control bound to this field, and also the filter string:

    RptFilter = "QCorrCR='" & PrimaryCR & "'"

    HTH

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Double use of same field in report (2000 SR-1)

    This helped. Thanks.

Posting Permissions

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