Query works but report has no data

  • Thread starter Thread starter NewSysAdmin
  • Start date Start date
N

NewSysAdmin

Hello,
I have a problem with my report that is driving me crazy! I am using a form
to pass values to the query and then report. The form has start and end date
fields and combo boxes for fields 'QC Project Leader, CustomerName, and
Model' which are linked to separate tables. I want to enable the user to be
able to either run it for specific QC Project Leader/Customer/Model or for
all records.

The query works fine when typing in the QC Project Leader/Customer/Model.
When I try to run the report, the only field that works is Customer. If the
user enters a specific QC Project Leader or Model, it gives error for no
data. What could be causing this problem? Thank you for any assistance you
can provide.

Here is part of my SQL code from the query:

WHERE ((([RMA/TR Tracking table].[Date of Call]) Between
[Forms]![RMARespTmFm]![StartDate] And [Forms]![RMARespTmFm]![EndDate]) AND
(([RMA/TR Tracking table].[QC Project Leader]) Like
[Forms]![RMARespTmFm]![QCPL] & "*") AND (([RMA/TR Tracking
table].Status)="Closed" Or ([RMA/TR Tracking table].Status)="Submitted") AND
(([RMA/TR Tracking table].[Complaint Category])="RMA") AND (([RMA/TR Tracking
table].CustomerName) Like [Forms]![RMARespTmFm]![Customer] & "*") AND
(([RMA/TR Tracking table].Model) Like [Forms]![RMARespTmFm]![Model] & "*"));
 
Do you use Lookup fields defined in your tables? What do you see if you open
the debug window (press Ctrl+G) and type in:

?[Forms]![RMARespTmFm]![Model]

?[Forms]![RMARespTmFm]![QCPL]

Do you see values that should match values in your table?
 
Thank you for your help. Yes, I am using Lookup fields. When I typed that,
it was saying Switchboard could not find the field...

So I decided to look more at the lookup fields in the form. In my table,
they were working fine. The form appeared to be working fine, too. But the
difference was that the Select statement on form included the RecordId as
well, then I had set the columns to 2, column width to "0";"1". When I took
the RecordID out of the select statement and changed columns to 1, width to
"1", it worked perfectly.

Thank you for pointing me in the right direction! Once again, this forum
has saved me many headaches and hours of work. I'm so grateful for this
invaluable resource!

Duane Hookom said:
Do you use Lookup fields defined in your tables? What do you see if you open
the debug window (press Ctrl+G) and type in:

?[Forms]![RMARespTmFm]![Model]

?[Forms]![RMARespTmFm]![QCPL]

Do you see values that should match values in your table?
--
Duane Hookom
Microsoft Access MVP


NewSysAdmin said:
Hello,
I have a problem with my report that is driving me crazy! I am using a form
to pass values to the query and then report. The form has start and end date
fields and combo boxes for fields 'QC Project Leader, CustomerName, and
Model' which are linked to separate tables. I want to enable the user to be
able to either run it for specific QC Project Leader/Customer/Model or for
all records.

The query works fine when typing in the QC Project Leader/Customer/Model.
When I try to run the report, the only field that works is Customer. If the
user enters a specific QC Project Leader or Model, it gives error for no
data. What could be causing this problem? Thank you for any assistance you
can provide.

Here is part of my SQL code from the query:

WHERE ((([RMA/TR Tracking table].[Date of Call]) Between
[Forms]![RMARespTmFm]![StartDate] And [Forms]![RMARespTmFm]![EndDate]) AND
(([RMA/TR Tracking table].[QC Project Leader]) Like
[Forms]![RMARespTmFm]![QCPL] & "*") AND (([RMA/TR Tracking
table].Status)="Closed" Or ([RMA/TR Tracking table].Status)="Submitted") AND
(([RMA/TR Tracking table].[Complaint Category])="RMA") AND (([RMA/TR Tracking
table].CustomerName) Like [Forms]![RMARespTmFm]![Customer] & "*") AND
(([RMA/TR Tracking table].Model) Like [Forms]![RMARespTmFm]![Model] & "*"));
Click to expand...
Click to expand...
 
Back
Top