splitting alpha numeric but keeping the zeroes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that allows me to split an alph-numeric (ex: D0015) but when
the code runs the zeroes are dropped. I need to have it split to D and 0015.
Below is the code that I have. It works well but its the zero thing I can't
figure out.

Dim Check As String
Dim lngLastRecordNo As Long
Dim lngPos As Long

Check = True

'go to last record
DoCmd.GoToRecord acActiveDataObject, , acLast

' update file
lngLastRecordNo = Me.CurrentRecord
DoCmd.GoToRecord acActiveDataObject, , acFirst

' Outer loop
Do

' Innerloop
' Set repetitions
For lngPos = 1 To Len(Me.FileNumber) Step 1
If Mid(Me.FileNumber, lngPos, 1) >= 0 And Mid(Me.FileNumber,
lngPos, 1) <= 9 Then

'found start of numbers
Me.FileLetter = Left(Me.FileNumber, lngPos - 1)
Me.FileNo = CLng(Mid(Me.FileNumber, lngPos))

'have finished for this one
Exit For

Else

'a letter get next chr
End If

Next lngPos

'this is how we exit when all records have be done
If Me.CurrentRecord = lngLastRecordNo Then Exit Do

DoCmd.GoToRecord acActiveDataObject, , acNext

'we never get to true we exit when all records have been visited
Loop Until Check = False

Can anyone see what needs to be added or changed?
Thanks for any help given.
*** John
 
It's losing the zeros because, by using Clng, you are coercing it to be a
number. If you want to retain the leading zeros you will have to leave it
as a string (and if FileNo is a field in your database, it will have to be
Text).
 
Thanks for the quick response. It is a numeric field. Just after I sent the
initial question I was informed that the users want to be able to select the
next sequential number available. If it is text they won't be able to do
that. So it needs to remain numeric. Any other suggestions?
 
1. Why will they not be able to do that? Numbers stored as text in a
consistent format will still be ascending numeric order.

2. You absolutely cannot store a numeric field with leading zeros, the
concept is meaningless.

3. Why do you want the leading zeros? If it is purely for presentation
purposes, why not simply format the field appropriately whenever you display
or report it? The following format expression, when used in, say, a text
box's format property, will show a number with up to 4 leading zeros: 0000
 
Sorry, but you can't have it both ways! <g>

If it's numeric, you can't keep the zeroes. End of story.

You can apply a format of "0000" to the field, but that just changes how it
appears, not what's stored.

If the field's text, you can get the next available number using
Format(CLng(Nz(FileNo, 0) + 1, "0000")
 
Not able to have my cake and eat it to. Darn. Oh well.
I will try the format line you provided to see how that will suffice.
Thanks for the responses.
 
Back
Top