Trying to pull specific string from a field

  • Thread starter Thread starter Shams
  • Start date Start date
S

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
 
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
Click to expand...


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")
 
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
Click to expand...


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.
.
Click to expand...
 
Back
Top