Split field in 2

[sueviolet]

I would like to split the following data in 2 (split at the comma). The field this data is contained in is a text field.

(2FBSRY, 01-JAN-1995)

Thanks for all your help.

 

[FoFa]

Is it always ( 6 characters comma space 11 characters )
if it is then mid([colname],2,6) would pull out the 2FBSRY and mid([colname],10,11) would pull the 01-JAN-1995
you can use that in a query.

 

[sueviolet]

Thanks for your help,

No the split is not always after 6 characters, so I need it be split at the comma

 

[Mile-O]

Part1: Mid(Left([MyField], 2, InStr(1, [MyField], ",") - 1), 2)

Part2: CDate(Left(Mid([MyField], InStr(1, [MyField], ",") + 2), Len(Left(Mid([MyField], InStr(1, [MyField], ",") + 2))-1)



It think....

 

[sueviolet]

Thanks for you help!

Sorry, I don't know enough about code to modify the above code so it works correctly...

I get the following error:

"the expression you entered has a function containing the wrong number of arguements"

?

 

[Mile-O]

Doing this in my head...bracket overload...

Part1: Mid(Left([MyField], InStr(1, [MyField], ",") - 1), 2, Len(Left([MyField], InStr(1, [MyField], ",") - 1)) - 1)

 

[sueviolet]

Thanks for helping me, I really appreciate it

Now I get the error: "data type mismatch in criteria expresion"

The field I am wanting to split is a text field

Thanks again

 

[robthedog]

Hi

You might like to try creating your own functions to do the job for you. Open a new module and paste the following code after “Option Compare Database”:

‘########################################################

Option Explicit

Dim LenField, Start

Function getFirstBit(OldValue)

    If InStr(1, OldValue, ",") Then

        LenField = InStr(1, OldValue, ",") - 1
        getFirstBit = Trim(Mid(OldValue, 1, LenField))

    Else
        getFirstBit = "ERROR"
    End If

End Function


Function getSecondBit(OldValue)
    
    If InStr(1, OldValue, ",") Then
    
        Start = InStr(1, OldValue, ",") + 1
        LenField = Len(OldValue) - InStr(1, OldValue, ",")
        getSecondBit = Trim(Mid(OldValue, Start, LenField))
    
    Else
        getSecondBit = "ERROR"
    End If
    
End Function

‘#####################################################

You can then use your functions directly in a query using the following syntax:

NewValue1: getFirstBit([YourField])
NewValue2: getSecondBit([YourField])