splitting data into different fields

  • Thread starter Thread starter bird lover
  • Start date Start date
B

bird lover

Working in Access 2003. No knowledge of basic, sql, or any programming
language.

Have data in [indexno] as follows:
Qn 2008 / 6610
Qn 2007 / 15578
Bx 2005 / 20280
Bx 2007 / 7764
A space always surrounds the /. A space is always before the location and
year.

What is query syntax to update info into following fields:
[indexcounty],[indexyear][indexfile] so it looks like this
[indexcounty] [indexyear] [indexfile]
Qn 2008 6610
Bx 2005 7764
Thanks for any help.
 
STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Paste the function below into a VBA module and save the module with a name
such as mod_UtilityFunctions

Then use a query that looks like the following to update your table
UPDATE [YourTable]
SET IndexCounty = fGetToken([IndexNo]," ",1)
, IndexYear = fGetToken([IndexNo]," ",2)
, IndexFile = fGetToken([IndexNo]," ",4)
WHERE IndexNo is not Null

'================== CODE Begins =====================
Public Function fGetToken(strIn, _
Optional strDelimiter As String = " ", _
Optional LPos As Long = 1)
'Return the Nth item from a delimited list of items.

Dim strArr As Variant

If Len(strIn & "") = 0 Then
fGetToken = strIn
Else
strArr = Split(strIn, strDelimiter)
If LPos - 1 <= UBound(strArr) Then
fGetToken = strArr(LPos - 1)
Else
fGetToken = Null
End If
End If

End Function
'================= CODE ENDS =======================

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top