Transferring IF/FIND/MID Formula from Excel to Access Form

[mcguitar]

Good day,

I have something that works in Excel, and I am working on transferring it to Access.

In Excel, I can paste a selection of data in one cell (M2), and cells M5 - M18 pulls data from that one cell using IF, FIND and MID formulas. I am using the FIND formula to find the position of the specific text, then the MID formula to pull the data.

=IF(M2="","",MID(M2, (FIND("%", M2, 1)+1), (FIND("*", M2, 1)-1)-(FIND("%", M2, 1)+1)))
or
=IF(M2="","",MID(M2, (FIND("$", M2, 1)+1), 5))

I can use the MID formula, but when implementing the FIND portion, it errors out. I have replaced the M2 with [Text200] as this is the form data where the information is input.

How do I translate this to Access friendly forms? Thanks all!

 

[Joe4]

In Access, you use IIF instead of IF.
And you can use the INSTR function in place of FIND (see: MS Access: Instr Function).

Armed with that knowledge, you should be able to adapt your formula.