Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Posts
    64

    Angry Unanswered: Extracting numbers from text string

    I'm going into brain implosion mode, so thought I would seek help before I go down the wrong path. I need to extract some numbers from a text field which represent a date. For example, I have the string "DEL 040302 BTCH" and I need to get the 040302 out to reformat as a date.

    I am developing in Access 2002. Any help greatly appreciated!

    PS Don't ask why they don't store the date separately, I seem to meet much resistance when suggesting changes to data entry onto the mainframe!

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Are the dates always expresed as six digits?

    Are they always in the middle of the field?

    Are they the only numeric characters found in this field, or do some records have the date along with some other numeric information?

  3. #3
    Join Date
    Feb 2002
    Posts
    64
    Oh, sorry, yes they are always the only numeric characters in the field and in six digit format. They are sometimes following by alpha characters, but sometimes not depending on whether it was a batch delivery.

    Again, I know it would be better to get them to change the data inputting.... We can't go there!

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    If the first position of the first numeric character is fixed (always the same position), then you are just taking the mid$() of that field.

    If that first position is variable, then you are going to have to pass the field to a function in order to determine the first numeric position, which you will then use in a mid$() of the field.

    The function would look like this:

    Code:
    Function basFindDate(InputField as string) as Date
    
         Dim i as byte  'Assumes that the field is no more than 255 characters
    
         For i = 1 to Len(InputField)
              If IsNumeric(mid$(InputField,i,1))=True Then
                   Exit For
              End If
         Next i
    
         basFindDate=CDate(mid$(InputField,i,2) & "/" & mid$(InputField,i+2,2) & "/" & mid$(InputField,i,4))
    
    Exit Function
    I just wrote this quickly, so you're going to have to debug it.

    It assumes that the InputField always contains a six-digit date. If that is not the case, you are going to have to errortrap for i=Len(InputField)+1.

    It also assumes that the date is the only numeric in the InputField.

  5. #5
    Join Date
    Feb 2002
    Posts
    64

    Thumbs up

    That'll work with a slight bit of tweaking.

    A million thanks for the solution so quickly!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •