If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#Error when referencing "," in query formula
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 |
#2
|
|||
|
|||
#Error when referencing "," in query formula
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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200510/1 |
#3
|
|||
|
|||
#Error when referencing "," in query formula
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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
Error when running Query, but not Report | Blaze | Running & Setting Up Queries | 29 | September 9th, 2005 05:40 AM |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
Hidden files in Ms-Query cause ODBC connect errors or Query is wac | needyourhelp | General Discussion | 4 | July 12th, 2004 09:38 PM |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |