Convert Numeric Field to text in Query

[sara]

I have a mess on my hands. I have a whole set of queries and reports
(almost a whole app, really) that has "location" as a text field.
Sometimes it's a number and a few times it's text (001, 002, and ADV,
TRN, MER). I now have to link on "LocationCo de" (text)and "StoreNum"
(numeric - from the other application - all numeric).

I want the query to put the values for 001 from the numeric side with
the values for 001 from the text side, but I keep getting "type
mismatch in expression".

Is there a way to tell Access to use the Text as Numeric or vice-versa
in either of the underlying queries so I can get what I need?

I need the Text fields - ADV, TRN, MER (and their associated Payroll
numbers)from the table/query that is text LocationCode.

SELECT qryYTDPRRawData forDashboardPRt oSales.Location Code,
Sum(qryYTDPRRaw DataforDashboar dPRtoSales.Payr ollThis) AS
YTDPayrollThis,
Sum(qryYTDPRRaw DataforDashboar dPRtoSales.Payr ollLast) AS
YTDPayrollLast,
qryYTDSalesRawD ataforDSashboar dPRtoSales.[YTDTY Sales] AS YTDSalesThis,

qryYTDSalesRawD ataforDSashboar dPRtoSales.[YTDLY Sales] AS YTDSalesLast,

[YTDPayrollThis]/[YTDSalesThis] AS YTDPercentSales This,
[YTDPayrollLast]/[YTDSalesLast] AS YTDPercentSales Last,
qryTotYTDChainS alesforDashboar d.ChYTDSalesThi s,
qryTotYTDChainS alesforDashboar d.ChYTDSalesLas t
FROM qryTotYTDChainS alesforDashboar d,
qryYTDPRRawData forDashboardPRt oSales INNER JOIN
qryYTDSalesRawD ataforDSashboar dPRtoSales ON
qryYTDPRRawData forDashboardPRt oSales.Location Code =
qryYTDSalesRawD ataforDSashboar dPRtoSales.Stor eNum

GROUP BY qryYTDPRRawData forDashboardPRt oSales.Location Code,
qryYTDSalesRawD ataforDSashboar dPRtoSales.[YTDTY Sales],
qryYTDSalesRawD ataforDSashboar dPRtoSales.[YTDLY Sales],
qryTotYTDChainS alesforDashboar d.ChYTDSalesThi s,
qryTotYTDChainS alesforDashboar d.ChYTDSalesLas t
HAVING (((qryYTDPRRawD ataforDashboard PRtoSales.Locat ionCode)<>"020" ));

Thanks - I don't know what to do anymore! Put the numeric data into a
temp talbe with a text field?

Sara

[David Seeto via AccessMonster.com]

Access does have some conversion functions between the various types that
you can use - however, the simplest thin gin this case would probably to
have an expression column that changes your number field to a text field
using Format$(Locatio n,"000") and joining that field to the other table
where Location is a 3 character text field...

--
Message posted via http://www.accessmonster.com

[sara]

Fantastic!! This worked. I figured there must be some sort of
conversion for this...I'm sure I'll now have occasion to use it again.
Again, I thank you.
Sara