Solved Extract text from String ,Name and telephone number

[ypma]

More guidance is requested . I have a field in Select query solicitors, which produces the name of the solicitor and the telephone number .My requirement is to be able extract only the solicitors name . I have tried various combination using left function and is numeric to remove the numbers. The output format is always the same Watt Jones : 23473058. If more details require please advise .

Any assistance would be appreciated .

Regards Ypma

 

[plog]

I have a field in Select query solicitors, which produces the name of the solicitor and the telephone number

Why does the underlying table store your data like this? In a properly structured database every discrete piece of data goes into its own field, not jammed together.

Left() is only good if you know where the name ends. To get that you need another function: InSTr( https://www.techonthenet.com/access/functions/string/instr.php). Nest them and you have your desired data. Of course as said before, it shouldn't even be stored like this.

 

[Gasman]

No doubt the query combines both fields?

 

[ypma]

No doubt the query combines both fields?

Thank you Gasman, the field in the subject query solicitor, is produced from a combo box on the form, this combo box has a lookup table for its source .I fully except plog's comments , but this is the problem I am face with and hope you can tell if its possible to create another field in the query to only show the text ?

Regards Ypma

 

[Gasman]

You can create that field in the query as plog indicated using left() and Instr() functions.

I was envisaging your query to have SolName & ":" & SolTelNumber putting them into the one field for the query?

 

[MarkK]

Use VBA.Split(), which splits the string at the delimiter you specify, and return the parts in a variant array.

debug.print split("Watt Jones : 23473058", " : ")(0)

 

[ypma]

Use VBA.Split(), which splits the string at the delimiter you specify, and return the parts in a variant array.

debug.print split("Watt Jones : 23473058", " : ")(0)

Mark k, Is it something like this Legal: split("solictors", " : ")(0) ? as I am not sure where to insert your solution .

Regards Ypma

Note, I am not a programmer ,but dip my feet in occasionally

 

[CJ_London]

you can't use the the split function in a query as you appear to be trying to do. You need to write a function in a general module - something like

function splitstr(s as string, delim as string, pos as integer) as string
   
    splitstr=split(s,delim)(pos)
    'edit you might want to remove extra spaces in which case use trim(split(s,delim)(pos))
end function

and in your query

legal:splitstr([solicitors],":",0)

 

[ypma]

you can't use the the split function in a query as you appear to be trying to do. You need to write a function in a general module - something like

function splitstr(s as string, delim as string, pos as integer) as string
 
    splitstr=split(s,delim)(pos)
    'edit you might want to remove extra spaces in which case use trim(split(s,delim)(pos))
end function

and in your query

legal:splitstr([solicitors],":",0)

Thank you CJ_London. I will implement you suggestion tomorrow, as I need my beauty sleep.

Regards Ypma

 

[Galaxiom]

An alternative technique would be to use the following expressions in your query. No need for a custom function then.

SolicitorName: Trim(Left(fieldname, Instr(fieldname, ":") - 1))
Phone: Trim(Mid(fieldname, Instr(fieldname, ":") + 1, 20))

The third argument on Mid, (in this case, 20), needs to be arbitrarily large enough to capture what is after the colon.

By the way if you want to look for the last occurrence of a character to break up the string, then investigate the use of InstrRev().

Generally speaking, the performance of a user defined function will be inferior to using native SQL functions because the engine doesn't have to call out to the VBA project for the SQL function.

Although many of the functions in SQL share the same names as VBA functions, the engine doesn't actually use the VBA function but rather its own resource.

 

[arnelgp]

Although many of the functions in SQL share the same names as VBA functions, the engine doesn't actually use the VBA function but rather its own resource.

Trim, Mid, Left, Instr is from VBA namespace. so it is calling a function.
the only function in SQL are Min, Max, First, Last?

 

[ypma]

An alternative technique would be to use the following expressions in your query. No need for a custom function then.

SolicitorName: Trim(Left(fieldname, Instr(fieldname, ":") - 1))
Phone: Trim(Mid(fieldname, Instr(fieldname, ":") + 1, 20))

The third argument on Mid, (in this case, 20), needs to be arbitrarily large enough to capture what is after the colon.

By the way if you want to look for the last occurrence of a character to break up the string, then investigate the use of InstrRev().

Generally speaking, the performance of a user defined function will be inferior to using native SQL functions because the engine doesn't have to call out to the VBA project for the SQL function.

Although many of the functions in SQL share the same names as VBA functions, the engine doesn't actually use the VBA function but rather its own resource.

Thank you Galaxiom: The SolicitorName expression you produced gave me exact result I needed . I have learnt something new , I intend to see if i can get CJ_London functions to work, to add to my knowledge , They say " a little knowledge is dangerous" !!

 

[ypma]

CJ_London . Thanks you for the function , It produced the correct result that I was after and I will retain it for further reference .

Regards Ypma

 

[Galaxiom]

Trim, Mid, Left, Instr is from VBA namespace. so it is calling a function.
the only function in SQL are Min, Max, First, Last?

No. The names of the functions are the same in VBA and JET/ACE database engines but they are different objects. A query uses the engine functions by preference and they are far more efficient.

This is most evident when connecting to an Access database via ODBC which connects to the engine, not the project. A well known case is the Nz() function which is in VBA but not the engine and doesn't work properly from external connections where Iif() is typically used instead.

This page lists the functions in Jet/ACE. Notice that Nz() is not among them.

 

[arnelgp]

"It is important to note that there are 2 type of functions in Access - functions used in SQL/Queries and functions used in VBA code. Some functions can be used as both, but some can not."

?which one?

 

[Galaxiom]

"It is important to note that there are 2 type of functions in Access - functions used in SQL/Queries and functions used in VBA code. Some functions can be used as both, but some can not."

Quoting someone who doesn't understand the difference between the engine and the application isn't helpful.

There are two types of functions. Jet/ACE database engine functions and VBA functions. Many of the have the same names.

 

[arnelgp]

Quoting someone who doesn't understand the difference between the engine and the application isn't helpfu

the quote comes from the page you post.
no further question, you have answered them all.

 

[Galaxiom]

no further question, you have answered them all.

I can see the page I linked was not ideal. Sorry about that.

Anyway I hope you now understand that the database engine functions are not the VBA functions.

 

[CJ_London]

Notice that Nz() is not among them.

it is there in the advanced/logical functions

just discovered that switch and choose also work in vba which might take away a few case statements.

 

[Galaxiom]

it is there in the advanced/logical functions

That was another thing I took on face value from the post on another site where I found the link. I suspect the list was expanded after that post was written.

I do know for sure that Nz() misbehaves if the database query using it is connected from Excel.