splitting a field

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

Guest

Hello. I have a db that has a field for file numbers. An example is W12345
or WA1234. What the mgrs want to do now (after thousands of records) is to
change the way the file numbers are done. They would like the beginning
letters split off from the numbers and placed into a separate field. What
the user will do is use a combobox to select the appropriate prefix. The
numbers in the original field will remain only that they would like to have
the number field generate automatically the next sequential number rather
then having someone type it in.
Mainly, my question is removing the leading letters and placing them in
another field. For this I am at a loss and seek help. How would something
like this be accomplished?
Thanks in advance for your response.
*** John
 
Hi John E,

Not sure which is best way to go about this but this is one solution

All you have to do is create a procudure that goes through every record in
the table.

I have assumed that

1) a Letter always starts a field
2) a number always finishes a field
3) once a number has been found all the rest are numbers



In your table create 2 new fields:-

FileLett Str
FileNo number Long Interger

the following code will populate the above new fields with the necessary data


Dim lngPos As Long

For lngPos = 1 To Len(Me.FileNumber) Step 1 ' Set repetitions.


If Mid(Me.FileNumber, lngPos, 1) >= 0 And Mid(Me.FileNumber, lngPos, 1)
<= 9 Then

'we have found start of numbers
Me.FileLetter = Left(Me.FileNumber, lngPos - 1)
Me.FileNo = CLng(Mid(Me.FileNumber, lngPos))
Exit For 'have finished for this one

Else
'we have a letter get next chr


End If


Next lngPos

Good luck

Trev
 
Trevor, thanks for the info. Your 3 assumptions are correct. I will give
your solution a try. This is an embarrasing question, but where do I place
this code and how do I run it?
.... John
 
Hi John,

Before you start make sure you have copies just in case it goes wrong.

Don't be embarrased I did not know before I built my first one. Will not be
easy this, however, I will try to do it not longwinded and in steps so you
can always ask more about a step.


Step one

Create a form from your table including the following fields ID,
FileNumber,FileLett,FileNO

Step two

Creaste a command button on your newly created form and put following code
in it

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

Check = True

DoCmd.GoToRecord acActiveDataObject, , acLast 'goto last record
lngLastRecordNo = Me.CurrentRecord ' update file
DoCmd.GoToRecord acActiveDataObject, , acFirst

Do ' Outer loop.

' Innerloop

For lngPos = 1 To Len(Me.FileNumber) Step 1 ' Set repetitions.


If Mid(Me.FileNumber, lngPos, 1) >= 0 And Mid(Me.FileNumber,
lngPos, 1) <= 9 Then

'we have found start of numbers
Me.FileLetter = Left(Me.FileNumber, lngPos - 1)
Me.FileNo = CLng(Mid(Me.FileNumber, lngPos))
Exit For 'have finished for this one

Else
'we have 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

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


Good luck

Trev
 
dude you're a retard

just use real integer keys, anything else is a complete waste of time
 
Trevor, I ran it thru a small test db and it work perfectly. Thank you very
much for you help on this.
*** John
 
Back
Top