Using Mid and Find function in Access

[psulions83]

Hi,

I am trying to convert an Excel formula to Access but cannot figure out what will work. I have tried using InStr for the FIND alternative but cannot get it to work.

Excel formula
MID(O2,FIND("%",O2)-1,1)

I feel like I am doing something that is a simple fix but for some reason I cannot work around it.

Any help would be much appreciated.

 

[welshgasman]

myString=MID("YourString",instr("YourString","%")-1,1)

 

[psulions83]

thank you.....that worked great. I thought i performed the function like that before but i guess i messed something up.

In the below formula, any idea why i would ever get #FUNC ! and #ERROR !?

IIf(IsNumeric(Mid([Tbl_Contract]![PayTerm],InStr([Tbl_Contract]![PayTerm],"%")-1,1)),Mid([Tbl_Contract]![PayTerm],InStr([Tbl_Contract]![PayTerm],"%")-1,1)/100,0)

 

[xenou]

Hi,
You have to be very careful with IIF (or like me, just use it very rarely or never). VBA IIF does not use short circuit evaluation.

This means that the ENTIRE expression is evaluated, including all parts, including your second part that attempts to divide with something that may not be a number, even though you probably don't want that to happen if the first part is false.

In this case, you probably cannot use IIF() this way.

 

[psulions83]

Thats interesting. Thank you for that bit of information. I guess there is no way to suppress this with access then?

 

[psulions83]

When I wrap the IsError function around the MID it makes all the lines that do not have an error zero. Before putting it the lines that should have an answer were right vs the other lines erroring out.

 

[xenou]

When I wrap the IsError function around the MID it makes all the lines that do not have an error zero. Before putting it the lines that should have an answer were right vs the other lines erroring out.

Is this satisfactory or is it still a problem?

 

[psulions83]

Xenou,

Not really but I found a work around since using IIf statements don't really work well for error handling. Thanks for letting me know about that so I didn't spend 20 hours trying to figure out why it wasn't working!

Thanks

 

[xenou]

Okay. As a note, you can sometimes just divide your data into the good and the bad (and if necessary the ugly as well):

SELECT
	(A / B) as DangerousOperation 
FROM 
	Table 
WHERE
	B <> 0

UNION ALL

SELECT
	0 as DangerousOperation 
FROM 
	Table 
WHERE
	B = 0

 

[psulions83]

Will give that a try....thank you!