Convert Numeric to Text Fields, Help!!!

[Guest]

Long story short... dealing several legacy access databases that I inherited
and now I have run into a brick wall (caused by one data field).

I need to find a quick/simple way to convert a NUMBER field to a text field
for comparison within an update query. I realize simplest would be to go
through and convert the offending field to text, but unable to do that
immediately, is there a simple alternative until I can convert the offending
databases and their fields?

Need to go from: NUMBER (Long Integer)
to: TEXT (50)

Thank you in advance.

MJ

 

[Douglas J. Steele]

It's not clear to me where you want to do this: at the table level, or
simply for comparison purposes in a query.

Assuming it's in a query, you can use the CStr function to convert the
number to a string. Remember, though, that if the text representation for
the numeric value has leading zeroes, you won't be able to match.

 

[Pieter Wijnen]

CStr(offendingfield)

HTH

Pieter

 

[Guest]

If I can do this within the update query, that would be great. Below is the
SQL view of the query:

UPDATE CashAudit INNER JOIN tblIndividuals ON CashAudit.EmployeeID =
tblIndividuals.EmployeeID SET CashAudit.EmpName = tblIndividuals.Employee
WHERE (((tblIndividuals.Team)="Customer Service"));

The CashAudit.EmployeeID is the numeric field. Is it possible to resolve
this comparison within the query?

Thanks again for your quick reply.

MJ

 

[Douglas J. Steele]

Try

UPDATE CashAudit INNER JOIN tblIndividuals ON CStr(CashAudit.EmployeeID) =
tblIndividuals.EmployeeID SET CashAudit.EmpName = tblIndividuals.Employee
WHERE (((tblIndividuals.Team)="Customer Service"));

If tblIndividuals.EmployeeID stores them with leading zeroes, try

UPDATE CashAudit INNER JOIN tblIndividuals ON Format(CashAudit.EmployeeID,
"00000000") =
tblIndividuals.EmployeeID SET CashAudit.EmpName = tblIndividuals.Employee
WHERE (((tblIndividuals.Team)="Customer Service"));

(replace "0000000" with however many digits you need)

 

[Pieter Wijnen]

try

UPDATE CashAudit , tblIndividuals
SET CashAudit.EmpName = tblIndividuals.Employee
WHERE tblIndividuals.Team="Customer Service"
AND CashAudit.EmployeeID = Clng(tblIndividuals.EmployeeID)

don't remember wether Access deems that as an updatable query
if noy you have to create code using recordsets

HTH

Pieter