report returns Field ID instead of value

  • Thread starter Thread starter Guest
  • Start date Start date
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)
 
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)
 
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);
 
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
 
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.
 
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)
 
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
 
Back
Top