report returns Field ID instead of value

G

Guest

This has probably been answered before

I have a query that when i run, it returns exactly as i need if not linked
to anything (input form or report). The query is a parameter query that is
linked to a form (the form is a series of combo boxes). i have also
constructed a report for it to populate.

the problem i have is that one of my fields returns the Field ID in the
report and dispite my endevours to trace the offending link, I fail to find
it.

Any suggestions would be much appreciated. (I can post any SQL if needed)
 
S

Steve

You probably have something like this:

TblCustomer
CustomerID
Customername
etc

TblOrder
OrderID
OrderDate
CustomerID
etc

On your form where you enter orders you enter the customer with a combobox.
The combobox uses TblCustomer as the row source. The combobox has Bound
Column set to 1 and Column Width set to 0;2. The combobox displays the
customer name but when you make your selection, CustomerID is recorded in
TblOrder. CustomerID is a number. If you look at TblOrder, you will see all
numbers in the CustomerID field. If you create a query based only on
TblOrder for the recordsource of a report, and expect to see the customer
name in the CustomerID field, all you are going to see are numbers where you
expect to see customer names. The fix is simple. Include TblCustomer in your
query and join CustomerID to CustomerID. Include CustomerName from
TblCustomer in your query then include CustomerName in the fields for your
report. Now you will see the customer name where you expect to see the
customer name.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
G

Guest

Jason,

It is the client ID that returns the value in the report (ok in the
datasheet view)

Thanks Gerry


SELECT [tbl_PROJECTS].[Project ID], [tbl_PROJECTS].[Project No (RPS)],
[tbl_PROJECTS].[Project Name], [tbl_PROJECTS].[Start Date],
[tbl_PROJECTS].[End Date], [tbl_PROJECTS].[Client ID], [tbl_PROJECTS].[Road
Type ID], [tbl_PROJECTS].[Rural Urban ID], [tbl_PROJECTS].[Location ID],
[tbl_PROJECTS].[Contract Type ID], [tbl_PROJECTS].[Const Cost],
[tbl_PROJECTS].[Project Description], [tbl_Services Provided].[RPS Services]
FROM [tbl_Services Available] INNER JOIN (tbl_PROJECTS INNER JOIN
[tbl_Services Provided] ON [tbl_PROJECTS].[Project ID]=[tbl_Services
Provided].[Project ID]) ON [tbl_Services Available].[Services
ID]=[tbl_Services Provided].[RPS Services]
WHERE ([tbl_PROJECTS].[Rural Urban ID]=[Forms]![frm_gerry]![Combo36] Or
[Forms]![frm_gerry]![Combo36] Is Null) And ([tbl_PROJECTS].[Location
ID]=[Forms]![frm_gerry]![Combo38] Or [Forms]![frm_gerry]![Combo38] Is Null)
And ([tbl_PROJECTS].[Road Type ID]=[Forms]![frm_gerry]![Combo40] Or
[Forms]![frm_gerry]![Combo40] Is Null) And ([tbl_PROJECTS].[Contract Type
ID]=[Forms]![frm_gerry]![Combo42] Or [Forms]![frm_gerry]![Combo42] Is Null);
 
G

Guest

Thanks Steve, I see where you are talking about. But this is the situation I
have

tbl_Projects
Proj no
Proj Name
Client
Proj Descrip
etc

tbl_Client
Name
Address
Contact
etc

tbl client is already linked to tbl project via a combo box.

when I run the query normally, the datasheet returns the correct name so I
feel that the link is correct. it is only when i generate a report from it
that the report returns the ID value aghhhhhh

Gerry
 
J

Jason Lepack

Yep, Lookup Fields are bad for many reasons. Check on your report
that the item is a combo box and not a text box. If it's not, put the
field on again.
 
S

Steve

In tbl_Client add a field named ClientID and make it Autonumber. In
tbl_Projects, change Client to ClientID and be sure to enter the correct
ClientID from tbl_Client for each record. Base your report om a query that
includes both tables and be sure to include Name from tbl_Client. Put the
Name field on your report and your problem will vanish.

By the way, Name is a reserved word in Access and should not be used as a
field name. Change the name of Name.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
G

Guest

Thanks Steve (and Jason)

Sorry for not answering sooner, caught up with work. I managed to resove it.
The problem was, i was using an intersect table and had referenced in the
query instead of the parent table.

Thanks,

Gerry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top