FIND function ?

  • Thread starter Thread starter SmartCookie
  • Start date Start date
S

SmartCookie

Hello, and thanks in advance for help yet again. I've searched for
this with no luck. (My searching skills must need improvement!)

I have the following functions that I can use in Excel. How do I do
the same thing in Access?

FIND (",",A1) In my excel example this is in field D1.
and
MID (A1,4,D1-4)
-----------------------
The field text shows as:

M# 838, Fifth Third Bank, IV#.... etc. In my excel example, this
is field A1
-------------------------
I need to pick out the 838 in this example, so I can link to another
table to find the company name. The problem is that this could be
anywhere from 2 digits or could be 6 digits.

Does this make sense? Any help? Is there just an easier way in access
that I'm not finding? I thought any functions in Excel would be
available in Access.

Michelle
Cincinnati USA Regional Chamber.
 
If the value you want will always be between the first space
and the first comma, you can do this:

calculated field in query:

IDcalc: mid([fld], instr([fld]," ")+1,
instr([fld],",")-instr([fld]," ")-1)

where [fld] is the name of your field

IDcalc is whatevever you want to call the column name --
anything before the colon will be the name of the column

calculated field on form:

Name --> IDcalc
controlSource --> mid([controlname], instr([controlname],"
")+1, instr([controlname],",")-instr([controlname]," ")-1)

where [controlname] is the NAME property of a control on the
form

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)
 
IDcalc: mid([fld], instr([fld]," ")+1,
instr([fld],",")-instr([fld]," ")-1)


This is brilliant, thank you!!! Thanks both of you for responding.

I have one more problem, however. I didn't think to mention that the
field doesn't always start with "M# 838, Fifth third... " but these
are the only fields I want this ID from.

I tried to throw and IIF(Left([Trans Description],1)="M" around it ...
but I am getting prompted for "M" so I've obviously done something
wrong.

I amended the expression you gave me to: IDcalc: IIf(Left([Trans
Description],1)="M",(Mid([Trans Description],InStr([Trans
Description]," ")+1,InStr([Trans Description],",")-InStr([Trans
Description]," ")-1)),0)

What have I done wrong? Thanks again so much!
 
IDcalc: mid([fld], instr([fld]," ")+1,
instr([fld],",")-instr([fld]," ")-1)


This is brilliant, thank you!!! Thanks both of you for responding.

I have one more problem, however. I didn't think to mention that the
field doesn't always start with "M# 838, Fifth third... " but these
are the only fields I want this ID from.

I tried to throw and IIF(Left([Trans Description],1)="M" around it ...
but I am getting prompted for "M" so I've obviously done something
wrong.

I amended the expression you gave me to: IDcalc: IIf(Left([Trans
Description],1)="M",(Mid([Trans Description],InStr([Trans
Description]," ")+1,InStr([Trans Description],",")-InStr([Trans
Description]," ")-1)),0)

What have I done wrong? Thanks again so much!
 
you are welcome ;)

in another column on the grid:

field --> FirstLetter: Left([Trans Description],1)
criteria --> "M"
show --> no (not necessary to show the criteria)

then put the equation from earlier in a seperate column

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)
 
Back
Top