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

Topic

New Reply

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:

<>

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

This helped. Thanks.

Reply | Quote

This helped. Thanks.

Reply | Quote

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

Reply | Quote

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

Reply | Quote