UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Split Function Missing In Access 2010?, Access 2010    
 
   
NesY
post Aug 7 2017, 07:33 AM
Post#1



Posts: 12
Joined: 16-February 17



Hi, can someone please help with finding the Split function in Access 2010? I cannot find it in my built-in functions list, category Array (see attached picture). Alternatively, a VBA code for writing a UDF for that function will be much appreciated too.
Attached File(s)
Attached File  Access_2010_Expression_Builder_Array_Functions.png ( 25.06K )Number of downloads: 1
Attached File  Access_2010_Expression_Builder_Array_Functions.png ( 25.06K )Number of downloads: 0
 
Go to the top of the page
 
moke123
post Aug 7 2017, 07:49 AM
Post#2



Posts: 1,045
Joined: 26-December 12
From: Western Ma., USA


i dont believe you'll find it in the expression builder buy if you go to the vbe , and type split, intellisense should bring it up for you.
Go to the top of the page
 
GroverParkGeorge
post Aug 7 2017, 08:10 AM
Post#3


UA Admin
Posts: 30,172
Joined: 20-June 02
From: Newcastle, WA


Correct.

Split() is a VBA-only function, not an expression you would use in a query or calculated control on a form.

The database engine in Access (it used to use JET, now ACE) contains the Expression Service which handles expressions, as opposed to VBA, which handles its own functions. Some are similarly named, but don't necessarily work exactly the same, e.g Iif().

I agree it's sort of murky, though, because we would use some "functions" in queries, e.g. Date() or Format(). And they are, indeed, available in the Expression Builder.

Here's Allen Browne's thoughtful commentary on expressions that are native to SQL vs VBA calls to functions.

It's worth a few hours of reading if you intend to use VBA and SQL effectively.





--------------------
Go to the top of the page
 
doctor9
post Aug 7 2017, 08:45 AM
Post#4


UtterAccess Editor
Posts: 17,431
Joined: 29-March 05
From: Wisconsin


NesY,

If you described what you were trying to accomplish, we might be able to give you some help. I'm assuming you want to do something with the Merchant Contact Name.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
NesY
post Aug 7 2017, 09:15 AM
Post#5



Posts: 12
Joined: 16-February 17



OK, I have a field [Merchant Contact Name], that is a person's name, a string of sometimes 2, sometimes 3 words (First, eventually Middle, Family). I am trying to split the string into distinct words and then write a function in a query that would return only the Family name back. The InStrRev function does not work correctly, which is why I turned to Split for possible help.
Go to the top of the page
 
doctor9
post Aug 7 2017, 09:30 AM
Post#6


UtterAccess Editor
Posts: 17,431
Joined: 29-March 05
From: Wisconsin


NesY,

Okay, so you just want the last name. How is the full name formatted? Is it just like "Maria Beth Van Den Loon"? Or is it "Van Den Loon, Maria Beth"? Perhaps you can already guess why I'm asking; last names are not always a single "word" which makes parsing them out very difficult. If you just want to grab the last word, a simple UDF would be:

CODE
Public Function LastName(ByVal strFullName As String) As String

    If InStrRev(strFullName, " ") = 0 Then
        Exit Function
    Else
        LastName = Mid(strFullName, InStrRev(strFullName, " ") + 1)
    End If

End Function

In your query, you'd have an expression that does something like this:

FamilyName: LastName([Merchant Contact Name])

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
NesY
post Aug 7 2017, 09:50 AM
Post#7



Posts: 12
Joined: 16-February 17



Thanks for the detailed code Dennis. Copied it in a module and got the new function, then run the query, but it stoped with message Undefined function FamilyName... Any idea?
Go to the top of the page
 
GroverParkGeorge
post Aug 7 2017, 09:53 AM
Post#8


UA Admin
Posts: 30,172
Joined: 20-June 02
From: Newcastle, WA


One solution to this problem COULD involve Split() in Dennis' function, in which you create an array containing each "component" of the name. If there are only first and family names, the array would have two members, three if there are first, middle and last names, and so on.

The real problem is, as Dennis pointed out, that you simply can't count on ANY function handling all possible names. Therefore, my suggestion would be to modify the table itself, do the parsing of names one time to get them all sorted out, and not bother with this problem in the future.

In other words, instead of "[Merchant Contact Name]" you should have MerchantContactFirstName, MerchantContactMiddleName, and MerchantContactLastName fields.

If you need to import new merchant records in bulk, you may have to repeat the clean up process each time, but that's probably better than struggling with inappropriately defined fields.


--------------------
Go to the top of the page
 
GroverParkGeorge
post Aug 7 2017, 09:58 AM
Post#9


UA Admin
Posts: 30,172
Joined: 20-June 02
From: Newcastle, WA


You might benefit from some study of the Rules of Normalization, which are the basic principles underlying all good table design.

For example, your current table violates the first rule of normalization:

"1NF is the most basic of normal forms - each field in a table must contain only one piece of information, and there can be no duplicate rows."

In this case, "FirstName" is one piece of information, "MiddleName" is another piece of information, and "LastName" is yet another. Three pieces of information, three fields.

--------------------
Go to the top of the page
 
NesY
post Aug 7 2017, 10:02 AM
Post#10



Posts: 12
Joined: 16-February 17



Right of course, but the table is not done by me, I just have to use it, so all names are in the same column..
Go to the top of the page
 
GroverParkGeorge
post Aug 7 2017, 10:28 AM
Post#11


UA Admin
Posts: 30,172
Joined: 20-June 02
From: Newcastle, WA


You have to use it "as is" for some external reason, and can't normalize it, then?

Unfortunately, that does happen all too often.

--------------------
Go to the top of the page
 
kfield7
post Aug 7 2017, 10:41 AM
Post#12



Posts: 726
Joined: 12-November 03
From: Conroe, TX


Parsing out a name, as discussed, can be quite complicated.
When we do this in our head, there are several rules that we apply without thinking too much about it.
Computers are very fast, but they must have rules.

That said, if you want the computer to do all the "work", you have to give it as full of a set of rules as you can conceive would be applicable.

Something like,
* If there's a comma, it must be Last, First....
* If there's more than two "words" (defined as separated by spaces and/or a comma) it must be First Middle Last unless there's a comma then it could be Last1 Last2, First; unless it's really first Last1 Last2 (how does the computer know? How do YOU know? you assume something based on your experience with names, a subjective database very difficult to implement on the computer -- accept that sometimes it will be wrong).
* If there's more than three "words" then the last name must have #words-2 words
* If there's more than 3 "words" are there multiple middle names or a two-part last name? or both?

All subject to your rules of interpretation when you see the name.

So, write out all your rules that you can think of, then code the function in VBA according to your rules.
Go to the top of the page
 
datAdrenaline
post Aug 7 2017, 10:46 AM
Post#13


UtterAccess Editor
Posts: 17,923
Joined: 4-December 03
From: Northern Virginia, USA


>> but it stoped with message Undefined function FamilyName... <<

- Is the function named FamilyName() in a Module named FamilyName? If so, rename the Module to something like mod_FamilyName
- Is the function in a Standard Module object ... NOT a module bound to a Form or Report? If the hosting module is not a stand alone (Standard) Module object, then create a Module and add the function to the Standard Module.
- Is the function declared as Public?

--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
doctor9
post Aug 7 2017, 10:49 AM
Post#14


UtterAccess Editor
Posts: 17,431
Joined: 29-March 05
From: Wisconsin


NesY,

> but it stoped with message Undefined function FamilyName

What I provided is what you would put in the Field row of a new column if you were looking at your query in the Design Grid view. If you just need an SQL expression, it would be:

LastName([Merchant Contact Name]) AS FamilyName

If you're still getting an error, please post your query's SQL code and maybe we'll spot the problem.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
datAdrenaline
post Aug 7 2017, 11:01 AM
Post#15


UtterAccess Editor
Posts: 17,923
Joined: 4-December 03
From: Northern Virginia, USA


Also, if you are getting all the text after the last space in the field, then you really don't need VBA. You can still use an expression that is inherent to the db engine ...


Mid([Merchant Contact Name], IIf([Merchant Contact Name] LIKE "* *", InStrREV([Merchant Contact Name] & "", " ") + 1, Len([Merchant Contact Name] & "") + 1))

--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
doctor9
post Aug 7 2017, 12:12 PM
Post#16


UtterAccess Editor
Posts: 17,431
Joined: 29-March 05
From: Wisconsin


Brent,

Well spotted - I assumed that the InStrRev() function was also not available for some reason, but you're right, it should work just fine. Except, of course, when the last name is more than one word long.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
NesY
post Aug 8 2017, 01:33 AM
Post#17



Posts: 12
Joined: 16-February 17



Brent and Dennis,
Many thanks for your support on this issue! Both ways work - the VBA of Dennis (after renaming the Module name to something else the the UDF name) and the expression of Brent. My innitial try was to use a combination of InStrRev (to get the number of symbols before the first space character as delimiter) and the Right function to get that number of characters from the right side of the string, but for some reason the InstRev function gave incorrect results. Anyways, great support, thank again.
NesY
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th August 2017 - 10:42 AM