update query question

[montyb]

I have a field called "UnitNo" that I have combined with another field called "Mark" into one field called "MarknUnit". These are identifiers to rail cars that I am tracking. I need to relate these records to another table. The problem is that quite a bit of the UnitNo records are incomple (they should be six characters long, many of which are only four to five characters long). I need to update the incomplete records by putting 0s at the beginning of the record (ex. 3466 to 003466). This will allow the record to relate. How do I do this with an update query? Thanks in advance.


[sig][/sig]

 

[Amiel]

Assuming that the field in question is of the 'String' data type ...


"Update [table1] SET [UnitNo] = "00" & [UnitNo] Where Len([UnitNo]) = 4

"Update [table1] SET [UnitNo] = "0" & [UnitNo] Where Len([UnitNo]) = 5


... no problem. B-) [sig]<p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br> [/sig]

 

[montyb]

Amiel, thanks for the help.

Question: Should I be entering the above coding to the &quot;update to&quot; area of the update query. I get the error message &quot;The expression contains ivalid syntax. You may have entered an operand without any operator.&quot; The cursor then blinks on &quot;Where&quot;. Otherwise, this looks exactly like what I would need to do. [sig][/sig]

 

[Amiel]

Click on Queries, click 'New', 'Design View', 'Close' show table dialog, click 'View', click 'SQL view', cut and paste the statement, change the table and/or field names where necessary, save the query.

Suggest executing this against a copy of your data to be sure you're getting the desired results. [sig]<p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br> [/sig]