If the field is Null then test for that by changing the function or using an
IIF statement in the query
Field: IIF([SomeField] is Null,Null, F2([SomeField]))
or in the function
Function F2(TextIn)
Dim tStr() as Variant
If Len(TextIN & "") = 0 Then
F2 = Null
Else
tStr = Split(TextIn,".")
IF Ubound(tStr)<1 Then
F2 = ""
Else
F2 = tStr(1)
End If
End If
End Function
Actually, here is a more generic function that I've written. You use it by
telling it the string, the delimiter, and the section number
getSection([SomeField],".",1)
getSection([SomeField],".",2)
getSection([SomeField],".",3)
Public Function getSection(strIn, _
Optional strDelimiter As String = ";", _
Optional intSectionNumber As Integer = 1)
'==============================================================
' Procedure : getSection
' Author : John Spencer
' Purpose : Return section of string
' Access 2000 and later
'==========================================
Dim strArray As Variant
If Len(strIn & vbNullString) = 0 Then
getSection = strIn
Else
strArray = Split(strIn, strDelimiter, -1, vbTextCompare)
If UBound(strArray) >= intSectionNumber - 1 Then
getSection = strArray(intSectionNumber - 1)
Else
getSection = Null
End If
End If
End Function
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
MacNut2004 said:
This works great for the second half if it is blank....but what if the
entire
field is blank? (the upper portion as well as the second?)
Thank you!!
John Spencer said:
Test the size of the array returned by the split function to make sure
there
are enough elements to return a value
Function F2(TextIn As String) As String
Dim tStr() as Variant
tStr = Split(TextIn,".")
IF Ubound(tStr)<1 Then
F2 = ""
Else
F2 = tStr(1)
End If
End Function
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Hello!
I found this post VERY helpful -- thank you very much! Only question I
have
is this. This is what some of my data looks like where I need to use
the
split function:
511100.001
511200
540000.003
and so on. As you notice...some of the data will NOT have numbers
after a
period...and so I am getting an error in my code that says "run-time
error
9.
Subscript Out of Range"
Any ideas of how to get around this? I would like the second field of
the
split function to appear as null if there are no numbers after the
period.
Thank you very much in advance!!
MN
:
Gee: didn't work for me either.
Create some functions that you can call:
Function F1(TextIn As String) As String
F1 = Split(TextIn, "-")(0)
End Function
Function F2(TextIn As String) As String
F2 = Split(TextIn, "-")(1)
End Function
and so on, then use CR1: F1([CR]), CR2: F2([CR]) and so on.
Sorry about that!
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
Doug,
I tried entering this command into a new query column as CR1:
Split([CR],
"-")(0) and it gave me 'the expression you entered contains an
invalid
.
(dot), ! operator or invalid parenthesis'. If I remove the (0), it
gives
me
an 'undefined function split in expression'
Any thoughts?
:
Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so
on.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
I guess I'm looking for the actual string command in an Access
Query
that
allows me to do just that. You are right, I do not need to store
all 5
columns. Just the new ones. I am importing Excel reports from
our
supplier every day (45-75 line items) that has the field that
needs
to
be
separated and I don't want to do a lot of labor in Excel before
bringing
the
reformatted data into my database.
:
You would build an update query and put the values into new
fields
using
the
update query (assuming this is a one-time thing). You DO NOT
want
to
store
all five fields. Create the new four fields, update them, then
delete
the
combined field.
Storing both would be redundant and would lead to problems.
Users
would
need to be careful to update all the fields. Also, it is much
more
efficient to string them together in a query, report, or form if
you
need
them in that format rather than have Access go do disk reads.
If you need more details, post back.
--
Rick B
message
Does Access have a Text-To-Columns function like Excel?
I have a field in access that contains AAA-BBBB-CCC-DDDD or
AAAA-BBB-CCCC-DDD. I need to be able to split this data into
4
new
fields
like Text-To-Columns in Excel. Please advise. THANKS!
