On Aug 20, 5:51*pm, "Stuart McCall" <smcc...@myunrealbox.com> wrote:
> "Gina" <gors...@hotmail.com> wrote in message
>
> news:7814bd19-c0bb-4493-b723-(E-Mail Removed)...
> On 20 Aug, 10:03, "Stuart McCall" <smcc...@myunrealbox.com> wrote:
>
>
>
>
>
> > "Gina" <gors...@hotmail.com> wrote in message
>
> >news:d3df79bc-dcfb-4c8e-8a51-(E-Mail Removed)....
>
> > >I have to parse some data (8000+ records). One field which is giving
> > > me a particular headache is in the following format:
>
> > > The quick brown fox[space][space][space]Star Wars[space][space]
> > > [space]00:03:20
>
> > > where [space] represents a space.
>
> > > This field needs to be split into 3 fields:
>
> > > The quick brown fox
> > > Star Wars
> > > 00:03:20
>
> > > The first 2 fields are of varying lengths, the third field is always
> > > fixed in length. There is always 1 space between the words in the
> > > first 2 fields and always 3 spaces separating the fields.
>
> > > Can anyone suggest how to do this?
>
> > > Thanks
>
> > > Gina
>
> > Dim a As Variant
> > Dim itm As Variant
>
> > a = Split(FieldData, " ") 'That's 3 spaces between the quotes
> > For Each itm In a
> > Debug.Print itm
> > Next
>
> > will produce in the immediate window:
>
> > The quick brown fox
> > Star Wars
> > 00:03:20- Hide quoted text -
>
> > - Show quoted text -
> > Hey Stuart,
>
> > Thanks for the speedy response! *I can't check that right now as the
> > datafile is not to hand but I can see how that would work. *How can I
> > populate the 3 fields, Field1, Field2, Field3. For example could I use
> > the split function in an update query? *What would be the syntax for
> > that?
>
> > Thanks
>
> > Gina
>
> Hi Gina
>
> Sorry for the delay replying. I think I'd go for DAO to do the job. I'll
> assume that each row's data is in a variable called FieldData (one row ata
> time):
>
> Dim rs As DAO.Recordset
> Dim a As Variant
>
> Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)
>
> 'The following would be executed in some kind of loop
> 'to fetch each row of data into FieldData
>
> a = Split(FieldData, " * ")
> rs.AddNew
> rs!Field1 = a(0)
> rs!Field2 = a(1)
> rs!Field3 = a(2)
> rs.Update
>
> 'Go round the loop again, till finished
>
> rs.Close
> Set rs = Nothing
>
> The above is UNTESTED air code.
>
> Hope it helps.- Hide quoted text -
>
> - Show quoted text -
Thanks Stuart,
I get the idea. I can make that work.
Gina
|
Similar Threads
