FIELD SPLITTER

[Rich_Lovina]

This should be easy, but Ive had a disk crash and am awaiting holidays to end so people all get back to work in Australia and I can then get my disk repaired....

I have a field with A&B [Initials] and I want to split out B into separate field 2ND INIT.
I did have a lovely Module which did all this which I ran in a query and created a new table from the module. I have found the following in past postings:

Public Sub SplitString(strToSplit as String)
Dim intFirstSpace As Integer
Dim FirstName, LastName As String
intFirstSpace = InStr(1,Str," ")
FirstName = Mid(Str,1,intFirstSpace-1)
LastName = Mid(Str, intFirstSpace+1, Len(Str))
End Sub

...but this (I think) only splits by the space. All my fields have the & in the middle of the INITIALS with NO spaces.

Hopefully someone kind (Are you out there Jack or JWindon?) as I've also temporarily lost my Outlook Express also, so my direct email is down and out too...
Thanks in advance anyone

 

[Jack Cowley]

Rich -

This should fix it for you:

intFirstSpace = InStr(1, Str, "&")

Jack

 

[Rich_Lovina]

Thanks greatly, Jack from the VB Dummy. I'd done that whilst waiting to hear from someone, and whilst I'm downloading Outlook Express 5.03 (to fix the email problem).
Now I forgot how to call up the module 1 in the query. Is it Field(Initials must have two new names (say InitsA and InitsB), then
a)Does intfirstspace go in the ExpressionBuilder box of the query, and
b)my equivalents for firstname & lastname go in the two field cells?
Or does strToSplit go in expression builder?

 

[Rich_Lovina]

Is anyone out there who can help me call up a module to create the two new fields INITS_A and INITS_B?
I'm new at VB and after mastering code for a form, Ive forgotten what to put where to bring up the code in a MAKE TABLE query.
Basically, in the field cell what do I write, in the query field INITIALS which I'm splitting do I put down anything for the criteria?
Thanks in advance.

 

[Jack Cowley]

I have sent you a small demo.

 

[Rich_Lovina]

Thanks very much Jack. Exactly what I need, and Outlook Express now receives but can't send, hence this note. I will study that layout carefully. Also most of the code I've temp. lost is available here in the Forum. My tech had an IBM HD go too, so I suspect dual HDs (CD's too) for perm.backup best.
Its a great search engine in emergency dataloss situations....other Forum users might note!

 

[Rich_Lovina]

Jack,I'm getting a "Run Time Error 5" and I believe it must relate to fieldname INITIALS. I pasted my 150k table into yr query design (easiest way with a temp 1.5 gig HD), but its not clear where the code problem lies.
Why would it work with five record sample db yet not with 130k recs. Its showing error# when trying to run MAKE TABLE.
Any ideas????Tks

 

[Rich_Lovina]

Jack, pls ignore last, as have found problem. Had only 40k recs with the nasty & and balance without. Code only worked with those 'with &' so had to modify a little.
Thanks greatly, result popped up instantly when corrected. Two big Queensland 4 XXXX beers for you, and Happy New Year when you read this.