#Error when referencing "," in query formula

  • Thread starter Thread starter bandblue
  • Start date Start date
B

bandblue

Hello,

I have a query that I am pulling out selective data from a string of a
part description. Here is the part description.

RESISTOR 0603 47KOHM,5%
Note the Field name is [ITEM_DESCRIPTION]

Here is my formula
Tolerance1:
IIf(InStr([ITEM_DESCRIPTION],"%")>0,IIf(Mid([ITEM_DESCRIPTION],InStr([ITEM_DESCRIPTION],"%")-2,1)="
",Mid([ITEM_DESCRIPTION],InStr([ITEM_DESCRIPTION],"%")-1,2),IIf(Mid([ITEM_DESCRIPTION],InStr([ITEM_DESCRIPTION],"%")-2,1)=",",Mid([ITEM_DESCRIPTION],InStr([ITEM_DESCRIPTION],"%"-1),2)

The formula works fine when referencing " " however the result is
#Error when I reference the comma ","

This happens in all cases where a comma is instead of a space. Any idea
on what I need to change? Thank you for your help
 
What do you mean be "referencing"? Are you changing part of the formulae to ",
" instead of " "? Or are these values in the field the forumla is being
applied to?
 
Thank you for your question. You are correct I want to formula to look
for any "%" then to extract this out of the description I apply the
instr function to find the position of the "%" in the string and then
substract until a " " or "," is found.

The error happens when a "," is noted in my formula. To extract the
percentage "%" My part description is as follows
RESISTOR 0603 47KOHM,5%
So if my formula is correct the result should be 5% but instead the
result is #Error when the following formula is applied
IIf(Mid([ITEM_D­ESCRIPTION],InStr([ITEM_DESCRIPTION],"%")-2,1)=",",Mid([ITEM_DESCRIPTION],I­nStr([ITEM_DESCRIPTION],"%"-1),2)


However when I look for " " that preceeds the "%" the formula returns
the correct value of 5%.

I assume is has to do with using the comma in quotations in the formula
however I cannot find a fix. Any suggestions?
 
Back
Top