split field data to 3 fields

  • Thread starter Thread starter Paul Mars
  • Start date Start date
P

Paul Mars

I was given a list of data that needs to be entered into a database, but
first

One list field has data as:
xxx
xxx, xxx
xxx xxx
xxx xxx xxx

xxx represents one or more letters. I need to make 3 separate fields and
drop the commas.

Please help me,
Paul
 
Why do they still call it Help? Neither the Help index or answer wizard
found Instr.

I also found no Help results on Left or Right, which I remember as possible
solutions.

Help sucks. Sorry for the vent. More ng help please.

Paul
 
Why do they still call it Help? Neither the Help index or answer wizard
found Instr.

I also found no Help results on Left or Right, which I remember as possible
solutions.

Help sucks. Sorry for the vent. More ng help please.

Paul
Click to expand...
** snipped **

You're looking in the wrong place.
Look in VBA Help.

From any code window, or from the debug.window, click on help.
Now search for the function InStr, Left, Mid, etc.

An alternative way is to open a code window, type the function name
and press F1.
 
Paul said:
Why do they still call it Help? Neither the Help index or answer wizard
found Instr.

I also found no Help results on Left or Right, which I remember as possible
solutions.

Help sucks. Sorry for the vent. More ng help please.
Click to expand...

No need to apologize. Since the Index was fumbled, Help is
just asking to be vented upon ;-)

First make sure you're using VBA Help (in the VB IDE
Window). Then select the Contents tab, VBA Language
Reference and Functions.

The code could be something along the lines of this air
code:

posa = Instr(Me.field, " ")
If posa = 0 Then
part1 = Me.field
part2 = Null
part3 = Null
Else
part1 = Left(Me.field, posa-1)
posb = InStr(posa+1, Me.field, " ")
If posb = 0 Then
part2 = Mid(Me.field, posa+1)
part3 = Null
Else
part2 = Mid(Me.field, posa+1, posb - posa -1)
Part3 = Mid(Me.field, posb+1)
End If
End If
 
Much thanks to all.

P

Marshall Barton said:
No need to apologize. Since the Index was fumbled, Help is
just asking to be vented upon ;-)

First make sure you're using VBA Help (in the VB IDE
Window). Then select the Contents tab, VBA Language
Reference and Functions.

The code could be something along the lines of this air
code:

posa = Instr(Me.field, " ")
If posa = 0 Then
part1 = Me.field
part2 = Null
part3 = Null
Else
part1 = Left(Me.field, posa-1)
posb = InStr(posa+1, Me.field, " ")
If posb = 0 Then
part2 = Mid(Me.field, posa+1)
part3 = Null
Else
part2 = Mid(Me.field, posa+1, posb - posa -1)
Part3 = Mid(Me.field, posb+1)
End If
End If
Click to expand...
 
Back
Top