Lookup Problem

[Tim Leach]

I have the following problem;

I have two tables defined as follows:
Table1 has a field called Zip, this field is a lookup field to another
table called ZIP_CODES. In this table there are several fields, the ones I
am concerned with are, ZIP, CITY, STATE, COUNTY.

On the form to enter data, when the user puts in the ZIP of the person, the
CITY, STATE, and COUNTY are filled in by looking up the ZIP in the
ZIP_CODES table and displayed on the form. This part works fine. The
problem comes when I want to put the information on a report.

I use a query to ask the user for a record to print. As long as I have only
the fields from Table1 in the query it works fine, but if I add the fields
from ZIP_CODES that I want printed, the reports print out blank. When I
remove the ZIP_CODES fields the record prints out, but I don't have the
CITY, STATE and COUNTY on it.

I have tried using unbound boxes with a DLookup function to display what I
want on the report but I can't make it work, but this is what I had:
Dlookup(City,[ZIP_CODES],[Table1].[ZIP]= [ZIP_CODES].[ZIP]

I am not sure that is the correct way to use it.

Is there any other way to make the data from the ZIP_CODES table appear on
the report with the data from Table1?

Please help?

Tim Leach

 

[Guest]

I suppose you have established a relationship between the two tables.
put them both in the query and place the fields you want from table1 except
'zip' field. place 'zip text' (not the ID column) on the fields.

This should do it.
Good luck

 

[Tim Leach]

Jim,

There is a relationship Table1.zip ---> ZIP_CODES.ZIP

I am not quite sure what you mean by: place 'zip text' (not the ID column)
on the fields

I am new to using access and am probably over my head.
Do you mean, don't include Table1.zip in the query but put ZIP_CODES.ZIP
and ZIP_CODES.CITY etc.. in the query?

Please forgive me if I sound ignorant, but I am just a cop who used to be a
programmer with mainframes.

Thanks Again for your help

Tim Leach

I suppose you have established a relationship between the two tables.
put them both in the query and place the fields you want from table1 except
'zip' field. place 'zip text' (not the ID column) on the fields.

This should do it.
Good luck

I have the following problem;

I have two tables defined as follows:
Table1 has a field called Zip, this field is a lookup field to another
table called ZIP_CODES. In this table there are several fields, the ones I
am concerned with are, ZIP, CITY, STATE, COUNTY.

On the form to enter data, when the user puts in the ZIP of the person, the
CITY, STATE, and COUNTY are filled in by looking up the ZIP in the
ZIP_CODES table and displayed on the form. This part works fine. The
problem comes when I want to put the information on a report.

I use a query to ask the user for a record to print. As long as I have only
the fields from Table1 in the query it works fine, but if I add the fields
from ZIP_CODES that I want printed, the reports print out blank. When I
remove the ZIP_CODES fields the record prints out, but I don't have the
CITY, STATE and COUNTY on it.

I have tried using unbound boxes with a DLookup function to display what I
want on the report but I can't make it work, but this is what I had:
Dlookup(City,[ZIP_CODES],[Table1].[ZIP]= [ZIP_CODES].[ZIP]

I am not sure that is the correct way to use it.

Is there any other way to make the data from the ZIP_CODES table appear on
the report with the data from Table1?

Please help?

Tim Leach