Report query using variable from report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When generating a report, the report returns a field, 'Segment1' that I would
also like to use on the same report in a dynamic database query using that
value as part of the lookup statement. The query that will return the proper
value outside of the report is:

SELECT UPC.UpcCode
FROM UPC
WHERE (((UPC.Segment)=(Segment1)));

'Segment1' is current being displayed on the report in another field, and I
need to use that value within the sql statement to select a UPC code that
corresponds to this record.

Unfortunately, I'm not as familiar with the syntax needed to use this within
a field in Access2003, and all my attempts have just produced errors, or
prompt boxes.

Thank you for any help!

AH
 
AHeber said:
When generating a report, the report returns a field, 'Segment1' that I would
also like to use on the same report in a dynamic database query using that
value as part of the lookup statement. The query that will return the proper
value outside of the report is:

SELECT UPC.UpcCode
FROM UPC
WHERE (((UPC.Segment)=(Segment1)));

'Segment1' is current being displayed on the report in another field, and I
need to use that value within the sql statement to select a UPC code that
corresponds to this record.

Unfortunately, I'm not as familiar with the syntax needed to use this within
a field in Access2003, and all my attempts have just produced errors, or
prompt boxes.
Click to expand...


Generally, this should be done in the report's record source
query by joining the UPC table on the corresponding field in
whatever other table the report is based on.

If that is impractical, then a text box in the report can
use and expression to get the code.
=DLookup("UpcCode", "UPC", "Segment=" & Segment1)
 
Placing the suggested code in the Control Source for the Text Box:

=DLookup("UpcCode", "UPC", "Segment=" & Segment1)

results in the '#Error' when the report is generated. Is there a method
(debug) of getting more detail as to the cause of this error?
 
After looking at a few other similar questions, I included single quotes
around the 'Segment1' and the report works as desired.

Thank you for your help!
 
I should have guessed that the segment field was a text
field and added the quotes. Sorry about that.

As I said before, that's not the best way, but if you're
happy ...
 
Back
Top