FIND function ?

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.
 
S

strive4peace

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 ;)
 
S

SmartCookie

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!
 
S

SmartCookie

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!
 
S

strive4peace

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 ;)
 
Top