Round Up In Queries

[mmatzke]

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?

 

[Allen Browne]

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[KARL DEWEY]

Try this ---
IIf([YourField]>Int([YourField]),Int([YourField])+1,[YourField])

 

[Jason Lepack]

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

 

[mmatzke]

Here is what I typed:
GCDV: IIf([CDV]/20>Int([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

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

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?

 

[mmatzke]

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

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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?

 

[mmatzke]

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

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

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?

 

[John Spencer]

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
..

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

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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?

 

[mmatzke]

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

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
..

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

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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?

 

[Jason Lepack]

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]?

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

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

- Show quoted text -

 

[KARL DEWEY]

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

Here is what I typed:
GCDV: IIf([CDV]/20>Int([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

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

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?

 

[mmatzke]

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

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

Here is what I typed:
GCDV: IIf([CDV]/20>Int([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

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


:

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?

 

[mmatzke]

Number (Long Integer)
--
Mark Matzke

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]?

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

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, (e-mail address removed)
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?

- Show quoted text -

 

[Jason Lepack]

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?

Number (Long Integer)
--
Mark Matzke

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]?

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
:
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, (e-mail address removed)
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?

- Show quoted text -

 

[mmatzke]

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

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?

Number (Long Integer)
--
Mark Matzke

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, (e-mail address removed)
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.

:
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, (e-mail address removed)
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?

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Jason Lepack]

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

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

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?

Number (Long Integer)
--
Mark Matzke
:
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, (e-mail address removed)
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
:
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, (e-mail address removed)
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?

- Show quoted text -

 

[mmatzke]

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

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

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

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, (e-mail address removed)
Number (Long Integer)

:
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, (e-mail address removed)
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.

:
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, (e-mail address removed)
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?

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[mmatzke]

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

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

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

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



:
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, (e-mail address removed)
Number (Long Integer)
--
Mark Matzke

:
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, (e-mail address removed)
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

:
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, (e-mail address removed)
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 -

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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

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
..

in
message news:[email protected]...

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


:

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.)

"(e-mail address removed)" <[email protected]>
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?

 

[Anton]

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

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