Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

question about an update query

question about an update query

question about an update query

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.

RE: question about an update query

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.

Jim Lunde
Custom Application Development

RE: question about an update query

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

RE: question about an update query

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?

RE: question about an update query

Try putting this in the "Update To" row in your query:

CStr([UnitNo] & [Mark])

Jim Lunde
Custom Application Development

RE: question about an update query

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.

RE: question about an update query

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 = ""
    For i = 1 To intTimes
        strTempUnit = strTempUnit & "0"
    Next i
    strTempUnit = strTempUnit & strUnit
    GetMarknUnit = strMark & strTempUnit
End Function

Now in your update query, put the following in the "Update To" Row:

GetMarknUnit([Mark], [UnitNo])

This should work, I tried it, and I think this will give you the results your looking for.

Jim Lunde
Custom Application Development

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close