If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#ERROR and currency issues
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 |
#2
|
|||
|
|||
#ERROR and currency issues
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") Dos Equis wrote: 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 |
Thread Tools | |
Display Modes | |
|
|