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!
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!
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:
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
basFindDate=CDate(mid$(InputField,i,2) & "/" & mid$(InputField,i+2,2) & "/" & mid$(InputField,i,4))
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.