Parsing Text Fields

[Tom]

Hi all,

I have a couple of problems. The first one is I want to
parse a character from a text field in a query. The field
can have different lengths, but it is always the furthest
right character I wish to remove, e.g.:

19000|

I want to remover the "|" in the field to just display
19000. Secondly, I have a large text field in a table
that I wish to split into a number of fields. Again, this
field can have varying lengths, and I wish to split it by
word. e.g:

Ford Orion 1.6 Ghia
Ford Escort
Ford Ka Deluxe Automatic 1.3 3dr

This is an example of the kind of data I am working with,
and I really just want to copy the Excel Text to Coloumn
function in Access. I have been told to use the VB Split
function, but I have limited VBA knowledge so would
appreciate any help given!

Cheers.

 

[Chris Nebinger]

First one:

Left(FieldName,Len(fieldname)-1)

The second one:

I'm not sure what you want to do. The split function will
put each word in an array.

dim values() as string
Values=Split(strField," ")

Values(1) has the first word, values(2) has the second,
etc.

What do you want to do with the results?


Chris Nebinger

 

[Tom]

Hi Chris,

Thanks for your help here. On the second one, I want to
split the one field into numerous, but putting each word
as a seperate coloumn in the table/query.

E.g. I want to split the following field :

Vauxhall Astra Ex

into three fields, Vauxhall in the first, Astra in the
Second and EX in the third. However, if the original field
contains Vauxhall Astra Ex 1.6. I need to split this field
into three, the first containing Vauxhall, the second
containg Astra and then the third containing Ex 1.6. Is
this possible?

Once again, thanks for your help!!

 

[Chris Nebinger]

The easiest way to do this is to use a function like:

Paste this code into a standard module.

Public Function SplitWords(Words, intWord As Integer)
If IsNull(Words) Then Exit Function
Dim Values() As String
Dim intCounter As Integer
'Values will be 0 based
Values = Split(Words, " ")
If intWord < 3 Then
SplitWords = Values(intWord - 1)
Else
SplitWords = Mid(Words, InStr(Words, Values(2)))
End If
End Function


Use it in a query like:
FirstWord:SplitWords([FieldName],1)
SecondWord:SplitWords([FieldName],2)
EndingWords:SplitWords([FieldName,3)


Chris Nebinger

 

[Guest]

Hi Chris,

Thanks for your help here. However, I have pasted the
code into a module, then named the module SplitWords. I
am then trying to build a query, by creating fields as you
have suggested below e.g. FirstWord: SplitWords
([Fieldname],1).

When I try to run the query I am getting an error message
saying "Undefined function 'SplitWords' in Expression".

What am I doing wrong????

-----Original Message-----
The easiest way to do this is to use a function like:

Paste this code into a standard module.

Public Function SplitWords(Words, intWord As Integer)
If IsNull(Words) Then Exit Function
Dim Values() As String
Dim intCounter As Integer
'Values will be 0 based
Values = Split(Words, " ")
If intWord < 3 Then
SplitWords = Values(intWord - 1)
Else
SplitWords = Mid(Words, InStr(Words, Values(2)))
End If
End Function


Use it in a query like:
FirstWord:SplitWords([FieldName],1)
SecondWord:SplitWords([FieldName],2)
EndingWords:SplitWords([FieldName,3)


Chris Nebinger

-----Original Message-----
Hi Chris,

Thanks for your help here. On the second one, I want to
split the one field into numerous, but putting each word
as a seperate coloumn in the table/query.

E.g. I want to split the following field :

Vauxhall Astra Ex

into three fields, Vauxhall in the first, Astra in the
Second and EX in the third. However, if the original field
contains Vauxhall Astra Ex 1.6. I need to split this field
into three, the first containing Vauxhall, the second
containg Astra and then the third containing Ex 1.6. Is
this possible?

Once again, thanks for your help!!

.

.

 

[Dirk Goldgar]

Hi Chris,

Thanks for your help here. However, I have pasted the
code into a module, then named the module SplitWords. I
am then trying to build a query, by creating fields as you
have suggested below e.g. FirstWord: SplitWords
([Fieldname],1).

When I try to run the query I am getting an error message
saying "Undefined function 'SplitWords' in Expression".

What am I doing wrong????

Change the name of the module (not the function in the module) to
something else; maybe, "basSplitWords". The module can't have the same
name as the procedures defined within it.

 

[TOM]

Brilliant thanks for your help!! That worked a treat.

-----Original Message-----

Hi Chris,

Thanks for your help here. However, I have pasted the
code into a module, then named the module SplitWords. I
am then trying to build a query, by creating fields as you
have suggested below e.g. FirstWord: SplitWords
([Fieldname],1).

When I try to run the query I am getting an error message
saying "Undefined function 'SplitWords' in Expression".

What am I doing wrong????

Change the name of the module (not the function in the module) to
something else; maybe, "basSplitWords". The module can't have the same
name as the procedures defined within it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.