Leading Zero Problem

[CarrieR]

Hello out there-

I need to populate a table with a range of numbers, the following code works great except when the numbers have a leading 0:

Private Sub cmdRunCode_Click()

Dim intStart As Long
Dim IntEnd As Long
Dim intReqNum As Long

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Select*From tbl_Test")
intStart = Me.txtStartNum
IntEnd = Me.txtEndNum
intReqNum = intStart
Do While intReqNum <= IntEnd
rst.AddNew
rst!ReqNum = intReqNum
rst.Update
intReqNum = intReqNum + 1
Loop
rst.Close
Set rst = Nothing


End Sub

I have tried changing the data type, of course in the table I have as text, but the preceding zeros are still being stripped out. Can anybody help me on this? (Many times I have been "stuck" on an Access issue and I always seem to get it resolved by posting on this site!)

 

[VBslammer]

You're using integer datatypes to enter new records to the table. If you want to add the numbers in a specific format you need to inform Access of that using the Format() function:
[tt]
rst!ReqNum = Format(intReqNum, "0000")
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[Blorf]

Hi.

Change
rst!ReqNum = intReqNum
to
rst!ReqNum = right ("0000000" & intReqNum, 7)

There are 7 zeros in my example, and # 7 at the right.

Replace Zeros and the number with the appropriate length, and I think you should be in business.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://mysite.verizon.net/res0n4sv/

 

[CarrieR]

Neither method worked. Problem also in that there will be instances on text in the field, such as N1234, other uses will be 052137777. Any suggestions?

 

[CarrieR]

Can anybody out there help me with this? Examples of data that will be input in a table: TE5010121 thru TE5010221, etc. I need to enter a beginning range of number and the ending number, to populate the table with the range. Can be all numeric or combination of number/text.

 

[Edski]

Carrie,

intStart = Me.txtStartNum
IntEnd = Me.txtEndNum

Surely you will get a Type mismatch error when you try to coerce a Text data type into a Long. You've got Text data and you're trying to perform a numeric calculation such as incrementing the value? Think about it. What is "A01" + 1? You think it should be "A02"? Think again!

You could try stripping the leading text characters off the text fields to convert them to Long type.

I have tried changing the data type, of course in the table I have as text,

It should be Text period. Don't even think about using another type.

 

[Edski]

Hi,
This bit of code will strip the leading Text characters off the variables. Note that I'm assuming that ALL the text fields either start with text character(s) or are fully numeric.

Dim txtStart As String, txtEnd As String
Dim intPos As Integer

txtStart = Me.txtStartNum
txtEnd = Me.txtEndNum

intPos = 1
While Not IsNumeric(Mid(txtStart, intPos, 1))
    intPos = intPos + 1
Wend
intStart = Mid(txtStart, intPos, Len(txtStart) - intPos + 1)
intReqNum = intStart

Then you can increment this number and put back the leading Text (if any) and then update it into the table. Leading zeros need to be taken into account but the others have given some good tips to get you started.

Hope that helps.

 

[CarrieR]

Edski

Thanks for your help, but I am still getting errors. (I am a visual basic dummy also). Here is the code so far:


Private Sub cmdRunCode_Click()
Dim txtStart As String, txtEnd As String
Dim intPos As Integer

txtStart = Me.txtStartNum
txtEnd = Me.txtEndNum

intPos = 1
While Not IsNumeric(Mid(txtStart, intPos, 1))
intPos = intPos + 1

Wend
intStart = Mid(txtStart, intPos, Len(txtStart) - intPos + 1)
IntReq = intStart

Do While intReqNum <= IntEnd
rst.AddNew
rst!ReqNum = intReqNum
rst.Update
intReqNum = intReqNum + 1
Loop
rst.Close
Set rst = Nothing



End Sub

Also the numbers can be any combination, either starting with numbers and followed by letters or vice versa, or be totally numeric. (Examples: A1689B through A1700B, TE5010070 through TE5010125. etc etc.) Error on rst.AddNew.

 

[Tranman]

Carrie,
What I would do is change my form so that I had 3 fields:
A letter prefix field, a numeric field, and a letter suffix field. This keeps you from having to mess with the letters in your code.

Then I would work only with the number part of it:

Dim intStart as Integer
Dim intEnd as Integer
Dim intCounter as Integer

intStart = val(Me.txtStartNum)
IntEnd = val(Me.txtEndNum)

For intCounter = 0 to (intEnd - intStart)
intstart = intStart + intCounter
rst.AddNew
rst!ReqNum = me.txtPfx & format(intStart,String(Len
(me.txtEndNum), "0")) & me.txtSfx
rst.Update
Next

This code will (I think, if I didn't mess up the parens), give you a string composed of any alpha prefix, plus a string of the numbers between the begin and end numbers you key (inclusive, with a length equal to the length of your end value), plus any alpha suffix.

Good Luck,
Tranman

 

[Tranman]

Carrie,
Whoa! Just noticed your values. Integers only go up to 32,000 something, so you will want to use Long datatype instead (Dim lngStart as Long).
Tranman

 

[CarrieR]

I will go ahead and try, and thank you all so very much. Will let you know if works. I really appreciate your help.

 

[CarrieR]

Tranman--

getting error code, method or data member not found, on me.TxtPfx (you also have as me.txtSfx??) Correction as to??

 

[Tranman]

Carrie,
Those are the new textboxes I suggested that you add to your form so the leading and trailing letters do not need to be part of the "number" field. It avoids having to mess with the characters in your code. So your form would look like:

Prefix Begin End Suffix
|R | | 49| | 0226| | |

And the code would generate fields containing:

R0049
R0050
R0051
.
.
R0225
R0226

Or if you had letters on the end:
Prefix Begin End Suffix
| | | 49| | 226| |N |

049N
050N
051N
.
.
225N
226N

You see what I mean. The "number" part of the resulting string will always be padded with leading zeros to the length of the "End" field.

The prefix/suffix part will be whatever you key into the prefix and/or suffix field. Or, if you don't have any letter prefix or suffix, the field generated will just contain the number range:

Prefix Begin End Suffix
| | | 49| | 226| | |

049
050
051
.
.
225
226

If you're still confused and frustrated, why not post a list of the input data that is giving you problems, and what you want the output to look like, and we'll lead you down the path of understanding. It will be easier to help you if we understand the whole problem.

Tranman

 

[CarrieR]

Gotcha. Fields created, but am getting run time error 424 on "RST.Add new". Below is the entire code in place.

Private Sub cmdRunCode_Click()

Dim intStart As Long
Dim intEnd As Long
Dim intCounter As Long

intStart = Val(Me.txtStartNum)
intEnd = Val(Me.txtEndNum)

For intCounter = 0 To (intEnd - intStart)
intStart = intStart + intCounter
rst.AddNew
rst!ReqNum = Me.txtPfx & Format(intStart, String(Len(Me.txtEndNum), "0")) & Me.txtSfx
rst.Update
Next


End Sub

 

[Tranman]

Carrie,
Assuming you are using ADO, add this line:
Dim rst as New ADODB.Recordset

Before your "For/Next Loop", add this line(all on 1 line):
rst.Open "<table you're putting the data in>", currentproject.connection, adOpenDynamic, adLockOptimistic

After your "For/Next Loop", add these lines:
rst.Close
Set rst = Nothing

Let me know if that does the trick.

Tranman

 

[Tranman]

Carrie,
Sorry, just saw another problem. The final product will look like this

Private Sub cmdRunCode_Click()
Dim lngWork as Long '****Add this line
Dim intStart As Long
Dim intEnd As Long
Dim intCounter As Long
Dim rst as New ADODB.Recordset

intStart = Val(Me.txtStartNum)
intEnd = Val(Me.txtEndNum)

rst.Open "<table you're putting the data in>", currentproject.connection, adOpenDynamic, adLockOptimistic

For intCounter = 0 To (intEnd - intStart)
lngWork = intStart + intCounter 'Modify this line
rst.AddNew
rst!ReqNum = Me.txtPfx & Format(lngWork, String(Len(Me.txtEndNum), "0")) & Me.txtSfx '****Modify this line
rst.Update
Next

rst.Close
Set rst = Nothing

End Sub

 

[CarrieR]

It worked! You are awesome! I really appreciate all your help (my boss does also!)

Thank you so much

Carrie

 

[Tranman]

Thank you for your kind words. I'm glad I could be of assistance.

It's nice of your boss to let you use this resource. So many places they don't want to let the employees get near the web because they might waste a few minutes.

The payback is, when you are a wizard, and no longer a noob, sign in once in a while and lend someone else a hand.

It was nice working with you.

Tranman (Paul)