Convert Numeric to Text Fields, Help!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
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)
 
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
 
Back
Top