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
|
|||
|
|||
Access Expression
I'm trying to convert the results of an expression but I can't seem to get it
right and I don't know if its possible to do the way I'm going about it. I also want to be able to print a report which I have created using the data from the query. t bottles: ([c size]*[first count])/[bottle size] LESS THAN ONE (1) ENTER 0.5 FROM 1.0 TO 1.25 ENTER 1 FROM 1.26 TO 1.75 ENTER 1.5 FROM 1.76 TO 2 ENTER 2 Can someone help? |
#2
|
|||
|
|||
Access Expression
You have a calulated field and then some data that is not related to the
field. Can you provide more information? "ft" wrote: I'm trying to convert the results of an expression but I can't seem to get it right and I don't know if its possible to do the way I'm going about it. I also want to be able to print a report which I have created using the data from the query. t bottles: ([c size]*[first count])/[bottle size] LESS THAN ONE (1) ENTER 0.5 FROM 1.0 TO 1.25 ENTER 1 FROM 1.26 TO 1.75 ENTER 1.5 FROM 1.76 TO 2 ENTER 2 Can someone help? |
#3
|
|||
|
|||
Access Expression
On Thu, 12 Oct 2006 12:18:02 -0700, ft
wrote: I'm trying to convert the results of an expression but I can't seem to get it right and I don't know if its possible to do the way I'm going about it. I also want to be able to print a report which I have created using the data from the query. t bottles: ([c size]*[first count])/[bottle size] LESS THAN ONE (1) ENTER 0.5 FROM 1.0 TO 1.25 ENTER 1 FROM 1.26 TO 1.75 ENTER 1.5 FROM 1.76 TO 2 ENTER 2 Can someone help? Try using the Switch() function: Switch([c size]*[first count])/[bottle size] 1, 0.5, [c size]*[first count])/[bottle size] = 1.25, 1.0, [c size]*[first count])/[bottle size] = 1.75, 1.5, [c size]*[first count])/[bottle size] = 2, 2.0, True, Null) The Switch() function takes arguments in pairs; when it first hits a pair with a TRUE value for the first member of the pair, it returns the second member of the pair and quits. Hence the last pair - return a NULL if the expression isn't in any of the ranges or is not defined. John W. Vinson[MVP] |
#4
|
|||
|
|||
Access Expression
What I have is this.
We have some prepack cards (medications) and this cards have diferent amounts of pills in them... could be 30, 15 , 45 etc. that's my [c size] The other field [first count] is the numbers of those cards we have in stock at the time of the physical count. [bottle size] is the manufacturer bottle quantity. What I'm doing is taking the the count and * the card size... then dividing by the bottle size to get the amount of bottles we have in those cards. However we want to convert the results to a more accurate number. So if the amount is less then 1 we want to convert to 0.5 If it's between an #(interger) and #.25 we want to convert it to # If it's between an #.26 and #.75 we want to convert it to #.5 If it's between an #.76 and the next up # we want to convert it to the next up # This is what I had but this are separate columns but it does not work. TOTAL COUNT1: IIf(([c size]*[first count]/[bottle size]-Int([c size]*[first count]/[bottle size])) Between 0.75 And 0.999999,Int([c size]*[first count]/[bottle size])+1,[c size]*[first count]/[bottle size]) TOTAL COUNT2: IIf(([c size]*[first count]/[bottle size]-Int([c size]*[first count]/[bottle size])) Between 0.26 And 0.75,Int([c size]*[first count]/[bottle size])+0.5,[c size]*[first count]/[bottle size]) TOTAL COUNT3: IIf(([c size]*[first count]/[bottle size]-Int([c size]*[first count]/[bottle size])) Between 0 And 0.25,Int([c size]*[first count]/[bottle size]),[c size]*[first count]/[bottle size]) See if we can help me to have this expresisons or a better one in one column. "KARL DEWEY" wrote: You have a calulated field and then some data that is not related to the field. Can you provide more information? "ft" wrote: I'm trying to convert the results of an expression but I can't seem to get it right and I don't know if its possible to do the way I'm going about it. I also want to be able to print a report which I have created using the data from the query. t bottles: ([c size]*[first count])/[bottle size] LESS THAN ONE (1) ENTER 0.5 FROM 1.0 TO 1.25 ENTER 1 FROM 1.26 TO 1.75 ENTER 1.5 FROM 1.76 TO 2 ENTER 2 Can someone help? |
#5
|
|||
|
|||
Access Expression
Thanks this works but the problem is that I wasn't very clear the first time.
I want it to work not only with the results examples that I gave but also to work if the result is say for example 20.66 to convert to 20.5. Sorry! because the result coul be 1.25 or 25.1 or 100.76 etc Thank you "John Vinson" wrote: On Thu, 12 Oct 2006 12:18:02 -0700, ft wrote: I'm trying to convert the results of an expression but I can't seem to get it right and I don't know if its possible to do the way I'm going about it. I also want to be able to print a report which I have created using the data from the query. t bottles: ([c size]*[first count])/[bottle size] LESS THAN ONE (1) ENTER 0.5 FROM 1.0 TO 1.25 ENTER 1 FROM 1.26 TO 1.75 ENTER 1.5 FROM 1.76 TO 2 ENTER 2 Can someone help? Try using the Switch() function: Switch([c size]*[first count])/[bottle size] 1, 0.5, [c size]*[first count])/[bottle size] = 1.25, 1.0, [c size]*[first count])/[bottle size] = 1.75, 1.5, [c size]*[first count])/[bottle size] = 2, 2.0, True, Null) The Switch() function takes arguments in pairs; when it first hits a pair with a TRUE value for the first member of the pair, it returns the second member of the pair and quits. Hence the last pair - return a NULL if the expression isn't in any of the ranges or is not defined. John W. Vinson[MVP] |
#6
|
|||
|
|||
Access Expression
On Fri, 13 Oct 2006 10:10:02 -0700, ft
wrote: Thanks this works but the problem is that I wasn't very clear the first time. I want it to work not only with the results examples that I gave but also to work if the result is say for example 20.66 to convert to 20.5. Sorry! because the result coul be 1.25 or 25.1 or 100.76 etc So you want the value rounded to the nearest half-integer value? Ok, try: Round(2*[your expression], 0) / 2 John W. Vinson[MVP] |
#7
|
|||
|
|||
Access Expression
Thanks it worked, but can we keep anything from been converted down to zero?
"John Vinson" wrote: On Fri, 13 Oct 2006 10:10:02 -0700, ft wrote: Thanks this works but the problem is that I wasn't very clear the first time. I want it to work not only with the results examples that I gave but also to work if the result is say for example 20.66 to convert to 20.5. Sorry! because the result coul be 1.25 or 25.1 or 100.76 etc So you want the value rounded to the nearest half-integer value? Ok, try: Round(2*[your expression], 0) / 2 John W. Vinson[MVP] |
#8
|
|||
|
|||
Access Expression
This uses nested IIF statement.
SELECT ftTABLE.Item, ftTABLE.[C Size], ftTABLE.[first count], ftTABLE.[bottle size], [c size]*[first count]/[bottle size] AS Bottles, IIf([c size]*[first count]/[bottle size]1,0.5,IIf([c size]*[first count]/[bottle size]-Int([c size]*[first count]/[bottle size])0 And [c size]*[first count]/[bottle size]-Int([c size]*[first count]/[bottle size])=0.25,Int([c size]*[first count]/[bottle size]),IIf([c size]*[first count]/[bottle size]-Int([c size]*[first count]/[bottle size])0.25 And [c size]*[first count]/[bottle size]-Int([c size]*[first count]/[bottle size])=0.75,Int([c size]*[first count]/[bottle size])+0.5,Int([c size]*[first count]/[bottle size])+1))) AS [Total Bottles] FROM ftTABLE; "ft" wrote: What I have is this. We have some prepack cards (medications) and this cards have diferent amounts of pills in them... could be 30, 15 , 45 etc. that's my [c size] The other field [first count] is the numbers of those cards we have in stock at the time of the physical count. [bottle size] is the manufacturer bottle quantity. What I'm doing is taking the the count and * the card size... then dividing by the bottle size to get the amount of bottles we have in those cards. However we want to convert the results to a more accurate number. So if the amount is less then 1 we want to convert to 0.5 If it's between an #(interger) and #.25 we want to convert it to # If it's between an #.26 and #.75 we want to convert it to #.5 If it's between an #.76 and the next up # we want to convert it to the next up # This is what I had but this are separate columns but it does not work. TOTAL COUNT1: IIf(([c size]*[first count]/[bottle size]-Int([c size]*[first count]/[bottle size])) Between 0.75 And 0.999999,Int([c size]*[first count]/[bottle size])+1,[c size]*[first count]/[bottle size]) TOTAL COUNT2: IIf(([c size]*[first count]/[bottle size]-Int([c size]*[first count]/[bottle size])) Between 0.26 And 0.75,Int([c size]*[first count]/[bottle size])+0.5,[c size]*[first count]/[bottle size]) TOTAL COUNT3: IIf(([c size]*[first count]/[bottle size]-Int([c size]*[first count]/[bottle size])) Between 0 And 0.25,Int([c size]*[first count]/[bottle size]),[c size]*[first count]/[bottle size]) See if we can help me to have this expresisons or a better one in one column. "KARL DEWEY" wrote: You have a calulated field and then some data that is not related to the field. Can you provide more information? "ft" wrote: I'm trying to convert the results of an expression but I can't seem to get it right and I don't know if its possible to do the way I'm going about it. I also want to be able to print a report which I have created using the data from the query. t bottles: ([c size]*[first count])/[bottle size] LESS THAN ONE (1) ENTER 0.5 FROM 1.0 TO 1.25 ENTER 1 FROM 1.26 TO 1.75 ENTER 1.5 FROM 1.76 TO 2 ENTER 2 Can someone help? |
#9
|
|||
|
|||
Access Expression
On Fri, 13 Oct 2006 14:01:01 -0700, ft
wrote: Thanks it worked, but can we keep anything from been converted down to zero? Iif([your expression] 0.75, 0.5, Round(2*[your expression], 0) / 2) John W. Vinson[MVP] |
#10
|
|||
|
|||
Access Expression
I hate brackets
pills: IIf([qty]1,0.5,Int([qty])+(Int((2*([qty]-Int([qty])+0.249))))/2) "ft" wrote in message ... Thanks this works but the problem is that I wasn't very clear the first time. I want it to work not only with the results examples that I gave but also to work if the result is say for example 20.66 to convert to 20.5. Sorry! because the result coul be 1.25 or 25.1 or 100.76 etc Thank you "John Vinson" wrote: On Thu, 12 Oct 2006 12:18:02 -0700, ft wrote: I'm trying to convert the results of an expression but I can't seem to get it right and I don't know if its possible to do the way I'm going about it. I also want to be able to print a report which I have created using the data from the query. t bottles: ([c size]*[first count])/[bottle size] LESS THAN ONE (1) ENTER 0.5 FROM 1.0 TO 1.25 ENTER 1 FROM 1.26 TO 1.75 ENTER 1.5 FROM 1.76 TO 2 ENTER 2 Can someone help? Try using the Switch() function: Switch([c size]*[first count])/[bottle size] 1, 0.5, [c size]*[first count])/[bottle size] = 1.25, 1.0, [c size]*[first count])/[bottle size] = 1.75, 1.5, [c size]*[first count])/[bottle size] = 2, 2.0, True, Null) The Switch() function takes arguments in pairs; when it first hits a pair with a TRUE value for the first member of the pair, it returns the second member of the pair and quits. Hence the last pair - return a NULL if the expression isn't in any of the ranges or is not defined. John W. Vinson[MVP] |
|
Thread Tools | |
Display Modes | |
|
|