How do I get a calculation to return an EVEN number in Access?

[Crazy Lady]

my calculation is:
(((w * l)*2)/1.8)*x
The answer MUST return an even number. I can do this in Excel, can't find a
way in Access.
Help!

 

[Nikos Yannacopoulos]

In what context are you trying to do this in Access? In a query form,
report, or what?

Where do w and x come from?

What do you want to do with the result?

Nikos

 

[Crazy Lady]

Sorry, I should have been more specific.

Yes, The calculation is in a query. W(idth) and Length) and x are
columns in a table. 1.8 is a standard distance in metres by which the first
part of the calculation is divided, but since the result MUST be an EVEN
number, it implies that 1.8 might have to be aqdjusted slightly in the real
world, but on paper I just need an even number result.

(((w * l)*2)/1.8)*x

In Excel it reads EVEN(((w * l)*2)/1.8)*x) and the result is an even number.

 

[Nikos Yannacopoulos]

I wasn't aware of function EVEN() in Excel, and I couldn't find
something similar in Access. The good news is it's fairly easy to replicate.

So, assuming the fields in the table (what you call "columns", which is
Excel terms really) are named:
Width
Length
xFactor

in your query design, put the cursor in the Field row (1st row) in the
first available field (column), and paste an expression like:

MyCalc: 2*((((([Width] * [Length])*2)/1.8)*[xFactor])\2)+
IIf((((([Width] * [Length])*2)/1.8)*[xFactor]) Mod 2>0,2,0)

Watch out for wrapping in your newsreader, this is all in one line! Make
sure you substitute my assumed field names with the actual ones. MyCalc
is just an alias, use whatever suits you.

HTH,
Nikos

 

[Crazy Lady]

No, Nikos that is not working. I'm afraid I slipped up with the calc in the
first place, it's (((w + l)*2)/1.8)* x.

Sorry, that's because my brain in hurting by now.

I'm not sure if this would make any difference to your calc, but as it is it
comes back asking me for a parameter for the field that the calculation is in
when I try to run the query. I just need an even number which can then be
used in further calculations (which are working) in other queries.

What does Mod do? True to "help", "help" doesn't tell you half the things
you need to know and assumes that you know most things.

Thanks for your help so far, I'm sure we're heading in the right direction.

I wasn't aware of function EVEN() in Excel, and I couldn't find
something similar in Access. The good news is it's fairly easy to replicate.

So, assuming the fields in the table (what you call "columns", which is
Excel terms really) are named:
Width
Length
xFactor

in your query design, put the cursor in the Field row (1st row) in the
first available field (column), and paste an expression like:

MyCalc: 2*((((([Width] * [Length])*2)/1.8)*[xFactor])\2)+
IIf((((([Width] * [Length])*2)/1.8)*[xFactor]) Mod 2>0,2,0)

Watch out for wrapping in your newsreader, this is all in one line! Make
sure you substitute my assumed field names with the actual ones. MyCalc
is just an alias, use whatever suits you.

HTH,
Nikos

Sorry, I should have been more specific.

Yes, The calculation is in a query. W(idth) and Length) and x are
columns in a table. 1.8 is a standard distance in metres by which the first
part of the calculation is divided, but since the result MUST be an EVEN
number, it implies that 1.8 might have to be aqdjusted slightly in the real
world, but on paper I just need an even number result.

(((w * l)*2)/1.8)*x

In Excel it reads EVEN(((w * l)*2)/1.8)*x) and the result is an even number.


:

 

[George Nicholson]

The Mod statement returns the remainder after a division
9 Mod 2 returns 1 (an uneven number)
10 Mod 2 returns 0 (an even number)
However, it rounds its arguments to integers (whole numbers) before it
calulates remainders. This can cause problems if you don't take it into
account when working with decimal values.

If you are in the market for a user defined function to assist you:
*******************************************
Public Function RoundUpDown(varNumber As Variant, varDelta As Variant) As
Variant
'--------------------
'Name: RoundDelta (Function)
'Purpose: round varNumber to varDelta, up or down
'Inputs: varNumber = number to round
' varDelta = rounding precision
' +varDelta = rounds UP
' -varDelta = rounds DOWN
'Example: RoundUpDown(5.12,+0.25) = 5.25
' RoundUpDown(5.12,-0.25) = 5.00
'Output: varNumber rounded UP/DOWN
'Source: Access Advisor Magazine Tips 9/2001
'----------------------------
On Error Resume Next

Dim varTemp As Variant
varTemp = CDec(varNumber / varDelta)
If Int(varTemp) = varTemp Then
RoundUpDown = varNumber
Else
RoundUpDown = Int(((varNumber + (2 * varDelta)) / varDelta) - 1)*
varDelta
End If
End Function
**********************************************************

This function would go in a general code module
Then, in your query: MyField=RoundUpDown(YourCalculation, 2)
I have a similar function that could be used to round to the nearest even
number, but since EVEN rounds up, and you have used that in the past, this
seemed more appropriate.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

No, Nikos that is not working. I'm afraid I slipped up with the calc in
the
first place, it's (((w + l)*2)/1.8)* x.

Sorry, that's because my brain in hurting by now.

I'm not sure if this would make any difference to your calc, but as it is
it
comes back asking me for a parameter for the field that the calculation is
in
when I try to run the query. I just need an even number which can then be
used in further calculations (which are working) in other queries.

What does Mod do? True to "help", "help" doesn't tell you half the things
you need to know and assumes that you know most things.

Thanks for your help so far, I'm sure we're heading in the right
direction.

I wasn't aware of function EVEN() in Excel, and I couldn't find
something similar in Access. The good news is it's fairly easy to
replicate.

So, assuming the fields in the table (what you call "columns", which is
Excel terms really) are named:
Width
Length
xFactor

in your query design, put the cursor in the Field row (1st row) in the
first available field (column), and paste an expression like:

MyCalc: 2*((((([Width] * [Length])*2)/1.8)*[xFactor])\2)+
IIf((((([Width] * [Length])*2)/1.8)*[xFactor]) Mod 2>0,2,0)

Watch out for wrapping in your newsreader, this is all in one line! Make
sure you substitute my assumed field names with the actual ones. MyCalc
is just an alias, use whatever suits you.

HTH,
Nikos

Sorry, I should have been more specific.

Yes, The calculation is in a query. W(idth) and Length) and x are
columns in a table. 1.8 is a standard distance in metres by which the
first
part of the calculation is divided, but since the result MUST be an
EVEN
number, it implies that 1.8 might have to be aqdjusted slightly in the
real
world, but on paper I just need an even number result.

(((w * l)*2)/1.8)*x

In Excel it reads EVEN(((w * l)*2)/1.8)*x) and the result is an even
number.


:


In what context are you trying to do this in Access? In a query form,
report, or what?

Where do w and x come from?

What do you want to do with the result?

Nikos

Crazy Lady wrote:

my calculation is:
(((w * l)*2)/1.8)*x
The answer MUST return an even number. I can do this in Excel, can't
find a
way in Access.
Help!

 

[Nikos Yannacopoulos]

No, Nikos that is not working. I'm afraid I slipped up with the calc in the
first place, it's (((w + l)*2)/1.8)* x.

But that's the same expression you gave me in your previous post!

Sorry, that's because my brain in hurting by now.

I'm not sure if this would make any difference to your calc, but as it is it
comes back asking me for a parameter for the field that the calculation is in
when I try to run the query.

Probably a non-matching/misspelt field name. Did you substitute my
assumed field names in the expression with the actual ones in the table?
If yes, check the spelling. If still getting the error, post the exact
message, and the actual field names in your table.

I just need an even number which can then be
used in further calculations (which are working) in other queries.

What does Mod do?

Already replied by George. By the way, the user-defined function he
proposed is probably the best way to do it, I just thought you might not
feel very comfortable with code. If you want to give it a shot (which I
would suggest), I'll give you the exact expression to employ it in your
calculated field, provided I have your field names.

 

[Crazy Lady]

Thanks Nikos, My full calc looks like this:

Stds(([STRUCTURES]![W (m)] + [STRUCTURES]![L
(m)])*2)/1.8)*[STRUCTURES]![Lifts]

Your help is much appreciated. I must go on a course!

Crazy Lady

 

[Nikos Yannacopoulos]

OK... so you have pasted George's function in a general module, eh? Now
go back to the query design, and add your calculated field like:

EvenStds: RoundUpDown(((([W (m)]+[L (m)])*2)/1.8)*[Lifts], 2)

This should do the trick.

HTH,
Nikos

Thanks Nikos, My full calc looks like this:

Stds(([STRUCTURES]![W (m)] + [STRUCTURES]![L
(m)])*2)/1.8)*[STRUCTURES]![Lifts]

Your help is much appreciated. I must go on a course!

Crazy Lady


:

But that's the same expression you gave me in your previous post!




Probably a non-matching/misspelt field name. Did you substitute my
assumed field names in the expression with the actual ones in the table?
If yes, check the spelling. If still getting the error, post the exact
message, and the actual field names in your table.




Already replied by George. By the way, the user-defined function he
proposed is probably the best way to do it, I just thought you might not
feel very comfortable with code. If you want to give it a shot (which I
would suggest), I'll give you the exact expression to employ it in your
calculated field, provided I have your field names.

 

[Michael Bednarek]

Thanks Nikos, My full calc looks like this:

Stds(([STRUCTURES]![W (m)] + [STRUCTURES]![L
(m)])*2)/1.8)*[STRUCTURES]![Lifts]

Your help is much appreciated. I must go on a course!

Crazy Lady

[snip]

Try this:
(((([STRUCTURES]![W (m)] + [STRUCTURES]![L (m)])*2)/1.8)*[STRUCTURES]![Lifts] + 1) \ 2 * 2

Note the "\" operator.

 

[Crazy Lady]

Thanks George & Nikos. I didn't realise this site was here before. I've
struggled no end in getting as far as I have, but I guess there's more
satisfaction in working things out for yourself. I'm sure you haven't heard
the last from me!

OK... so you have pasted George's function in a general module, eh? Now
go back to the query design, and add your calculated field like:

EvenStds: RoundUpDown(((([W (m)]+[L (m)])*2)/1.8)*[Lifts], 2)

This should do the trick.

HTH,
Nikos

Thanks Nikos, My full calc looks like this:

Stds(([STRUCTURES]![W (m)] + [STRUCTURES]![L
(m)])*2)/1.8)*[STRUCTURES]![Lifts]

Your help is much appreciated. I must go on a course!

Crazy Lady


:

No, Nikos that is not working. I'm afraid I slipped up with the calc in the
first place, it's (((w + l)*2)/1.8)* x.

But that's the same expression you gave me in your previous post!



Sorry, that's because my brain in hurting by now.

I'm not sure if this would make any difference to your calc, but as it is it
comes back asking me for a parameter for the field that the calculation is in
when I try to run the query.

Probably a non-matching/misspelt field name. Did you substitute my
assumed field names in the expression with the actual ones in the table?
If yes, check the spelling. If still getting the error, post the exact
message, and the actual field names in your table.



I just need an even number which can then be
used in further calculations (which are working) in other queries.

What does Mod do?

Already replied by George. By the way, the user-defined function he
proposed is probably the best way to do it, I just thought you might not
feel very comfortable with code. If you want to give it a shot (which I
would suggest), I'll give you the exact expression to employ it in your
calculated field, provided I have your field names.

 

[LGC]

If you are in the market for a user defined function to assist you:

*******************************************
Public Function RoundUpDown(varNumber As Variant, varDelta As Variant) As
Variant
'--------------------
'Name: RoundDelta (Function)
'Purpose: round varNumber to varDelta, up or down
'Inputs: varNumber = number to round
' varDelta = rounding precision
' +varDelta = rounds UP
' -varDelta = rounds DOWN
'Example: RoundUpDown(5.12,+0.25) = 5.25
' RoundUpDown(5.12,-0.25) = 5.00
'Output: varNumber rounded UP/DOWN
'Source: Access Advisor Magazine Tips 9/2001
'----------------------------
On Error Resume Next

Dim varTemp As Variant
varTemp = CDec(varNumber / varDelta)
If Int(varTemp) = varTemp Then
RoundUpDown = varNumber
Else
RoundUpDown = Int(((varNumber + (2 * varDelta)) / varDelta) - 1)*
varDelta
End If
End Function
**********************************************************

Or....

To round down:

Public Function EvenDown(varNum) As Variant

'Round down to nearest even number
EvenDown = varNum and (-2)

End Function

To round up:

Public Function EvenUp(varNum) As Variant

'Round up to nearest even number
EvenUp = (varNum + 1) and (-2)

End Function

(This will not work for negative numbers.)

LGC

 

[Crazy Lady]

Hi George,

this works well and it's tidy because some of the calculations have IIf
statements of their own.

However, 1) what If I want the RoundUp and RoundDown Functions to be
separate? and 2) what If I want subsequent calculations carried out on the
Rounded value not the background value that access knows is the real value.

Crazy Lady

The Mod statement returns the remainder after a division
9 Mod 2 returns 1 (an uneven number)
10 Mod 2 returns 0 (an even number)
However, it rounds its arguments to integers (whole numbers) before it
calulates remainders. This can cause problems if you don't take it into
account when working with decimal values.

If you are in the market for a user defined function to assist you:
*******************************************
Public Function RoundUpDown(varNumber As Variant, varDelta As Variant) As
Variant
'--------------------
'Name: RoundDelta (Function)
'Purpose: round varNumber to varDelta, up or down
'Inputs: varNumber = number to round
' varDelta = rounding precision
' +varDelta = rounds UP
' -varDelta = rounds DOWN
'Example: RoundUpDown(5.12,+0.25) = 5.25
' RoundUpDown(5.12,-0.25) = 5.00
'Output: varNumber rounded UP/DOWN
'Source: Access Advisor Magazine Tips 9/2001
'----------------------------
On Error Resume Next

Dim varTemp As Variant
varTemp = CDec(varNumber / varDelta)
If Int(varTemp) = varTemp Then
RoundUpDown = varNumber
Else
RoundUpDown = Int(((varNumber + (2 * varDelta)) / varDelta) - 1)*
varDelta
End If
End Function
**********************************************************

This function would go in a general code module
Then, in your query: MyField=RoundUpDown(YourCalculation, 2)
I have a similar function that could be used to round to the nearest even
number, but since EVEN rounds up, and you have used that in the past, this
seemed more appropriate.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

No, Nikos that is not working. I'm afraid I slipped up with the calc in
the
first place, it's (((w + l)*2)/1.8)* x.

Sorry, that's because my brain in hurting by now.

I'm not sure if this would make any difference to your calc, but as it is
it
comes back asking me for a parameter for the field that the calculation is
in
when I try to run the query. I just need an even number which can then be
used in further calculations (which are working) in other queries.

What does Mod do? True to "help", "help" doesn't tell you half the things
you need to know and assumes that you know most things.

Thanks for your help so far, I'm sure we're heading in the right
direction.

I wasn't aware of function EVEN() in Excel, and I couldn't find
something similar in Access. The good news is it's fairly easy to
replicate.

So, assuming the fields in the table (what you call "columns", which is
Excel terms really) are named:
Width
Length
xFactor

in your query design, put the cursor in the Field row (1st row) in the
first available field (column), and paste an expression like:

MyCalc: 2*((((([Width] * [Length])*2)/1.8)*[xFactor])\2)+
IIf((((([Width] * [Length])*2)/1.8)*[xFactor]) Mod 2>0,2,0)

Watch out for wrapping in your newsreader, this is all in one line! Make
sure you substitute my assumed field names with the actual ones. MyCalc
is just an alias, use whatever suits you.

HTH,
Nikos

Crazy Lady wrote:
Sorry, I should have been more specific.

Yes, The calculation is in a query. W(idth) and Length) and x are
columns in a table. 1.8 is a standard distance in metres by which the
first
part of the calculation is divided, but since the result MUST be an
EVEN
number, it implies that 1.8 might have to be aqdjusted slightly in the
real
world, but on paper I just need an even number result.

(((w * l)*2)/1.8)*x

In Excel it reads EVEN(((w * l)*2)/1.8)*x) and the result is an even
number.


:


In what context are you trying to do this in Access? In a query form,
report, or what?

Where do w and x come from?

What do you want to do with the result?

Nikos

Crazy Lady wrote:

my calculation is:
(((w * l)*2)/1.8)*x
The answer MUST return an even number. I can do this in Excel, can't
find a
way in Access.
Help!