Find in string and

[NigelBrown]

Hi,

Would anyone now how to write an instr that would grab only the date from the string below (i.e between the 2 colons)

I have tried multiple times to get this to work to no avail, the colons will NOT always be at character 14, so need to find first ":" then grab the next 8 caracters.

USINAS SID MI:30/12/09:0.3072:85

i would like to achieve the result

30/12/09

so far I have tried using Left with instr and Right - getting very frustrated.

Thanks in advance

Nigel

[MMcCarthy]

Try this one ...

Right(Left("USINAS SID MI:30/12/09:0.3072:85", InStr(1, "USINAS SID MI:30/12/09:0.3072:85", "/", vbTextCompare) + 5), 8)
 

[NigelBrown]

Thanks msquared,
any chance I could put this in a query, dont really want to create a function but will do if it is the best way. I only need to perform this once a day on import for about 50 rows and was trying to just put an make table query on it.
Thanks

[NigelBrown]

no worries - got it to work - just took the vbtextcompare out. Thanks for this saved me a lot of time

[MMcCarthy]

You're welcome, glad you got it to work.

[nico5038]

An alternative might be a function:

Function fncSplit(strInput As String)
   Dim arr
   arr = Split(strInput, ":")
   fncSplit = arr(1)
End Function
 

In a query you can use:

SELECT fncSplit([Field with colons]), ... FROM tblX
 

Nic;o)

[NigelBrown]

Hi Guys,

Thanks you both for your answers, both work well, have a quick question, tried to do it myself but can this be modified to dismiss any fields that do not contain any ":" ? - at the moment it either returns the first 8 char with first option, or using the Function it will Error. I have tried writing an imbedded IF statement (i.e. IF(Instr[filed1],1=";", etc ) but cannot solve this. Appreciate the help Nic, Msquared.
Thanks

[nico5038]

Try

Function fncSplit(strInput As String)
 
    Dim arr
 
    if Instr(strInput,":") > 0 then
       arr = Split(strInput, ":")
       fncSplit = arr(1)
    else
       'Empty string , or whatever you want to return
       fncSplit = ""
    end if
 
End Function
 

Nic;o)

[NigelBrown]

Thanks Nic, should have known that myself - been staring at this far to long - going to take a break I think. Thanks again