Convert Numeric to Text Fields, Help!!!

M

MJ

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
 
K

Ken Snell \(MVP\)

Cast the number field with CStr in the update query:

UPDATE TableName
SET FieldName = "SomeValue"
WHERE CStr(NumberFieldName0 = TextFieldName;
 
M

MJ

Ken,

I tried to work the CStr() into the SQL view, but it did not care for it.
Below is the current SQL view of the offending query:

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

ATTEMPTED:
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
 
K

KARL DEWEY

You can not update a number field with text.
In your query you can use a calculated field to join (you will not be able
to see it in design view).
SELECT CashAudit.*, tblIndividuals.*
FROM CashAudit INNER JOIN tblIndividuals ON CStr(CashAudit.EmployeeID) =
tblIndividuals.EmployeeID
WHERE (((tblIndividuals.Team)="Customer Service"));
 
J

John Spencer

You might try the following.

UPDATE CashAudit INNER JOIN tblIndividuals
ON CashAudit.EmployeeID & "" = tblIndividuals.EmployeeID
SET CashAudit.EmpName = [tblIndividuals].[Employee]
WHERE tblIndividuals.Team="Customer Service"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top