Seperating a data field 1

[Dophia]

Hello everyone: I am trying to separate a field into three fields

The data would be like:

ABC34433-061513
AB3335-061513
OR
ABC34433-0615131 (extra number at the end)

The end is the date "061513" equals June 15 2013. The dash will be consistent. How can I separate the date from the data in front of the dash? Also, one more issue is that sometimes, there will be a number after the date, which I would like in a separate field.

Ex: ABC34433-0615131

Can someone point me in the right direction. I have looked at Allen Browne's ParseWord and the Split function. But it hasn't worked for me yet.

Thank you, Sophia

 

[Andrzejek]

Check the VBA (build-in) functions: [tt]Split, Len[/tt] and [tt]Mid[/tt]

Have fun.

---- Andy

 

[MajP]

Public Enum Part
  LeftPart = 0
  TheDate = 1
  Extra = 2
End Enum

Public Function splitText(varText As Variant, ThePart As Part) As Variant
  Dim aParts() As String
  If Not IsNull(splitText) Then
    aParts = Split(varText, "-")
    If ThePart = LeftPart Then
      splitText = aParts(LeftPart)
    ElseIf ThePart = TheDate Then
      splitText = Left(aParts(TheDate), 6)
    ElseIf ThePart = Extra Then
      splitText = aParts(TheDate)
      If Len(splitText) > 6 Then
        splitText = Right(splitText, Len(splitText) - 6)
      Else
        splitText = ""
      End If
    End If
  End If
End Function

Public Sub testsplit()
  Dim theText As String
  theText = "ABC34433-0615131"
  Debug.Print splitText(theText, LeftPart)
  Debug.Print splitText(theText, TheDate)
  Debug.Print splitText(theText, Extra)
  theText = "ABC34433-061513"
  Debug.Print splitText(theText, LeftPart)
  Debug.Print splitText(theText, TheDate)
  Debug.Print splitText(theText, Extra)
End Sub

 

[Dophia]

Thank you MajP, but I am getting a compile error and I can't figure it out.

Sophia

 

[Dophia]

I tried to solve the problem and this works up to the last line.

Public Function splitText(varText As Variant, ThePart As Variant) As String
Dim aParts() As String

If Not IsNull(splitText) Then
aParts = Split(varText, "-")

Any suggestions? Sophia

 

[PHV]

If Not IsNull([!]varText[/!]) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Thanks. My mistake. If passing parameters to a function used called from a query I always pass them as variants and check if they are null, because it is common to have an empty record in a query.

 

[MajP]

Regardless, are you getting a compile error or runtime error? You should not get a compile error. Because of the typo you could get a runtime error since a null value would make that line fail.

 

[Dophia]

Thank you both.

I am not sure how to call the function since what I have tried only gives me the left part of the data. I changed the code to the name of the field which is "Chip_Tatoo_ID_Mark". In a query, with the data in a field of "crs12511-061513" it is returning "crs12511" . In the query is:

LeftPart: splitText([Chip_Tatoo_ID_Mark],1)

How do I get it to return the other two parts of the field?



Public Enum Chip_Tatoo_ID_Mark
LeftPart = 0
TheDate = 1
Extra = 2
End Enum

Public Function splitText(varText As Variant, ThePart As Variant) As String
Dim aParts() As String

If Not IsNull(varText) Then
aParts = Split(varText, "-")
If Chip_Tatoo_ID_Mark = LeftPart Then
splitText = aParts(LeftPart)
ElseIf Chip_Tatoo_ID_Mark = TheDate Then
splitText = Left(aParts(TheDate), 6)
ElseIf Chip_Tatoo_ID_Mark = Extra Then
splitText = aParts(TheDate)
If Len(splitText) > 6 Then
splitText = Right(splitText, Len(splitText) - 6)
Else
splitText = ""
End If
End If
End If
End Function

What am I doing wrong ? Sophia

 

[MajP]

If you are calling from SQL you will not be able to use the enumerated constants. So something like

Select [Chip_Tatoo_ID_Mark], splitText([Chip_Tatoo_ID_Mark],0) as LeftPart, splitText([Chip_Tatoo_ID_Mark],1) as DatePart, splitText([Chip_Tatoo_ID_Mark],2) as ExtraPart from YourTableName

 

[Dophia]

That still returns the only the left part of the text only.

Maybe I am missing something?

Sophia

 

[PHV]

Public Function splitText(varText As Variant, ThePart As [!]TheNameOfYourEnumHere[/!]) As String

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Try it without any enumeration. Pass 0 for left, 1 for date, 2 for the extra

Public Function splitText(varText As Variant, ThePart As integer) As Variant
  Dim aParts() As String
  If Not IsNull(varText) Then
    aParts = Split(varText, "-")
    If ThePart = 0 Then
      splitText = aParts(0)
    ElseIf ThePart = 1 Then
      splitText = Left(aParts(1), 6)
    ElseIf ThePart = 2 Then
      splitText = aParts(1)
      If Len(splitText) > 6 Then
        splitText = Right(splitText, Len(splitText) - 6)
      Else
        splitText = ""
      End If
    End If
  End If
End Function

 

[dhookom]

Dophia,

To debug/test any function you can open the immediate/debug window (press ctrl+g) and enter something like:

? splitText("ABC34433-0615131", 1)

You should be able to see a result after pressing enter. If the result is as expected, add a break point in your code at the first If statement. Then use the F8 key to step through each line. Mouse over any variable to check its current value.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Duane,
The function works fine in vba, I provided the test with the original code. It was not tested from SQL which does not allow the enumerated constants.

Public Sub testsplit()
  Dim theText As String
  theText = "ABC34433-0615131"
  Debug.Print splitText(theText, LeftPart)
  Debug.Print splitText(theText, TheDate)
  Debug.Print splitText(theText, Extra)
  theText = "ABC34433-061513"
  Debug.Print splitText(theText, LeftPart)
  Debug.Print splitText(theText, TheDate)
  Debug.Print splitText(theText, Extra)
End Sub

The issue is running a UDF with enumeration constants from SQL, and a mistype in checking for a passed null value. Unfortunately enumerated constants cannot be called from SQL. So you have to pass the constant as an integer.

 

[dhookom]

MajP,
I understand the constants don't work in the SQL statement. I was hoping Dophia could learn a little about how to debug/troubleshoot without having to rely so much on others.

Duane
Hook'D on Access
MS Access MVP

 

[strongm]

You don't actuallu appear to have copied MajP's code correctly. For example, it's not

[tt]If Chip_Tatoo_ID_Mark = LeftPart Then[/tt]

it's

[tt]If ThePart = LeftPart Then[/tt]

 

[Dophia]

Thank you for helping me from start to end MajP. It works perfectly!

I am not proficient in VBA at all, but I sometimes need to get something accomplished with Access, so I am grateful for this group and knowledgeable people who are here.

Contact me if you need tax or accounting advise? I can help you there!

Sophia