Report based on Query

  • Thread starter Thread starter BigK9
  • Start date Start date
B

BigK9

I have a report based on a query that uses 2 tables. Table 1 has 2 fields:
an autonumber field that is the primary key and "field1" that is a list of
accounts. Table 2 has a field that is based on the "field1" of Table 1 as a
lookup field. The entire query uses Table 2 and another table that has
specific information for the report. Here is the problem. When I run the
query, it works perfectly and returns a text value from Table1;Field1. When
I run the report, it returns a numeric value for the Table1;Field1 box. I
need the text value.
 
That's because you are using a Lookup in your table. This was transferred to
your query but not to your report.

The correct way to use lookups is in a combo box in a form.

But to rectify the problem.
Open your query in Design View

As a punishment for using a table Lookup Access gives you long, ugly
fieldnames that take you ages to type.

Find that field name - it may not be easy because it will probably have a
Caption to make it look cute in your query but its real name may be
something catchy like lookup_.to_TableWhatEver

You may have to click on the fields, click on the Properties button and
delete the Caption to find out the real name.


Delete the Lookup by clicking on the LookupTab in Properties and replacing
Combo Box with Text Box

Your nice text will be replaced, probably by an ugly number.

Press the Add Tables button.Add the Lookup table to the query grid. Add the
text field from the Lookup table to the query.

Open your report in Design View

click the Field List button

Drag the text field onto the report

Promise yourself that as soon as you have some hours to spare that you will
take control of your database and remove all your lookups and create nice
forms with combos to do the work.

Evi
 
Back
Top