question about an update query

[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]

 

[jimmythegeek]

Is the field (MarknUnit) a number field (integer, long integer)? If so, you could simply enter 000000 as the format for the field in the table. [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[montyb]

Jeez, that was easy. Brain fart on my part. I'm almost embarassed to have asked the question. Thanks for the help. [sig][/sig]

 

[jimmythegeek]

If you don't know the answer, then it's a good question! [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[montyb]

I ran into a little problem when I used the solution you provided. While all the fields that were not the full six characters in length (I have it set up as number/long int) now show zeros in front of them..the zeros don't carry over when I run the update query to combine both the Mark and UnitNo fields. When I put the cursor on any record that is short, it show the original value without the zeros.

How do I now make zeros stick? [sig][/sig]

 

[jimmythegeek]

Try putting this in the &quot;Update To&quot; row in your query:

CStr([UnitNo] & [Mark]) [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[montyb]

I'm afraid the zeros still don't stick.

If the Mark is CNRR and the UnitNo is 012345 (the zero showing because we updated the format to 000000 and it was originally showing 12345) and I run the update query to combine both fields, I get CNRR12345 (not CNRR012345). The above code gave me the same results that I get by putting in [Mark] & [UnitNo].

So, suffice to say, I'm still stumped...

Out of curiosity, why don't the zero stay when put the cursor on any of the records that were updated to zeros? When you put the cursor on the record, the begining zeros disappear and are replaced by a corresponding number of underlines. [sig][/sig]

 

[jimmythegeek]

I didn't realize your field (MarknUnits) contained letters. I thought you were combining to numbers (i.e. 22,33 = 002233) I didn't know your Mark field was text.

This changes things!

OK, this will work!

First, change all three fields (Mark, Unit, MarknUnit) to text. I assume this is what you want:

Mark Unit MarknUnit
----------------------------------
CNRR 123 CNRR000123

Copy the following function (between the lines) into a blank module, and save the module as anything you want:

====================
Public Function GetMarknUnit(strMark As String, strUnit As String) As String
Dim intLen As Integer, intTimes As Integer, i As Integer
Dim strTempUnit As String

intLen = Len(strUnit)
intTimes = 6 - intLen

strTempUnit = &quot;&quot;
For i = 1 To intTimes
strTempUnit = strTempUnit & &quot;0&quot;
Next i
strTempUnit = strTempUnit & strUnit

GetMarknUnit = strMark & strTempUnit
End Function
====================

Now in your update query, put the following in the &quot;Update To&quot; Row:

GetMarknUnit([Mark], [UnitNo])

This should work, I tried it, and I think this will give you the results your looking for. [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]