Round Up In Queries

I just love it when a problem is stated and then additional items ( /20 ) are
thrown into the mix after the response.
--
KARL DEWEY
Build a little - Test a little


" wrote:

Here is what I typed:
GCDV: IIf([CDV]/20Int([CDV]/20),Int([CDV]/20)+1,[CDV]/20)
The output is 0
If CDV=38, then the output be 2

Thanks for your quick help, didn't expect responses so soon!


--
Mark Matzke


"KARL DEWEY" wrote:

Try this ---
IIf([YourField]Int([YourField]),Int([YourField])+1,[YourField])
--
KARL DEWEY
Build a little - Test a little


" wrote:

I need to have numbers such as 1.00001 or 1.5, or 1.95843 all round up to the
number 2. Can anyone help me with this?
--
Mark Matzke

Yeah, that was my bad, but in my defrense it is hard to know how to pose a
question correctly. Hope I didn't negitively impact your day. This forum,
and the folks like you , are the only things that has kept me successful in
ACCESS, so regardless of anything else, thank you for helping!
--
Mark Matzke


"KARL DEWEY" wrote:

I just love it when a problem is stated and then additional items ( /20 ) are
thrown into the mix after the response.
--
KARL DEWEY
Build a little - Test a little


" wrote:

Here is what I typed:
GCDV: IIf([CDV]/20Int([CDV]/20),Int([CDV]/20)+1,[CDV]/20)
The output is 0
If CDV=38, then the output be 2

Thanks for your quick help, didn't expect responses so soon!


--
Mark Matzke


"KARL DEWEY" wrote:

Try this ---
IIf([YourField]Int([YourField]),Int([YourField])+1,[YourField])
--
KARL DEWEY
Build a little - Test a little


" wrote:

I need to have numbers such as 1.00001 or 1.5, or 1.95843 all round up to the
number 2. Can anyone help me with this?
--
Mark Matzke

Number (Long Integer)
--
Mark Matzke


"Jason Lepack" wrote:

I retract my statement, a value of 38/20 (1.9) with my function
returns 1.9 (not 0 though...) but Allen Browne's works just fine.
What is the data type of [CDV]?

On Apr 17, 12:50 pm,
wrote:
GCDV: IIf(([CDV]/20)\1([CDV]/20),([CDV]/20)\1+1,([CDV]/20))
brought me to 0, as did the other two suggestions, so i tested my data and
when i do just
GCDV: [CDV]
I get 38, which is correct. my hope is that I can divide that number
(variable) by 20 and round up.

Thanks for your help, please let me know if you have any ideas.

--
Mark Matzke



"Jason Lepack" wrote:
SELECT iif([yournumfield]\1[yournumfield], [yournumfield]\1+1,
[yournumfield])
FROM [yourtable]

Note that it's \ not /

\ is integer division. 1.02 \ 1 = 1

Or you could create a ceiling function:

function ceiling(double x) as integer
ceiling = x \ 1
if x ceiling then
ceiling = ceiling + 1
end if
end function

and call it in your query:

SELECT ceiling([yournumfield])
FROM [yourtable]

Cheers,
Jason Lepack

On Apr 17, 11:52 am,
wrote:
I need to have numbers such as 1.00001 or 1.5, or 1.95843 all round up to the
number 2. Can anyone help me with this?
--
Mark Matzke- Hide quoted text -

- Show quoted text -

SELECT
[CDV]/20 AS a,
-([CDV]/20) AS b,
int(-([CDV]/20)) AS c,
-int(-([CDV]/20)) AS d
FROM
[yourtable]

What does this query return?

On Apr 17, 2:14 pm,
wrote:
Number (Long Integer)
--
Mark Matzke



"Jason Lepack" wrote:
I retract my statement, a value of 38/20 (1.9) with my function
returns 1.9 (not 0 though...) but Allen Browne's works just fine.
What is the data type of [CDV]?

On Apr 17, 12:50 pm,
wrote:
GCDV: IIf(([CDV]/20)\1([CDV]/20),([CDV]/20)\1+1,([CDV]/20))
brought me to 0, as did the other two suggestions, so i tested my data and
when i do just
GCDV: [CDV]
I get 38, which is correct. my hope is that I can divide that number
(variable) by 20 and round up.

Thanks for your help, please let me know if you have any ideas.

--
Mark Matzke

"Jason Lepack" wrote:
SELECT iif([yournumfield]\1[yournumfield], [yournumfield]\1+1,
[yournumfield])
FROM [yourtable]

Note that it's \ not /

\ is integer division. 1.02 \ 1 = 1

Or you could create a ceiling function:

function ceiling(double x) as integer
ceiling = x \ 1
if x ceiling then
ceiling = ceiling + 1
end if
end function

and call it in your query:

SELECT ceiling([yournumfield])
FROM [yourtable]

Cheers,
Jason Lepack

On Apr 17, 11:52 am,
wrote:
I need to have numbers such as 1.00001 or 1.5, or 1.95843 all round up to the
number 2. Can anyone help me with this?
--
Mark Matzke- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

a b c d
1.9 -1.9 -2 2

Hey, I have a new question under forms, it is:

Subject: Long Iff Statement 4/17/2007 12:03 PM PST

Can I do an iff statment like:

=Iff([Text93][Text84] Or [CDV][ Text68] Or [CHSI][Text70] Or
[Text39][Text82] Or [DIGI][Text71] Or [Text40][Text83] Or
[Text60][Text102],Yes,No)

Where if any of these statements are true it will give a result of Yes, if
not then No?

--
Mark Matzke

--
Mark Matzke


"Jason Lepack" wrote:

SELECT
[CDV]/20 AS a,
-([CDV]/20) AS b,
int(-([CDV]/20)) AS c,
-int(-([CDV]/20)) AS d
FROM
[yourtable]

What does this query return?

On Apr 17, 2:14 pm,
wrote:
Number (Long Integer)
--
Mark Matzke



"Jason Lepack" wrote:
I retract my statement, a value of 38/20 (1.9) with my function
returns 1.9 (not 0 though...) but Allen Browne's works just fine.
What is the data type of [CDV]?

On Apr 17, 12:50 pm,
wrote:
GCDV: IIf(([CDV]/20)\1([CDV]/20),([CDV]/20)\1+1,([CDV]/20))
brought me to 0, as did the other two suggestions, so i tested my data and
when i do just
GCDV: [CDV]
I get 38, which is correct. my hope is that I can divide that number
(variable) by 20 and round up.

Thanks for your help, please let me know if you have any ideas.

--
Mark Matzke

"Jason Lepack" wrote:
SELECT iif([yournumfield]\1[yournumfield], [yournumfield]\1+1,
[yournumfield])
FROM [yourtable]

Note that it's \ not /

\ is integer division. 1.02 \ 1 = 1

Or you could create a ceiling function:

function ceiling(double x) as integer
ceiling = x \ 1
if x ceiling then
ceiling = ceiling + 1
end if
end function

and call it in your query:

SELECT ceiling([yournumfield])
FROM [yourtable]

Cheers,
Jason Lepack

On Apr 17, 11:52 am,
wrote:
I need to have numbers such as 1.00001 or 1.5, or 1.95843 all round up to the
number 2. Can anyone help me with this?
--
Mark Matzke- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Yes, but you will have to use 'Yes' unless you're talking about a
check box.

On Apr 17, 3:10 pm,
wrote:
a b c d
1.9 -1.9 -2 2

Hey, I have a new question under forms, it is:

Subject: Long Iff Statement 4/17/2007 12:03 PM PST

Can I do an iff statment like:

=Iff([Text93][Text84] Or [CDV][ Text68] Or [CHSI][Text70] Or
[Text39][Text82] Or [DIGI][Text71] Or [Text40][Text83] Or
[Text60][Text102],Yes,No)

Where if any of these statements are true it will give a result of Yes, if
not then No?

--
Mark Matzke

--
Mark Matzke



"Jason Lepack" wrote:
SELECT
[CDV]/20 AS a,
-([CDV]/20) AS b,
int(-([CDV]/20)) AS c,
-int(-([CDV]/20)) AS d
FROM
[yourtable]

What does this query return?

On Apr 17, 2:14 pm,
wrote:
Number (Long Integer)
--
Mark Matzke

"Jason Lepack" wrote:
I retract my statement, a value of 38/20 (1.9) with my function
returns 1.9 (not 0 though...) but Allen Browne's works just fine.
What is the data type of [CDV]?

On Apr 17, 12:50 pm,
wrote:
GCDV: IIf(([CDV]/20)\1([CDV]/20),([CDV]/20)\1+1,([CDV]/20))
brought me to 0, as did the other two suggestions, so i tested my data and
when i do just
GCDV: [CDV]
I get 38, which is correct. my hope is that I can divide that number
(variable) by 20 and round up.

Thanks for your help, please let me know if you have any ideas.

--
Mark Matzke

"Jason Lepack" wrote:
SELECT iif([yournumfield]\1[yournumfield], [yournumfield]\1+1,
[yournumfield])
FROM [yourtable]

Note that it's \ not /

\ is integer division. 1.02 \ 1 = 1

Or you could create a ceiling function:

function ceiling(double x) as integer
ceiling = x \ 1
if x ceiling then
ceiling = ceiling + 1
end if
end function

and call it in your query:

SELECT ceiling([yournumfield])
FROM [yourtable]

Cheers,
Jason Lepack

On Apr 17, 11:52 am,
wrote:
I need to have numbers such as 1.00001 or 1.5, or 1.95843 all round up to the
number 2. Can anyone help me with this?
--
Mark Matzke- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Tried that, same result, so i simplified it a git and changed the result to
number values, true=.05, false=1 with no help. So i tried doing it the long
way:
=Iff([Text93][Text84],0.05,Iff([CDV][Text68],0.05,Iff([CHSI][Text70],0.05,Iff([Text39][Text82],0.05,Iff([DIGI][Text71],0.05,Iff([Text40][Text83],0.05,Iff([Text60][Text102],0.05,1)))))))
and still get the same error.
--
Mark Matzke


"Jason Lepack" wrote:

Yes, but you will have to use 'Yes' unless you're talking about a
check box.

On Apr 17, 3:10 pm,
wrote:
a b c d
1.9 -1.9 -2 2

Hey, I have a new question under forms, it is:

Subject: Long Iff Statement 4/17/2007 12:03 PM PST

Can I do an iff statment like:

=Iff([Text93][Text84] Or [CDV][ Text68] Or [CHSI][Text70] Or
[Text39][Text82] Or [DIGI][Text71] Or [Text40][Text83] Or
[Text60][Text102],Yes,No)

Where if any of these statements are true it will give a result of Yes, if
not then No?

--
Mark Matzke

--
Mark Matzke



"Jason Lepack" wrote:
SELECT
[CDV]/20 AS a,
-([CDV]/20) AS b,
int(-([CDV]/20)) AS c,
-int(-([CDV]/20)) AS d
FROM
[yourtable]

What does this query return?

On Apr 17, 2:14 pm,
wrote:
Number (Long Integer)
--
Mark Matzke

"Jason Lepack" wrote:
I retract my statement, a value of 38/20 (1.9) with my function
returns 1.9 (not 0 though...) but Allen Browne's works just fine.
What is the data type of [CDV]?

On Apr 17, 12:50 pm,
wrote:
GCDV: IIf(([CDV]/20)\1([CDV]/20),([CDV]/20)\1+1,([CDV]/20))
brought me to 0, as did the other two suggestions, so i tested my data and
when i do just
GCDV: [CDV]
I get 38, which is correct. my hope is that I can divide that number
(variable) by 20 and round up.

Thanks for your help, please let me know if you have any ideas.

--
Mark Matzke

"Jason Lepack" wrote:
SELECT iif([yournumfield]\1[yournumfield], [yournumfield]\1+1,
[yournumfield])
FROM [yourtable]

Note that it's \ not /

\ is integer division. 1.02 \ 1 = 1

Or you could create a ceiling function:

function ceiling(double x) as integer
ceiling = x \ 1
if x ceiling then
ceiling = ceiling + 1
end if
end function

and call it in your query:

SELECT ceiling([yournumfield])
FROM [yourtable]

Cheers,
Jason Lepack

On Apr 17, 11:52 am,
wrote:
I need to have numbers such as 1.00001 or 1.5, or 1.95843 all round up to the
number 2. Can anyone help me with this?
--
Mark Matzke- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I was using iff, not iif, duh... corrected and works!
--
Mark Matzke


" wrote:

Tried that, same result, so i simplified it a git and changed the result to
number values, true=.05, false=1 with no help. So i tried doing it the long
way:
=Iff([Text93][Text84],0.05,Iff([CDV][Text68],0.05,Iff([CHSI][Text70],0.05,Iff([Text39][Text82],0.05,Iff([DIGI][Text71],0.05,Iff([Text40][Text83],0.05,Iff([Text60][Text102],0.05,1)))))))
and still get the same error.
--
Mark Matzke


"Jason Lepack" wrote:

Yes, but you will have to use 'Yes' unless you're talking about a
check box.

On Apr 17, 3:10 pm,
wrote:
a b c d
1.9 -1.9 -2 2

Hey, I have a new question under forms, it is:

Subject: Long Iff Statement 4/17/2007 12:03 PM PST

Can I do an iff statment like:

=Iff([Text93][Text84] Or [CDV][ Text68] Or [CHSI][Text70] Or
[Text39][Text82] Or [DIGI][Text71] Or [Text40][Text83] Or
[Text60][Text102],Yes,No)

Where if any of these statements are true it will give a result of Yes, if
not then No?

--
Mark Matzke

--
Mark Matzke



"Jason Lepack" wrote:
SELECT
[CDV]/20 AS a,
-([CDV]/20) AS b,
int(-([CDV]/20)) AS c,
-int(-([CDV]/20)) AS d
FROM
[yourtable]

What does this query return?

On Apr 17, 2:14 pm,
wrote:
Number (Long Integer)
--
Mark Matzke

"Jason Lepack" wrote:
I retract my statement, a value of 38/20 (1.9) with my function
returns 1.9 (not 0 though...) but Allen Browne's works just fine.
What is the data type of [CDV]?

On Apr 17, 12:50 pm,
wrote:
GCDV: IIf(([CDV]/20)\1([CDV]/20),([CDV]/20)\1+1,([CDV]/20))
brought me to 0, as did the other two suggestions, so i tested my data and
when i do just
GCDV: [CDV]
I get 38, which is correct. my hope is that I can divide that number
(variable) by 20 and round up.

Thanks for your help, please let me know if you have any ideas.

--
Mark Matzke

"Jason Lepack" wrote:
SELECT iif([yournumfield]\1[yournumfield], [yournumfield]\1+1,
[yournumfield])
FROM [yourtable]

Note that it's \ not /

\ is integer division. 1.02 \ 1 = 1

Or you could create a ceiling function:

function ceiling(double x) as integer
ceiling = x \ 1
if x ceiling then
ceiling = ceiling + 1
end if
end function

and call it in your query:

SELECT ceiling([yournumfield])
FROM [yourtable]

Cheers,
Jason Lepack

On Apr 17, 11:52 am,
wrote:
I need to have numbers such as 1.00001 or 1.5, or 1.95843 all round up to the
number 2. Can anyone help me with this?
--
Mark Matzke- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Okay, so it works with literal values, and fails with your field.

That suggests that the data type is not being understood correctly, so you
could try typecasting:
- Int( - (CDbl([CDV]) / 20))

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

" wrote in
message
...
I too get 2 with
-Int(-(38/20))
and I get 38 with
GCDV: [CDV]
and I get 0 with
-Int(-([CDV]/20))

So I am not sure you can use a formula inside the int command, to solve
this
i made seperate querie to do the calculation of
zCDV:[CDV]/20
then modified this one to be
-Int(-[zCDV])
and it worked (gave a output of 2)

So all is good, though I would have liked to do it all in one querie. You
were a GREAT HELP, thank you very much!
--
Mark Matzke


"John Spencer" wrote:

Something is wrong then. I checked the following
-Int(-(38/20))
and got 2 as the result.

Check the formula again and check the value of CDV.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

" wrote
in
message ...
I used GCDV: -Int(-([CDV]/20))
The output was 0
CDV is 38 for this record and the output should be 2
Any suggestions?
--
Mark Matzke


"Allen Browne" wrote:

In a query, type this expression into the Field row:
- Int( - [MyField])
replacing MyField with the name of your field.

1.01 will round up to 2, and -1.01 will round up to -1.
Use Fix() instead of Int() if you want a different result for
negatives.

If you want to write the values back to your table, change the query
into
an
Update query (Update on Query menu.)

"
wrote
in
message
...
I need to have numbers such as 1.00001 or 1.5, or 1.95843 all round
up
to
the
number 2. Can anyone help me with this?

I need exactly the same solution
Did you mange anything?
Thanks

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com