Extracting Part of String within Separators 1

[sardine53]

Hello,

Access 2007.

I have a field "Account" with this data:
12*2222*22
3*88888*779
99*22433*1

I would like a formula for "Location" that would return only the info between the "*":
2222
88888
22433

This formula almost works but returns the numbers with an * at the end:

Location: Trim(Mid([Account],InStr(1,[Account],"*")+1,InStr(InStr(1,[Account],"*")+1,[Account],"*")-InStr(1,[Account],"*")))

Any help would be appreciated.

Thanks!

 

[MajP]

Public Function getBetween(varFld As Variant) As Variant
  'UDF in a Standard module
  If Not IsNull(varFld) Then
    getBetween = Split(varFld, "*")(1)
  End If
End Function

Example
SELECT Table1.Field1, getBetween([Field1]) AS BetweenValue
FROM Table1;

 

[PHV]

Location: Trim(Mid([Account],InStr(1,[Account],"*")+1,InStr(InStr(1,[Account],"*")+1,[Account],"*")-InStr(1,[Account],"*")[!]-1[/!]))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sardine53]

Perfect PH! Thanks so much.

 

[TheAceMan1]

How are ya sardine53 . . .

As usual ... [blue]PHV's[/blue] solution is spot on. My question is:

[blue]Is there any posibility of [purple]Account[/purple] returning a [blue]Null[/blue]?[/blue]

If true! . . . the formula will fail ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

mine won't.

 

[TheAceMan1]

Howdy [blue]MajP[/blue] . . .

Sometimes my [blue]speed reading[/blue] gets ahead of me! ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[HarleyQuinn]

I know it's not stated in the OP, but your solution will fail if for any reason (apart from it being null) there isn't a * in the field though.

Andy
---------------------------------
Zebracorn: 50% Zebra, 50% Unicorn = 100% Real.

http://lessthandot.com

 

[HarleyQuinn]

Just to be picky of course

Andy
---------------------------------
Zebracorn: 50% Zebra, 50% Unicorn = 100% Real.

http://lessthandot.com

 

[MajP]

Good point. You always want to not put error checking in a UDF used in a query. If not a big query will lock the database.
[/code]
Public Function getBetween(varFld As Variant) As Variant
On Error Resume Next
getBetween = Split(varFld, "*")(1)
End Function
[/code]

Assuming most values are in the proper form it is probably more efficient to simply trap the error than checking for null and checking that ubound(Split(varFld, "*"))>0