Different data type in an update query

[kupe]

I want to say update set field1 = field2

But field1's data type is Number and Field2 is Text. (Field1 is fed by a lookup table). Am I doomed to go through the 700 or so by hand, gurus, please?

 

[ByteMyzer]

You said Field1's Data Type is Number. What is the field size (Byte, Integer, Long Integer, Double)?

Try something like:

UPDATE Table1
SET Table1.Field1=[b]CLng[/b](Table1.Field2);

...to update Field1 to the value in Field2 converted to a Long Integer.

You may substitute CLng with any of the following, depending on Field1's actual field size:
CByte - convert to Byte
CInt - convert to Integer
CSng - convert to Single
CDbl - convert to Double

 

[kupe]

Hi ByteMyzer

Thanks for your help. What I have then is

UPDATE tblEmployees SET UnitID = CLng(Hostel);

UnitID is a Long Integer being part of a look-up table. Hostel contains the data that could be updated straight into UnitID, except that the warning says type conversion failure. Hostel is text.

Both fields are in the same table, tblEmployees;

It's not working. Do you see what I am doing wrong, please?

 

[kupe]

Oh ... do you mean something like UPDATE tableOne FROM tblLookUp WHERE thisID = LookUp.thisID?

But dooes the lookup table have the individual records? It's (they are) the one of a one-to many link ...

Sorry, Pat, new territory for me. I'll give it a try this afternoon (or as soon as I can get back to that db) and report back.

Much obliged to you.