Query to Break Table Data down into more fields

[itsmythg]

I have a table that has data i want to parse out part of for a separate column in the query.

THE LINE READS;
[COLOR=red yellow]IPSec ESP Tunnel Inb: Packet authentication failed username: xxxxxx\xxxxxx SPI: ttttttt. Dump of failed hash follows.[/color]

I WROTE;
[blue]IIf(InStr([Detail1],"authentication failed"),Right([Detail1],InStr(1,[Detail1],"Username: ")*1))[/blue]

Results;
[COLOR=red yellow]xxxxxx\xxxxxx SPI: ttttttt. Dump of failed hash follows.[/color]

I ONLY WANT;
[COLOR=red yellow]xxxxxx\xxxxxx[/color]

I TRIED;
[blue]IIf(InStr([Detail1],"authentication failed"),Right([Detail1],InStr(1,[Detail1],"Username: ")*1))-InStr(Right([Detail1],Len([Detail1])-InStr([Detail1],"S"))*1)[/blue]

But it takes an error.

Let me say thank you in advance for your help.

 

[PWise]

try

IIf(InStr([Detail1],"authentication failed"),
mid([Detail1],InStr(1,[Detail1],"Username: ")+1,99)
,"")

 

[itsmythg]

Thanks but i don't hink i explained in right i only wnat the information about the user and want to cut off every thing after likeSPI: ttttttt. Dump of failed hash follows.

 

[GingerR]

Trim(Mid([Detail1],InStr([Detail1],"Username: ")+9,InStr([Detail1],"SPI")-InStr([Detail1],"Username: ")-9))

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[itsmythg]

my error, i should have shown more data every line has the exact same data but because user names are different the number of characters will be different. I want it to stop when it sees "spi" i have done this before but for the life of me i am getting so forgetful i am going to loose my way home some nite.
Thanks again for your time.

 

[PHV]

Mid(Left(Detail1,InStr(Detail1,"  SPI: ")-1),InStr(Detail1,"username:")+10)

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

 

[itsmythg]

You guys are a great bunch!!! thanks for your help. I will be sure to save my code this time so I will remember how to do it in the future.

Thanks Roxie