Report returning table ID, not actual table data

[Dan231]

I have a table which has First Name and Last Name as fields. I have another
table used for problems that references this into 1 field called FullName.
The properties for this is a combobox and the row source is: SELECT
[EmployeeID], [LastName] & ", " & [FirstName] FROM Employees ORDER BY
[LastName] & ", " & [FirstName];

Now I am trying to create a report from the Problems table that will only
show open problems. I did this by creating a query. The query is correct .
My problem is with the actual report. Instead of listing the Name field, it
is returning the table ID#. I have no idea what the problem is.

This report was working fine until I changed the FullName field from just
last name to First and Last name. Any suggestions?

 

[Duane Hookom]

Your report's record source should contain the "table which has First Name
and Last Name as fields". You should be able to then display full name.

 

[Dan231]

I was using the query as the record source as I don't know how else to get
just the open items directly from the table.

Your report's record source should contain the "table which has First Name
and Last Name as fields". You should be able to then display full name.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I have a table which has First Name and Last Name as fields. I have another
table used for problems that references this into 1 field called FullName.
The properties for this is a combobox and the row source is: SELECT
[EmployeeID], [LastName] & ", " & [FirstName] FROM Employees ORDER BY
[LastName] & ", " & [FirstName];

Now I am trying to create a report from the Problems table that will only
show open problems. I did this by creating a query. The query is correct .
My problem is with the actual report. Instead of listing the Name field, it
is returning the table ID#. I have no idea what the problem is.

This report was working fine until I changed the FullName field from just
last name to First and Last name. Any suggestions?

 

[Duane Hookom]

You should be able to use the same query, however add the names table.
If you can't figure this out, come back with the query, its SQL view, the
name of your names table as well as its significant field, and the fields
that join the tables.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I was using the query as the record source as I don't know how else to get
just the open items directly from the table.

Your report's record source should contain the "table which has First Name
and Last Name as fields". You should be able to then display full name.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I have a table which has First Name and Last Name as fields. I have another
table used for problems that references this into 1 field called FullName.
The properties for this is a combobox and the row source is: SELECT
[EmployeeID], [LastName] & ", " & [FirstName] FROM Employees ORDER BY
[LastName] & ", " & [FirstName];

Now I am trying to create a report from the Problems table that will only
show open problems. I did this by creating a query. The query is correct .
My problem is with the actual report. Instead of listing the Name field, it
is returning the table ID#. I have no idea what the problem is.

This report was working fine until I changed the FullName field from just
last name to First and Last name. Any suggestions?

 

[Dan231]

Sorry, but I can't figure out what to do.

Here is the SQL view of the query, which does show the name field as: Last,
First:
SELECT Problems.User, Problems.Problem, Problems.Fixed, Problems.Date,
Problems.Comments
FROM Problems
WHERE (((Problems.Fixed)="No"))
ORDER BY Problems.Date;

The table with the employee data is: Employees
The fields here are: First Name and Last Name

Again, I'm not sure what info you need for fields that join the tables, but
in my Problems table (the one which I am trying to report on) does not have a
relationship link at all.

The Problem tables has a field named: User and the Lookup properties are set
to: Combo Box, Table/Query; source: SELECT [EmployeeID], [LastName] & ", " &
[FirstName] FROM Employees ORDER BY [LastName] & ", " & [FirstName];

This does return the Last, First from the the Employees table.

 

[Duane Hookom]

You stated "The table with the employee data is: Employees
The fields here are: First Name and Last Name" when clearly there must be an
EmployeeID field also.

Add the Employees table to your query and join the Problems.User field to
the EmployeeID field. Then add the names to the grid so they appear in your
report.

I expect you use lookup fields in your table designs. Most of us old
experienced programmers frown on this based on confused users
http://www.mvps.org/access/lookupfields.htm.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Sorry, but I can't figure out what to do.

Here is the SQL view of the query, which does show the name field as: Last,
First:
SELECT Problems.User, Problems.Problem, Problems.Fixed, Problems.Date,
Problems.Comments
FROM Problems
WHERE (((Problems.Fixed)="No"))
ORDER BY Problems.Date;

The table with the employee data is: Employees
The fields here are: First Name and Last Name

Again, I'm not sure what info you need for fields that join the tables, but
in my Problems table (the one which I am trying to report on) does not have a
relationship link at all.

The Problem tables has a field named: User and the Lookup properties are set
to: Combo Box, Table/Query; source: SELECT [EmployeeID], [LastName] & ", " &
[FirstName] FROM Employees ORDER BY [LastName] & ", " & [FirstName];

This does return the Last, First from the the Employees table.

You should be able to use the same query, however add the names table.
If you can't figure this out, come back with the query, its SQL view, the
name of your names table as well as its significant field, and the fields
that join the tables.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

 

[Dan231]

Sorry, yes, there are quite a few more fields in the Employees table. I
thought you just wanted the name fields. The EmployeeID field is actually
the auto number field.

I don't think I have any lookup fields.

I made the sugegsted changes and I get a type mismatch error.
Here is my SQL view:
SELECT Problems.Date, Problems.User, Problems.Problem, Problems.Comments,
Problems.Solution, Employees.FirstName, Employees.LastName
FROM Employees INNER JOIN Problems ON Employees.EmployeeID = Problems.User
WHERE (((Problems.Solution)="No"));

You stated "The table with the employee data is: Employees
The fields here are: First Name and Last Name" when clearly there must be an
EmployeeID field also.

Add the Employees table to your query and join the Problems.User field to
the EmployeeID field. Then add the names to the grid so they appear in your
report.

I expect you use lookup fields in your table designs. Most of us old
experienced programmers frown on this based on confused users
http://www.mvps.org/access/lookupfields.htm.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Sorry, but I can't figure out what to do.

Here is the SQL view of the query, which does show the name field as: Last,
First:
SELECT Problems.User, Problems.Problem, Problems.Fixed, Problems.Date,
Problems.Comments
FROM Problems
WHERE (((Problems.Fixed)="No"))
ORDER BY Problems.Date;

The table with the employee data is: Employees
The fields here are: First Name and Last Name

Again, I'm not sure what info you need for fields that join the tables, but
in my Problems table (the one which I am trying to report on) does not have a
relationship link at all.

The Problem tables has a field named: User and the Lookup properties are set
to: Combo Box, Table/Query; source: SELECT [EmployeeID], [LastName] & ", " &
[FirstName] FROM Employees ORDER BY [LastName] & ", " & [FirstName];

This does return the Last, First from the the Employees table.

You should be able to use the same query, however add the names table.
If you can't figure this out, come back with the query, its SQL view, the
name of your names table as well as its significant field, and the fields
that join the tables.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

 

[Duane Hookom]

Your "user" field in Problems should be a numeric long field and contain the
value from the EmployeeID field. Do you have another field in the Problems
table that might store the EmployeeID value?

When you go to the design view of the Problems table, what is the data type
of User? Does it have a Display Control property in the Lookup tab of Combo
Box?
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Sorry, yes, there are quite a few more fields in the Employees table. I
thought you just wanted the name fields. The EmployeeID field is actually
the auto number field.

I don't think I have any lookup fields.

I made the sugegsted changes and I get a type mismatch error.
Here is my SQL view:
SELECT Problems.Date, Problems.User, Problems.Problem, Problems.Comments,
Problems.Solution, Employees.FirstName, Employees.LastName
FROM Employees INNER JOIN Problems ON Employees.EmployeeID = Problems.User
WHERE (((Problems.Solution)="No"));

You stated "The table with the employee data is: Employees
The fields here are: First Name and Last Name" when clearly there must be an
EmployeeID field also.

Add the Employees table to your query and join the Problems.User field to
the EmployeeID field. Then add the names to the grid so they appear in your
report.

I expect you use lookup fields in your table designs. Most of us old
experienced programmers frown on this based on confused users
http://www.mvps.org/access/lookupfields.htm.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Sorry, but I can't figure out what to do.

Here is the SQL view of the query, which does show the name field as: Last,
First:
SELECT Problems.User, Problems.Problem, Problems.Fixed, Problems.Date,
Problems.Comments
FROM Problems
WHERE (((Problems.Fixed)="No"))
ORDER BY Problems.Date;

The table with the employee data is: Employees
The fields here are: First Name and Last Name

Again, I'm not sure what info you need for fields that join the tables, but
in my Problems table (the one which I am trying to report on) does not have a
relationship link at all.

The Problem tables has a field named: User and the Lookup properties are set
to: Combo Box, Table/Query; source: SELECT [EmployeeID], [LastName] & ", " &
[FirstName] FROM Employees ORDER BY [LastName] & ", " & [FirstName];

This does return the Last, First from the the Employees table.

:

You should be able to use the same query, however add the names table.
If you can't figure this out, come back with the query, its SQL view, the
name of your names table as well as its significant field, and the fields
that join the tables.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

 

[Dan231]

Yes, it is set as text. The combobox has a source of:
SELECT [EmployeeID], [LastName] & ", " & [FirstName] FROM Employees ORDER BY
[LastName] & ", " & [FirstName];

Which gives me a full name display to enter the data when it comes in. If I
had to memorize numbers for each user, that would be too difficult for me to
even want to keep this log.

Your "user" field in Problems should be a numeric long field and contain the
value from the EmployeeID field. Do you have another field in the Problems
table that might store the EmployeeID value?

When you go to the design view of the Problems table, what is the data type
of User? Does it have a Display Control property in the Lookup tab of Combo
Box?
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Sorry, yes, there are quite a few more fields in the Employees table. I
thought you just wanted the name fields. The EmployeeID field is actually
the auto number field.

I don't think I have any lookup fields.

I made the sugegsted changes and I get a type mismatch error.
Here is my SQL view:
SELECT Problems.Date, Problems.User, Problems.Problem, Problems.Comments,
Problems.Solution, Employees.FirstName, Employees.LastName
FROM Employees INNER JOIN Problems ON Employees.EmployeeID = Problems.User
WHERE (((Problems.Solution)="No"));

You stated "The table with the employee data is: Employees
The fields here are: First Name and Last Name" when clearly there must be an
EmployeeID field also.

Add the Employees table to your query and join the Problems.User field to
the EmployeeID field. Then add the names to the grid so they appear in your
report.

I expect you use lookup fields in your table designs. Most of us old
experienced programmers frown on this based on confused users
http://www.mvps.org/access/lookupfields.htm.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Sorry, but I can't figure out what to do.

Here is the SQL view of the query, which does show the name field as: Last,
First:
SELECT Problems.User, Problems.Problem, Problems.Fixed, Problems.Date,
Problems.Comments
FROM Problems
WHERE (((Problems.Fixed)="No"))
ORDER BY Problems.Date;

The table with the employee data is: Employees
The fields here are: First Name and Last Name

Again, I'm not sure what info you need for fields that join the tables, but
in my Problems table (the one which I am trying to report on) does not have a
relationship link at all.

The Problem tables has a field named: User and the Lookup properties are set
to: Combo Box, Table/Query; source: SELECT [EmployeeID], [LastName] & ", " &
[FirstName] FROM Employees ORDER BY [LastName] & ", " & [FirstName];

This does return the Last, First from the the Employees table.

:

You should be able to use the same query, however add the names table.
If you can't figure this out, come back with the query, its SQL view, the
name of your names table as well as its significant field, and the fields
that join the tables.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

 

[Duane Hookom]

You combo box has several important properties that we can't see
Column Widths:
Column Count:
Bound Column:
Control Source:

Does your table design use a combo box in the Lookup tab?

I use autonumber primary keys in all my tables. Users never a clue what
these values are. I never use lookups fields defined in tables.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Yes, it is set as text. The combobox has a source of:
SELECT [EmployeeID], [LastName] & ", " & [FirstName] FROM Employees ORDER BY
[LastName] & ", " & [FirstName];

Which gives me a full name display to enter the data when it comes in. If I
had to memorize numbers for each user, that would be too difficult for me to
even want to keep this log.

Your "user" field in Problems should be a numeric long field and contain the
value from the EmployeeID field. Do you have another field in the Problems
table that might store the EmployeeID value?

When you go to the design view of the Problems table, what is the data type
of User? Does it have a Display Control property in the Lookup tab of Combo
Box?
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Sorry, yes, there are quite a few more fields in the Employees table. I
thought you just wanted the name fields. The EmployeeID field is actually
the auto number field.

I don't think I have any lookup fields.

I made the sugegsted changes and I get a type mismatch error.
Here is my SQL view:
SELECT Problems.Date, Problems.User, Problems.Problem, Problems.Comments,
Problems.Solution, Employees.FirstName, Employees.LastName
FROM Employees INNER JOIN Problems ON Employees.EmployeeID = Problems.User
WHERE (((Problems.Solution)="No"));


:

You stated "The table with the employee data is: Employees
The fields here are: First Name and Last Name" when clearly there must be an
EmployeeID field also.

Add the Employees table to your query and join the Problems.User field to
the EmployeeID field. Then add the names to the grid so they appear in your
report.

I expect you use lookup fields in your table designs. Most of us old
experienced programmers frown on this based on confused users
http://www.mvps.org/access/lookupfields.htm.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Sorry, but I can't figure out what to do.

Here is the SQL view of the query, which does show the name field as: Last,
First:
SELECT Problems.User, Problems.Problem, Problems.Fixed, Problems.Date,
Problems.Comments
FROM Problems
WHERE (((Problems.Fixed)="No"))
ORDER BY Problems.Date;

The table with the employee data is: Employees
The fields here are: First Name and Last Name

Again, I'm not sure what info you need for fields that join the tables, but
in my Problems table (the one which I am trying to report on) does not have a
relationship link at all.

The Problem tables has a field named: User and the Lookup properties are set
to: Combo Box, Table/Query; source: SELECT [EmployeeID], [LastName] & ", " &
[FirstName] FROM Employees ORDER BY [LastName] & ", " & [FirstName];

This does return the Last, First from the the Employees table.

:

You should be able to use the same query, however add the names table.
If you can't figure this out, come back with the query, its SQL view, the
name of your names table as well as its significant field, and the fields
that join the tables.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

 

[Dan231]

yes it uses the combo box.
all the settings are listed as:
Combo Box
Table/Query
Select stmt previously posted
1
2
No
0";2"
8
2"
Yes

 

[Duane Hookom]

Apparently you have a lookup field defined in the table/field design. You
main issue seems to be that your field is a text field and you are storing
the EmployeeID from the employee table.

I would make a copy of the database and see what happens if you change the
field type to numeric->Long. You could then add the employee table to your
report's record source as I suggested earlier.

This is another good reason why many of us never ever use lookup fields.

 

[Dan231]

I guess I'm too new at this....

I "think" I did what you said, but the report now shows error in the text
box, where I want it to display last, first. I can't even get it to show
last name only.

 

[Duane Hookom]

What is the SQL view of your report's record source?
What is the Control Source property of the text box on your report?

 

[Dan231]

What is the SQL view of your report's record source?
SELECT Problems.User, Problems.Problem, Problems.Fixed, Problems.Date,
Problems.Comments
FROM Problems
WHERE (((Problems.Fixed)="No"))
ORDER BY Problems.Date;

What is the Control Source property of the text box on your report?

=[OpReq]![LastName]

 

[Duane Hookom]

You need to add the employee table to the report's record source query.

Did you remove the lookup property stuff from the Problem table's User
field? What is the current type of the User field? What do the values look
like once you remove the lookup misfeatures?

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

What is the SQL view of your report's record source?

SELECT Problems.User, Problems.Problem, Problems.Fixed, Problems.Date,
Problems.Comments
FROM Problems
WHERE (((Problems.Fixed)="No"))
ORDER BY Problems.Date;

What is the Control Source property of the text box on your report?

=[OpReq]![LastName]

What is the SQL view of your report's record source?
What is the Control Source property of the text box on your report?

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm