Access Expression

[Guest]

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?

 

[Guest]

You have a calulated field and then some data that is not related to the
field.

Can you provide more information?

 

[John Vinson]

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]

 

[Guest]

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.

You have a calulated field and then some data that is not related to the
field.

Can you provide more information?

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?

 

[Guest]

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

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]

 

[John Vinson]

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]

 

[Guest]

Thanks it worked, but can we keep anything from been converted down to zero?

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]

 

[Guest]

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;

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.

You have a calulated field and then some data that is not related to the
field.

Can you provide more information?

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?

 

[John Vinson]

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]

 

[David F Cox]

I hate brackets

pills: IIf([qty]<1,0.5,Int([qty])+(Int((2*([qty]-Int([qty])+0.249))))/2)

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

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]

 

[Guest]

Thank you very much, it works but of course you already know that.

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]