Trying to pull specific string from a field

[Shams]

Hi...I have the following string in my NOTES field:


[ NOTE STAMP: 07/20/2004 At 09:57 ] - DARRELL
[ CN: LOAN # 16485 ]
CHECK #1234, AMOUNT: $123

I'm trying to just pull the Loan number 16485 in a query,
any thoughts?

Thank a bunch!

Shams

 

[fredg]

Hi...I have the following string in my NOTES field:

[ NOTE STAMP: 07/20/2004 At 09:57 ] - DARRELL
[ CN: LOAN # 16485 ]
CHECK #1234, AMOUNT: $123

I'm trying to just pull the Loan number 16485 in a query,
any thoughts?

Thank a bunch!

Shams

If the loan number is always 5 digits:

LoanNum:IIf(InStr([FieldName],"LOAN ") > 0,
Mid([FieldName],InStr([FieldName],"LOAN")+7,5),"N/A")

If the loan number length can vary:

LoanNum: IIf(InStr([FieldName],"LOAN ")> 0,
Val(Mid([FieldName],InStr([FieldName],"LOAN")+7,5)),"N/A")

 

[Guest]

Thank you so much Fred. This works great!

Shams

-----Original Message-----

Hi...I have the following string in my NOTES field:

[ NOTE STAMP: 07/20/2004 At 09:57 ] - DARRELL
[ CN: LOAN # 16485 ]
CHECK #1234, AMOUNT: $123

I'm trying to just pull the Loan number 16485 in a query,
any thoughts?

Thank a bunch!

Shams

If the loan number is always 5 digits:

LoanNum:IIf(InStr([FieldName],"LOAN ") > 0,
Mid([FieldName],InStr([FieldName],"LOAN")+7,5),"N/A")

If the loan number length can vary:

LoanNum: IIf(InStr([FieldName],"LOAN ")> 0,
Val(Mid([FieldName],InStr([FieldName],"LOAN") +7,5)),"N/A")
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.