Leading Zeroes Needed

lembek
lembek used Ask the Experts™
on
I need to add leading zeroes to a string of alpha numeric characters in order to make the character length of all the data in this field the same length.

Example:

Current            Desired
LUTX1459321        LUTX01459321
LUTX14593210       LUTX14593210
LUTX1459322        LUTX01459322
LUTX14523          LUTX00014523

Not sure what to do about this?  Any help would be greatly appreciated.

Thanks,
Karl
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Are you converting old data, or trying to enter new data and keep this format?

If I were converting old data, I'd use a VB macro to loop the table, and make the changes record-by-record.

Author

Commented:
I'm converting old data, actually from another database but using access to do it...

Commented:
OK, try this...

Goto Modules, click New.

The VB editor pops up; copy&paste:

Function doConv(s As String) As String
  Dim o As String
  o = Left$(s, 4)
  s = Right$(s, Len(s) - 4)
  s = Format(s, "00000000")
  doConv = o & s
End Function

Sub testConvert()
  MsgBox "Result: " & doConv(InputBox("What value?"))
End Sub

Then, click on testConvert, and hit the 'Run' arrow (or press F5).
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
lembek:
You can use a query to do this, although a few things need to be clarified.. will the current values always begin with a static number of characters [4, in the case of your examples]?

If so, you can do this in a query:
----------------------------------------------------
UPDATE test SET test.[values] = Left([values],4) & Format(Mid([values],5),"00000000");
----------------------------------------------------

putting this in a query avoids having to loop through each of the records in a vb module..

Please let me know if you need further clarification..
geo

Author

Commented:
Bog,

The results of the test were great...now to apply them...I'm sorry, I really am a novice at this, but trying to learn...

Zabba,

I will try the query...


Thanks!!!

Commented:
lembek:
I should have pointed out (just in case you didn't know) that test is the name of the table, and values is the name of the field you're updating..

sorry for the initial lack of information..

geo

Author

Commented:
Sorry Geo,

I need clarification...I don't know this stuff well at all...I tried putting it into a query...am I supposed to substitute my field for [values]?

Thanks for your help, I really appreciate it...

Commented:
yes, definitely substitute your field's name for [values]..

:)

geo

Author

Commented:
Ooops...you beat me to it!!!

Thank you!!!

Author

Commented:
I apologize for my ignorance, but I created a small table with some examples called tbl_Test, the only field in the table is Bates, I am running an update query with Tbl_Test as the table and Bates as the field and in the Update to field I have the following query:

[UPDATE tbl_Test SET tbl_Test].[Bates]=Left([Bates],4) & Format(Mid([Bates],5),"00000000")

When I run it, it asks for a parameter value? What do I enter here...

Sorry again...but I really appreciate it...

:)
Commented:
it looks like you've got the left square bracket in the wrong place: make your sql like this:

UPDATE tbl_Test SET [tbl_Test].[Bates]=Left([Bates],4) & Format(Mid([Bates],5),"00000000")

and, please don't apologize, we've all been new at this at one point or another & if you're not familiar with the syntax, it is definitely confusing..

we're here to help..

geo

Author

Commented:
Geo,

Thanks for the encouragement...the query didn't seem to help, although I am sure I'm just missing something...this is the field before the query:

Bates
LUTX14593
LUTX14593210
LUTX1459321
LUTX1459
LUTX1459322
LUTX14

This is after:

Bates
0
0
0
0
0
0

Although it didn't ask for any input this time, it just updated the 6 rows...progress!!!  : )

Commented:
lembek:
I copied the exact sql statement you are using, just modifying the names of the tables & fields and it worked fine..

what type of data is your field? text, integer, etc?

geo

Author

Commented:
Text field...should I change this? I thought because it was alpha numeric it had to be text...

Author

Commented:
I took out the left half of the equation and made the statement:

Left([Bates],4) & Format(Mid([Bates],5),"00000000")

and it seemed to work!?!?

I'm going to make a copy of the real DB and see what happens with that...Thank you so much for your help and patience...

Commented:
I'm a little confused, though..

If you switch to SQL view in a query, you can just paste what I gave you into the window and run the query.

hmmm..
geo

Author

Commented:
When I ran your query in SQL view, it worked like a charm, but for some reason in Design View, I had to remove the left half to get it to work...I don't know, but it works...thanks again...

Author

Commented:
Thanks for your patience!!! I know it must have been trying!!!

Commented:
Geez... don't hate me...

I had to get up and go, sorry I couldn't finish.

Author

Commented:
Nothing but love bog....I appreciate the help...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial