Adding Leading Zeros - Easy Way? 2

[jt463]

I have records in one database that are formatted like so:

Subdivision-01
Subdivision-21
etc...

In another database, they are formatted as two separate records:

sub 001
sub 021

I am working to bring the two together by adding two fields to our original database so that we can tie the two together.

However, I am trying to figure out a way I can do an insert that will automatically add the leading zeros - IF NECESSARY. What I mean by that is that we could have a record that is: Subdivision-01 where I need to add 1 zero to make it '001', but then we could also have a record that is Subdivision-100 where I don't need to add any.

I would need the code to look at the record from the first database (example: Subdivision-01) and tell it to only examine the data to the right of the '-'. Then, it will need to determine if it is 3 characters in length, and if not - then it will need to add zeros to get the 3 character length. (Is this possible?!?) I will hard-code the proper correlating project name from the data left of the '-'.

In other words: Subdivision-01 should translate to 'sub' and '001'.

My second database is a 3 char fixed text length. The original database is simply a text field where the lot numbers are anywhere from 2 to 3 character lengths.

Any ideas would be greatly appreciated!

I am going to need to do this in two stages. I am going to write a public function which will look at my existing records and update them with the second database format. Then, I will write a private sub to make sure they are updated, going forward.

 

[Golom]

Something like

Where Right("000" & Mid(1,tbl1.SD_Field,Instr(tbl1.SD_Field,"-") + 1)), 3)
      =
      tbl2.SD_Number

Where
[tt]
tbl1.SD_Field --> The fields formatted as "Subdivision-01"
tbl2.SD_Number --> The fields formatted as "001"
[/tt]

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Remou]

Here is one thought, SQL:
[tt]UPDATE tblTable SET tblTable.ID2 = Format(Mid([ID],InStr([ID],"-")+1),"000");[/tt]
For the purposes of the example, ID contains Subdivision-... and ID2 is a blank field in the same table.

 

[jt463]

Thank you Golom and Remou!!!

I used a variation of your suggestions and had success with the first part. Based on this, I feel fairly confident with the left portion of part 2!

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim bID As String


Set db = CurrentDb
Set rst = db.OpenRecordset("select * from NewLotInformation")

rst.MoveFirst

SQL:

bID = Format(Mid("" & rst!Subdivision & "", InStr("" & rst!Subdivision & "", "-") + 1), "000")


rst.Edit
rst!building_id_ = bID
rst.Update

rst.MoveNext

If rst.EOF Then
    Exit Function
Else
    GoTo SQL:
End If


End Function

Thank you so much for your time.

 

[jt463]

One thing is holding me up in my last portion of this project. I can't figure out how to use your suggestions and apply it to the following code to NOT include the '-':

sID = Left("" & rst!Subdivision & "", InStr("" & rst!Subdivision & "", "-"))

Your help would be greatly appreciated. it may have to do with that I am not familiar with exactly what this code is doing. I understand the 'Left' property but am a bit shakey on the 'InStr' property.

Thanks!

 

[jt463]

I figured out a workaround. I simply added a '-' to the end of the Subdivision name!

I still would like to understand the code better if anyone would be willing to answer my previous question.

Thanks!

 

[Remou]

InStr("" & rst!Subdivision & "", "-")
Gives the position at which - occurs. So for Left, you need a minus 1.
InStr("" & rst!Subdivision & "", "-")-1
That is, the position just before -.
You will notice that the snippet above has a plus 1, that is, the position just after -.

 

[Golom]

sID = Left("" & rst!Subdivision & "", 
InStr("" & rst!Subdivision & "", "-")[COLOR=red] - 1[/color])

What it's doing

"" & rst!Subdivision & ""
Concatenates "" in case the "Subdivision" field is NULL

InStr("" & rst!Subdivision & "", "-")
Returns the character position of the first occurence of "-" in rst!Subdivision (or 0 if it isn't found.)

InStr("" & rst!Subdivision & "", "-") - 1
Returns the character position before the one in the above step.

Left (Left("" & rst!Subdivision & "", ... )
Returns the characters in rst!Subdivision starting with position 1 and ending with the character determined on the step above.

WARNING:
If rst!Subdivision doesn't contain a "-" character then

InStr("" & rst!Subdivision & "", "-") - 1

will return -1 and the system will raise an "invalid argument" error.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[jt463]

Both of those comments are extremely helpful for me. I feel like I understand now.

Thanks to both of you for your time and patience.