help me in , separate a text to several columns ?

[RuralGuy]

So you only need records where the RepairWelders field has a value, correct?

[evilleve]

just for repair welders yes, but in other fields no

(did you see the sheet? there is a field with name "WELD NO"
when a welder weld a joint , after that we check the weld is correct and dont have a defect (ndt) all of these have calculations . repairing is the last step)

[RuralGuy]

We need to concentrate on one issue at a time.

[evilleve]

ok , now we know we cant separate columns to rows and if we separate columns in this way we will have many many columns ....

[RuralGuy]

Columns *can* be separated into rows if that is what you decide to do.

[evilleve]

if they be divided to rows i cant use them

[RuralGuy]

Why not? You lost me there.

[evilleve]

thanks for replying me .
because in this way the rows become x2 and so more
we dont need just RepairWelder field and some fields have relations with RepairWelder
so its not the solution

[evilleve]

i used this as a function :

Public Function MySplit(REPAIRWELDERS As String, MyDelim As String, InOffset As Integer) As String
Dim MyArray() As String
MyDelim = "("
InOffset = 1
MyArray = Split(REPAIRWELDERS, MyDelim)
MySplit = MyArray(InOffset)
End Function

it works until the records has a "(" , when the record is null or dont have"(" it says :

Run-time error '9'
subscript out of range

whats the problem ?

[RuralGuy]

I did not put any error checking in the function. Let's start by properly using the Function. Show me how you invoke the function in your query. Maybe posting the SQL view of the query will be enough.

[evilleve]

s: mysplit([REPAIR WELDERS];"(";1))

[RuralGuy]

So you are completing a field in your query named "s" correct. I'm not sure if a semi-colon works as a parameter separator. I use a comma:
s: mysplit([REPAIR WELDERS], "(", 1)
...and we'll add some error checking to the Function in a bit.

[RuralGuy]

And here's a new function to use that has some error checking built in:

Public Function MySplit(InString As String, Delim As String, InOffset As Integer) As String
   '-- Invoked this function by passing in:
   '-- InString - The string to separate
   '-- Delim - The separating delimiter
   '-- InOffset - The nth value in the InString to return
   Dim MyArray() As String
   If (Len(InString & "") > 0) And (Len(Delim & "") > 0) And (InOffset > 0) Then
      '-- There are values to work with, go ahead and separate the string
      MyArray = Split(InString, MyDelim)
      If LBound(MyArray) = 0 Then
         '-- Adjust the requested parameter for zero base request
         InOffset = InOffset - 1
      End If
      If InOffset > UBound(MyArray) Then
         MsgBox "Requesting offset above available values", vbCritical + vbOKOnly
         MySplit = ""   '-- Return a Zero Length String (ZLS)
      Else
         '-- Return the value requested
         MySplit = MyArray(InOffset)
      End If
   Else
      MsgBox "Incomplete arguments to MySplit Function", vbCritical + vbOKOnly
      MySplit = ""   '-- Return a Zero Length String (ZLS)
   End If
End Function

[evilleve]

thanks .

i put this as function :

Public Function MySplit(REPAIRWELDERS As String, Delim As String, InOffset As Integer) As String

Dim MyArray() As String

If (Len(REPAIRWELDERS & "") > 0) And (Len(Delim & "") > 0) And (InOffset > 0) Then
MyArray = Split(REPAIRWELDERS, MyDelim)
If LBound(MyArray) = 0 Then
InOffset = InOffset - 1
End If
If InOffset > UBound(MyArray) Then
MsgBox "Requesting offset above available values", vbCritical + vbOKOnly
MySplit = ""
Else
MySplit = MyArray(InOffset)
End If
Else
MsgBox "Incomplete arguments to MySplit Function", vbCritical + vbOKOnly
MySplit = ""
End If
End Function

and this in query :

s: mysplit([REPAIR WELDERS];"(";1)

but when i run the query , in blank values an error happens :

[RuralGuy]

Have you tried single stepping the code to see what is happening? Put a breakpoint at the 1st If (just click outside of the margin and a DOT will appear) and F8 through the code and see what is happening.