#ERROR and currency issues

[Dos Equis]

I am building a qeury to identify how much each carrier is paid. I
have all the elements available but not sure how to proceed from here.
Each carrier is identified by CID and within the carrier table is a
field for pay so I should be able to multiply Homes*Pay and end up with
that carriers pay for homes delivered, all I get is #ERROR.
Subscribers Pay is working but can't figure out how to convert the
product to currency.
oh, this query is based on two other queries as I couldn't get it to
all work on 1 query and when I tried to use the queries to make a
report I kept getting an error about not being able to join information
from them.

All code is below:

SELECT qry_AreasAndHomesByCarrier.CID,
qry_SubscribersByCarrier.Carrier, qry_AreasAndHomesByCarrier.Areas,
qry_AreasAndHomesByCarrier.Homes, qry_SubscribersByCarrier.Subscribers,
[Homes]*[Pay] AS [Home Pay], [Subscribers]*0.2 AS [Sub Pay]
FROM (qry_SubscribersByCarrier INNER JOIN qry_AreasAndHomesByCarrier ON
qry_SubscribersByCarrier.Carrier = qry_AreasAndHomesByCarrier.Name)
INNER JOIN tbl_Carrier ON qry_AreasAndHomesByCarrier.CID =
tbl_Carrier.CID
GROUP BY qry_AreasAndHomesByCarrier.CID,
qry_SubscribersByCarrier.Carrier, qry_AreasAndHomesByCarrier.Areas,
qry_AreasAndHomesByCarrier.Homes, qry_SubscribersByCarrier.Subscribers,
tbl_Carrier.Pay, [Subscribers]*0.2;


Thanks for any help,

Byron

 

[Jeff L]

Two thoughts on the #ERROR:
1. Homes is not a numeric value. Check your table to make sure it's
not text.
2. There is more than one field in your query that Homes could be
referring to. However if that were the case, you'd normally get an
error when you tried to run the query.

To format your value in currency:
Format([Subscribers]*0.2, "Currency")