Line Total shows #Error on form when trying to show concatenation

[DarrellC]

(I have organized this in notepad prior to pasting it here in the forum because I wanted to include as much information as possible. Hopefully it's clear enough to understand.)

I'm relatively new to building/modifying a database like the one happening now. So, I cheated a little bit, and I am attempting to make a point of sale Db by modifying Microsoft's template "Order Entry" available in Access. (I hope that isn't a cardinal sin of database building, but it has almost everything I need in one convenient package already.)

TASK AT HAND: I need the subform combo box (Order Details Subform.ProductID) to show certain columns from the Products Table.

I have searched the forum, and managed to get this to happen. However, the process of making the combo box show all these columns has caused an error in my UnitPrice, which leads to an error on my LineTotal on the Orders form.

Below are the lines of the ProductID RowSource.

ORIGINAL LINE FOR ProductID RowSource:
SELECT Products.*, Products.ProductName FROM Products ORDER BY Products.ProductName;

----
My Change #1:
This RowSource attempt at adding concatenation will give correct totals, but NO columns will show in the combo box. Which means the concatenation is serving no purpose whatsoever, it's just there, but not causing any noticeable issues.

SELECT Products.*, ProductName & ", " & CategoryName & ", " & VendorName & ", " & "$" & UnitPrice FROM Products ORDER BY Products.ProductName;

RESULTS of My Change #1:
Order Details Subform
Product...Quantity..Discount...UnitPrice...LineTotal
JASMINE...2.........0 .00%.....$8.00.......$16.00

----
My Change #2:
This one will show all listed columns in the combo box, but causes an error on the UnitPrice. All I did here is change the asterisk to the ProductID column name along with the &'s.

SELECT Products.ProductID, ProductName & ", " & CategoryName & ", " & VendorName & ", " & "$" & UnitPrice FROM Products ORDER BY Products.ProductName;

RESULTS of My Change #2:
Order Details Subform
Product........................Quantity..Discount...UnitPrice... LineTotal
JASMINE, BATH BOMB, STARR, $8..2.........0.00%......empty........#Error

--:banghead:--

Can anyone tell me why this change affected the unit price, and what can I do to fix it?
It needs to show the columns as in change #2, without affecting the unit price.

 

[DarrellC]

I figured it out by searching for similar items in the Northwind Database. Here is my solution. I'm posting it here so others might be able to find it.

This displays all the columns I select in the ComboBox without messing with my totals and without concatenation or +'s.

SELECT DISTINCT Products.*, [ProductName], [CategoryName], [VendorName], [UnitPrice] FROM Products ORDER BY [ProductName];

Order Details Subform
Product........................Quantity..Discount...UnitPrice... LineTotal
JASMINE, BATH BOMB, STARR, $8. 2.........0.00%......$8.00......... $16.00