[AccessD] clear x characters...

Stuart McLachlan stuart at lexacorp.com.pg
Sun Feb 16 08:08:14 CST 2020


I did say it was aircode. You should have been able to work out a solution based on the 
concept.  :)

These work.

(I had to include the TRIM$() because your sample data is inconsistent in leading spaces)


Function Test() As Long
Dim strData As String
'strData = "<font size=1>ANGRY BIRDS  2<br> Thurop Van Orman, John Rice (USA, 
Finland)<br>5/9/2019 - Spentzos Film</font>"
strData = "<font size=1> THE FAVORITE<br>Giorgos Lanthimos (USA, UK, Irland 
)<br>5/9/2019 - Feelgood Ent</font>"

Debug.Print Trim$(Mid$(strData, 14, InStr(strData, "<br>") - 14))

Debug.Print Trim$(Mid$(strData, InStr(strData, "<br>") + 4, InStr(strData, "(") - InStr(strData, 
"<br>") - 4))

Debug.Print Mid$(strData, InStr(strData, "("), InStr(strData, ")") - InStr(strData, "(") + 1)

End Function

On 16 Feb 2020 at 14:03, Kostas Konstantinidis wrote:

> Hi Stuart,
> The first part works perfect but not the other two... response with
> just error#
> 
> Thank's
> /kostas
> 
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of
> Stuart McLachlan Sent: Friday, February 14, 2020 5:51 AM To: Access
> Developers discussion and problem solving
> <accessd at databaseadvisors.com> Subject: Re: [AccessD] clear x
> characters...
> 
> Aircode, but it should be close:
> 
> Title = Mid$(strData,14, Instr(strData,"<br>") -14) Director =
> Mid$(instr(strData,"<br>" + 5),instr(strData,"(") -
> instr(strData,"<br>" + 5)) ProdCountries = Mid$(Instr(strData,"(") ,
> Instr(strdata(")") - Instr(strData,"(") )
> 
> 
> 
> On 12 Feb 2020 at 18:29, Kostas Konstantinidis wrote:
> 
> > Dear people
> > I import a lot of records from a mysql table which are like:
> > <font size=1>ANGRY BIRDS  2<br> Thurop Van Orman, John Rice (USA,
> > Finland)<br>5/9/2019 - Spentzos Film</font> <font size=1> THE
> > FAVORITE<br>Giorgos Lanthimos (USA, UK, Irland )<br>5/9/2019 -
> > Feelgood Ent</font> etc...
> > 
> > What I need is to split it in three different fields (columns)
> > 
> > 1. to keep only the Movie Title clearing the first 14 standard
> > characters and everything after the end of the title e.g.
> >     ANGRY BIRDS  2
> >     THE FAVORITE
> > 2. to keep only the director´s name (after the first "<br> " and
> > everything after the first "(" e.g.
> >     Thurop Van Orman, John Rice
> >     Giorgos Lanthimos
> > 3. to keep only the Production Countries (including into
> > parenthesis) clearing all the previous and after that characters
> > 
> > is that possible from msaccess query?
> > 
> > Many thank´s
> > /kostas
> > 
> > 
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 





More information about the AccessD mailing list