Help with calculation in query!!!

  • Thread starter Thread starter Cam
  • Start date Start date
C

Cam

Hello,

I have the this Excel formula I used to calculate the lead time in days and
would like to transfer into Access query. The Excel formula looks at three
different option and calculate the result based on the if condition.

=(IF(AND(O6=1,P6=2),(((H6+I6)/60)/L6)+(J6*(1-M6)),
IF(AND(O6=2,P6=1),(((H6+I6)/60)/K6)+(J6*(1-M6)),
IF(AND(O6=1,P6=1),(((H6+I6)/60)*Q6+(J6*(1-M6))), "Did not meet defined
criteria"))))/N6

Can someone help me make it work in Access? Thanks
 
I would do exactly the same thing in Access that I would have done in Excel.
Create a small user-defined function that accepts arguements from O6, P6, H6,
I6, M6, K6, N6, and Q6. The function would return the appropriate value.

Cells and query columns are not the place to store your complex equations.
Use a function that can be saved in a module of "Business Functions". When a
pointy haired boss changes the calculations, you should be able to go to one
place to review the code and make changes and comments.

It looks like your expression might return a numeric or a string value. I
don't think this is good practice. You should return a number or null. The
null can be formatted in a control to display "did not meet...."

To get you started, a function might look like
Public Function MyCalc( O as Integer, P as Integer, _
H as Double, I as Double, L as Double, M as Double, _
Q as Double) as Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn as Variant
If O = 1 AND P = 2 Then
varReturn = (((H+I)/0)/L)+(J*(1-M))/N
Else
If O=2 AND P=1 Then

' add more elses or whatever

End If

MyCalc = varReturn
End Function

You would call this function in the same way you would call any other
function.
 
Duane,

I am not good with function and code, where would you put the function code
in query?
 
That's the beauty of the function. It is created in a standard module. You
would create a new module, type in the function using the start of the code I
wrote, and then use the function in your query.

You query would have something like:
NewColumnName: MyCalc([FieldO], [FieldP],....)
 
Duane,

What would the letters represent? my field name?

Duane Hookom said:
That's the beauty of the function. It is created in a standard module. You
would create a new module, type in the function using the start of the code I
wrote, and then use the function in your query.

You query would have something like:
NewColumnName: MyCalc([FieldO], [FieldP],....)
--
Duane Hookom
Microsoft Access MVP


Cam said:
Duane,

I am not good with function and code, where would you put the function code
in query?
Click to expand...
Click to expand...
 
Duane,

I put in the following code and it returns the error below.

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
Else
If O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
Else
If O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * Q + (J * (1 - M))) / N

End If

MyCalc = varReturn

End Function


"Compile error:
Block If without End If"

Cam said:
Duane,

What would the letters represent? my field name?

Duane Hookom said:
That's the beauty of the function. It is created in a standard module. You
would create a new module, type in the function using the start of the code I
wrote, and then use the function in your query.

You query would have something like:
NewColumnName: MyCalc([FieldO], [FieldP],....)
--
Duane Hookom
Microsoft Access MVP


Cam said:
Duane,

I am not good with function and code, where would you put the function code
in query?

:

I would do exactly the same thing in Access that I would have done in Excel.
Create a small user-defined function that accepts arguements from O6, P6, H6,
I6, M6, K6, N6, and Q6. The function would return the appropriate value.

Cells and query columns are not the place to store your complex equations.
Use a function that can be saved in a module of "Business Functions". When a
pointy haired boss changes the calculations, you should be able to go to one
place to review the code and make changes and comments.

It looks like your expression might return a numeric or a string value. I
don't think this is good practice. You should return a number or null. The
null can be formatted in a control to display "did not meet...."

To get you started, a function might look like
Public Function MyCalc( O as Integer, P as Integer, _
H as Double, I as Double, L as Double, M as Double, _
Q as Double) as Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn as Variant
If O = 1 AND P = 2 Then
varReturn = (((H+I)/0)/L)+(J*(1-M))/N
Else
If O=2 AND P=1 Then

' add more elses or whatever

End If

MyCalc = varReturn
End Function

You would call this function in the same way you would call any other
function.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have the this Excel formula I used to calculate the lead time in days and
would like to transfer into Access query. The Excel formula looks at three
different option and calculate the result based on the if condition.

=(IF(AND(O6=1,P6=2),(((H6+I6)/60)/L6)+(J6*(1-M6)),
IF(AND(O6=2,P6=1),(((H6+I6)/60)/K6)+(J6*(1-M6)),
IF(AND(O6=1,P6=1),(((H6+I6)/60)*Q6+(J6*(1-M6))), "Did not meet defined
criteria"))))/N6

Can someone help me make it work in Access? Thanks
Click to expand...
Click to expand...
Click to expand...
 
For every If you must have an End If. You might want to use ElseIf in place
of Else and If.
--
Duane Hookom
Microsoft Access MVP


Cam said:
Duane,

I put in the following code and it returns the error below.

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
Else
If O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
Else
If O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * Q + (J * (1 - M))) / N

End If

MyCalc = varReturn

End Function


"Compile error:
Block If without End If"

Cam said:
Duane,

What would the letters represent? my field name?

Duane Hookom said:
That's the beauty of the function. It is created in a standard module. You
would create a new module, type in the function using the start of the code I
wrote, and then use the function in your query.

You query would have something like:
NewColumnName: MyCalc([FieldO], [FieldP],....)
--
Duane Hookom
Microsoft Access MVP


:

Duane,

I am not good with function and code, where would you put the function code
in query?

:

I would do exactly the same thing in Access that I would have done in Excel.
Create a small user-defined function that accepts arguements from O6, P6, H6,
I6, M6, K6, N6, and Q6. The function would return the appropriate value.

Cells and query columns are not the place to store your complex equations.
Use a function that can be saved in a module of "Business Functions". When a
pointy haired boss changes the calculations, you should be able to go to one
place to review the code and make changes and comments.

It looks like your expression might return a numeric or a string value. I
don't think this is good practice. You should return a number or null. The
null can be formatted in a control to display "did not meet...."

To get you started, a function might look like
Public Function MyCalc( O as Integer, P as Integer, _
H as Double, I as Double, L as Double, M as Double, _
Q as Double) as Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn as Variant
If O = 1 AND P = 2 Then
varReturn = (((H+I)/0)/L)+(J*(1-M))/N
Else
If O=2 AND P=1 Then

' add more elses or whatever

End If

MyCalc = varReturn
End Function

You would call this function in the same way you would call any other
function.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have the this Excel formula I used to calculate the lead time in days and
would like to transfer into Access query. The Excel formula looks at three
different option and calculate the result based on the if condition.

=(IF(AND(O6=1,P6=2),(((H6+I6)/60)/L6)+(J6*(1-M6)),
IF(AND(O6=2,P6=1),(((H6+I6)/60)/K6)+(J6*(1-M6)),
IF(AND(O6=1,P6=1),(((H6+I6)/60)*Q6+(J6*(1-M6))), "Did not meet defined
criteria"))))/N6

Can someone help me make it work in Access? Thanks
Click to expand...
Click to expand...
Click to expand...
 
Duane,

I change the code below to your sugguestion and it didn't error out. I need
help with the result for the first two scenerios as it returns "#Error". Does
this mean the formula is wrong?

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
ElseIf O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
ElseIf O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * (Q) + (J * (1 - M))) / N
End If

MyCalc = varReturn

End Function


Duane Hookom said:
For every If you must have an End If. You might want to use ElseIf in place
of Else and If.
--
Duane Hookom
Microsoft Access MVP


Cam said:
Duane,

I put in the following code and it returns the error below.

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
Else
If O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
Else
If O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * Q + (J * (1 - M))) / N

End If

MyCalc = varReturn

End Function


"Compile error:
Block If without End If"

Cam said:
Duane,

What would the letters represent? my field name?

:

That's the beauty of the function. It is created in a standard module. You
would create a new module, type in the function using the start of the code I
wrote, and then use the function in your query.

You query would have something like:
NewColumnName: MyCalc([FieldO], [FieldP],....)
--
Duane Hookom
Microsoft Access MVP


:

Duane,

I am not good with function and code, where would you put the function code
in query?

:

I would do exactly the same thing in Access that I would have done in Excel.
Create a small user-defined function that accepts arguements from O6, P6, H6,
I6, M6, K6, N6, and Q6. The function would return the appropriate value.

Cells and query columns are not the place to store your complex equations.
Use a function that can be saved in a module of "Business Functions". When a
pointy haired boss changes the calculations, you should be able to go to one
place to review the code and make changes and comments.

It looks like your expression might return a numeric or a string value. I
don't think this is good practice. You should return a number or null. The
null can be formatted in a control to display "did not meet...."

To get you started, a function might look like
Public Function MyCalc( O as Integer, P as Integer, _
H as Double, I as Double, L as Double, M as Double, _
Q as Double) as Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn as Variant
If O = 1 AND P = 2 Then
varReturn = (((H+I)/0)/L)+(J*(1-M))/N
Else
If O=2 AND P=1 Then

' add more elses or whatever

End If

MyCalc = varReturn
End Function

You would call this function in the same way you would call any other
function.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have the this Excel formula I used to calculate the lead time in days and
would like to transfer into Access query. The Excel formula looks at three
different option and calculate the result based on the if condition.

=(IF(AND(O6=1,P6=2),(((H6+I6)/60)/L6)+(J6*(1-M6)),
IF(AND(O6=2,P6=1),(((H6+I6)/60)/K6)+(J6*(1-M6)),
IF(AND(O6=1,P6=1),(((H6+I6)/60)*Q6+(J6*(1-M6))), "Did not meet defined
criteria"))))/N6

Can someone help me make it work in Access? Thanks
Click to expand...
Click to expand...
Click to expand...
 
It looks ok to me, although if L, K, Or N are ever zero you will get a divide
by zero error.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Duane,

I change the code below to your sugguestion and it didn't error out. I need
help with the result for the first two scenerios as it returns "#Error". Does
this mean the formula is wrong?

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
ElseIf O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
ElseIf O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * (Q) + (J * (1 - M))) / N
End If

MyCalc = varReturn

End Function


Duane Hookom said:
For every If you must have an End If. You might want to use ElseIf in place
of Else and If.
--
Duane Hookom
Microsoft Access MVP


Cam said:
Duane,

I put in the following code and it returns the error below.

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
Else
If O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
Else
If O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * Q + (J * (1 - M))) / N

End If

MyCalc = varReturn

End Function


"Compile error:
Block If without End If"

:

Duane,

What would the letters represent? my field name?

:

That's the beauty of the function. It is created in a standard module. You
would create a new module, type in the function using the start of the code I
wrote, and then use the function in your query.

You query would have something like:
NewColumnName: MyCalc([FieldO], [FieldP],....)
--
Duane Hookom
Microsoft Access MVP


:

Duane,

I am not good with function and code, where would you put the function code
in query?

:

I would do exactly the same thing in Access that I would have done in Excel.
Create a small user-defined function that accepts arguements from O6, P6, H6,
I6, M6, K6, N6, and Q6. The function would return the appropriate value.

Cells and query columns are not the place to store your complex equations.
Use a function that can be saved in a module of "Business Functions". When a
pointy haired boss changes the calculations, you should be able to go to one
place to review the code and make changes and comments.

It looks like your expression might return a numeric or a string value. I
don't think this is good practice. You should return a number or null. The
null can be formatted in a control to display "did not meet...."

To get you started, a function might look like
Public Function MyCalc( O as Integer, P as Integer, _
H as Double, I as Double, L as Double, M as Double, _
Q as Double) as Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn as Variant
If O = 1 AND P = 2 Then
varReturn = (((H+I)/0)/L)+(J*(1-M))/N
Else
If O=2 AND P=1 Then

' add more elses or whatever

End If

MyCalc = varReturn
End Function

You would call this function in the same way you would call any other
function.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have the this Excel formula I used to calculate the lead time in days and
would like to transfer into Access query. The Excel formula looks at three
different option and calculate the result based on the if condition.

=(IF(AND(O6=1,P6=2),(((H6+I6)/60)/L6)+(J6*(1-M6)),
IF(AND(O6=2,P6=1),(((H6+I6)/60)/K6)+(J6*(1-M6)),
IF(AND(O6=1,P6=1),(((H6+I6)/60)*Q6+(J6*(1-M6))), "Did not meet defined
criteria"))))/N6

Can someone help me make it work in Access? Thanks
Click to expand...
Click to expand...
Click to expand...
 
John,

There is no zero value for K (decimal) and N (whole number), but L has some
decimal and zero in the data. How to I format the L field to return 1 if it
is equal zero? Thanks

John Spencer said:
It looks ok to me, although if L, K, Or N are ever zero you will get a divide
by zero error.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Duane,

I change the code below to your sugguestion and it didn't error out. I need
help with the result for the first two scenerios as it returns "#Error". Does
this mean the formula is wrong?

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
ElseIf O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
ElseIf O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * (Q) + (J * (1 - M))) / N
End If

MyCalc = varReturn

End Function


Duane Hookom said:
For every If you must have an End If. You might want to use ElseIf in place
of Else and If.
--
Duane Hookom
Microsoft Access MVP


:

Duane,

I put in the following code and it returns the error below.

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
Else
If O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
Else
If O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * Q + (J * (1 - M))) / N

End If

MyCalc = varReturn

End Function


"Compile error:
Block If without End If"

:

Duane,

What would the letters represent? my field name?

:

That's the beauty of the function. It is created in a standard module. You
would create a new module, type in the function using the start of the code I
wrote, and then use the function in your query.

You query would have something like:
NewColumnName: MyCalc([FieldO], [FieldP],....)
--
Duane Hookom
Microsoft Access MVP


:

Duane,

I am not good with function and code, where would you put the function code
in query?

:

I would do exactly the same thing in Access that I would have done in Excel.
Create a small user-defined function that accepts arguements from O6, P6, H6,
I6, M6, K6, N6, and Q6. The function would return the appropriate value.

Cells and query columns are not the place to store your complex equations.
Use a function that can be saved in a module of "Business Functions". When a
pointy haired boss changes the calculations, you should be able to go to one
place to review the code and make changes and comments.

It looks like your expression might return a numeric or a string value. I
don't think this is good practice. You should return a number or null. The
null can be formatted in a control to display "did not meet...."

To get you started, a function might look like
Public Function MyCalc( O as Integer, P as Integer, _
H as Double, I as Double, L as Double, M as Double, _
Q as Double) as Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn as Variant
If O = 1 AND P = 2 Then
varReturn = (((H+I)/0)/L)+(J*(1-M))/N
Else
If O=2 AND P=1 Then

' add more elses or whatever

End If

MyCalc = varReturn
End Function

You would call this function in the same way you would call any other
function.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have the this Excel formula I used to calculate the lead time in days and
would like to transfer into Access query. The Excel formula looks at three
different option and calculate the result based on the if condition.

=(IF(AND(O6=1,P6=2),(((H6+I6)/60)/L6)+(J6*(1-M6)),
IF(AND(O6=2,P6=1),(((H6+I6)/60)/K6)+(J6*(1-M6)),
IF(AND(O6=1,P6=1),(((H6+I6)/60)*Q6+(J6*(1-M6))), "Did not meet defined
criteria"))))/N6

Can someone help me make it work in Access? Thanks
Click to expand...
Click to expand...
Click to expand...
 
varReturn = (((H + I) / 60) / IIF(L=0,1,L)) + (J * (1 - M)) / N

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
John,

There is no zero value for K (decimal) and N (whole number), but L has some
decimal and zero in the data. How to I format the L field to return 1 if it
is equal zero? Thanks

John Spencer said:
It looks ok to me, although if L, K, Or N are ever zero you will get a divide
by zero error.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Duane,

I change the code below to your sugguestion and it didn't error out. I need
help with the result for the first two scenerios as it returns "#Error". Does
this mean the formula is wrong?

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
ElseIf O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
ElseIf O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * (Q) + (J * (1 - M))) / N
End If

MyCalc = varReturn

End Function


:

For every If you must have an End If. You might want to use ElseIf in place
of Else and If.
--
Duane Hookom
Microsoft Access MVP


:

Duane,

I put in the following code and it returns the error below.

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
Else
If O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
Else
If O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * Q + (J * (1 - M))) / N

End If

MyCalc = varReturn

End Function


"Compile error:
Block If without End If"

:

Duane,

What would the letters represent? my field name?

:

That's the beauty of the function. It is created in a standard module. You
would create a new module, type in the function using the start of the code I
wrote, and then use the function in your query.

You query would have something like:
NewColumnName: MyCalc([FieldO], [FieldP],....)
--
Duane Hookom
Microsoft Access MVP


:

Duane,

I am not good with function and code, where would you put the function code
in query?

:

I would do exactly the same thing in Access that I would have done in Excel.
Create a small user-defined function that accepts arguements from O6, P6, H6,
I6, M6, K6, N6, and Q6. The function would return the appropriate value.

Cells and query columns are not the place to store your complex equations.
Use a function that can be saved in a module of "Business Functions". When a
pointy haired boss changes the calculations, you should be able to go to one
place to review the code and make changes and comments.

It looks like your expression might return a numeric or a string value. I
don't think this is good practice. You should return a number or null. The
null can be formatted in a control to display "did not meet...."

To get you started, a function might look like
Public Function MyCalc( O as Integer, P as Integer, _
H as Double, I as Double, L as Double, M as Double, _
Q as Double) as Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn as Variant
If O = 1 AND P = 2 Then
varReturn = (((H+I)/0)/L)+(J*(1-M))/N
Else
If O=2 AND P=1 Then

' add more elses or whatever

End If

MyCalc = varReturn
End Function

You would call this function in the same way you would call any other
function.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have the this Excel formula I used to calculate the lead time in days and
would like to transfer into Access query. The Excel formula looks at three
different option and calculate the result based on the if condition.

=(IF(AND(O6=1,P6=2),(((H6+I6)/60)/L6)+(J6*(1-M6)),
IF(AND(O6=2,P6=1),(((H6+I6)/60)/K6)+(J6*(1-M6)),
IF(AND(O6=1,P6=1),(((H6+I6)/60)*Q6+(J6*(1-M6))), "Did not meet defined
criteria"))))/N6

Can someone help me make it work in Access? Thanks
Click to expand...
Click to expand...
Click to expand...
 
John,

I tried your formula and it still gave me the same #Error result. I was
wondering whether my formula referenced is incorrect, but I couldn't find
any. Could it be the Integer and Double format causing it to not reference
correctly? Thanks

John Spencer said:
varReturn = (((H + I) / 60) / IIF(L=0,1,L)) + (J * (1 - M)) / N

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
John,

There is no zero value for K (decimal) and N (whole number), but L has some
decimal and zero in the data. How to I format the L field to return 1 if it
is equal zero? Thanks

John Spencer said:
It looks ok to me, although if L, K, Or N are ever zero you will get a divide
by zero error.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Cam wrote:
Duane,

I change the code below to your sugguestion and it didn't error out. I need
help with the result for the first two scenerios as it returns "#Error". Does
this mean the formula is wrong?

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
ElseIf O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
ElseIf O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * (Q) + (J * (1 - M))) / N
End If

MyCalc = varReturn

End Function


:

For every If you must have an End If. You might want to use ElseIf in place
of Else and If.
--
Duane Hookom
Microsoft Access MVP


:

Duane,

I put in the following code and it returns the error below.

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
Else
If O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
Else
If O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * Q + (J * (1 - M))) / N

End If

MyCalc = varReturn

End Function


"Compile error:
Block If without End If"

:

Duane,

What would the letters represent? my field name?

:

That's the beauty of the function. It is created in a standard module. You
would create a new module, type in the function using the start of the code I
wrote, and then use the function in your query.

You query would have something like:
NewColumnName: MyCalc([FieldO], [FieldP],....)
--
Duane Hookom
Microsoft Access MVP


:

Duane,

I am not good with function and code, where would you put the function code
in query?

:

I would do exactly the same thing in Access that I would have done in Excel.
Create a small user-defined function that accepts arguements from O6, P6, H6,
I6, M6, K6, N6, and Q6. The function would return the appropriate value.

Cells and query columns are not the place to store your complex equations.
Use a function that can be saved in a module of "Business Functions". When a
pointy haired boss changes the calculations, you should be able to go to one
place to review the code and make changes and comments.

It looks like your expression might return a numeric or a string value. I
don't think this is good practice. You should return a number or null. The
null can be formatted in a control to display "did not meet...."

To get you started, a function might look like
Public Function MyCalc( O as Integer, P as Integer, _
H as Double, I as Double, L as Double, M as Double, _
Q as Double) as Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn as Variant
If O = 1 AND P = 2 Then
varReturn = (((H+I)/0)/L)+(J*(1-M))/N
Else
If O=2 AND P=1 Then

' add more elses or whatever

End If

MyCalc = varReturn
End Function

You would call this function in the same way you would call any other
function.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have the this Excel formula I used to calculate the lead time in days and
would like to transfer into Access query. The Excel formula looks at three
different option and calculate the result based on the if condition.

=(IF(AND(O6=1,P6=2),(((H6+I6)/60)/L6)+(J6*(1-M6)),
IF(AND(O6=2,P6=1),(((H6+I6)/60)/K6)+(J6*(1-M6)),
IF(AND(O6=1,P6=1),(((H6+I6)/60)*Q6+(J6*(1-M6))), "Did not meet defined
criteria"))))/N6

Can someone help me make it work in Access? Thanks
Click to expand...
Click to expand...
Click to expand...
 
Please provide us with your current code as well as sample values for the
fields and desired results.

--
Duane Hookom
Microsoft Access MVP


Cam said:
John,

I tried your formula and it still gave me the same #Error result. I was
wondering whether my formula referenced is incorrect, but I couldn't find
any. Could it be the Integer and Double format causing it to not reference
correctly? Thanks

John Spencer said:
varReturn = (((H + I) / 60) / IIF(L=0,1,L)) + (J * (1 - M)) / N

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
John,

There is no zero value for K (decimal) and N (whole number), but L has some
decimal and zero in the data. How to I format the L field to return 1 if it
is equal zero? Thanks

:

It looks ok to me, although if L, K, Or N are ever zero you will get a divide
by zero error.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Cam wrote:
Duane,

I change the code below to your sugguestion and it didn't error out. I need
help with the result for the first two scenerios as it returns "#Error". Does
this mean the formula is wrong?

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
ElseIf O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
ElseIf O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * (Q) + (J * (1 - M))) / N
End If

MyCalc = varReturn

End Function


:

For every If you must have an End If. You might want to use ElseIf in place
of Else and If.
--
Duane Hookom
Microsoft Access MVP


:

Duane,

I put in the following code and it returns the error below.

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
Else
If O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
Else
If O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * Q + (J * (1 - M))) / N

End If

MyCalc = varReturn

End Function


"Compile error:
Block If without End If"

:

Duane,

What would the letters represent? my field name?

:

That's the beauty of the function. It is created in a standard module. You
would create a new module, type in the function using the start of the code I
wrote, and then use the function in your query.

You query would have something like:
NewColumnName: MyCalc([FieldO], [FieldP],....)
--
Duane Hookom
Microsoft Access MVP


:

Duane,

I am not good with function and code, where would you put the function code
in query?

:

I would do exactly the same thing in Access that I would have done in Excel.
Create a small user-defined function that accepts arguements from O6, P6, H6,
I6, M6, K6, N6, and Q6. The function would return the appropriate value.

Cells and query columns are not the place to store your complex equations.
Use a function that can be saved in a module of "Business Functions". When a
pointy haired boss changes the calculations, you should be able to go to one
place to review the code and make changes and comments.

It looks like your expression might return a numeric or a string value. I
don't think this is good practice. You should return a number or null. The
null can be formatted in a control to display "did not meet...."

To get you started, a function might look like
Public Function MyCalc( O as Integer, P as Integer, _
H as Double, I as Double, L as Double, M as Double, _
Q as Double) as Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn as Variant
If O = 1 AND P = 2 Then
varReturn = (((H+I)/0)/L)+(J*(1-M))/N
Else
If O=2 AND P=1 Then

' add more elses or whatever

End If

MyCalc = varReturn
End Function

You would call this function in the same way you would call any other
function.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have the this Excel formula I used to calculate the lead time in days and
would like to transfer into Access query. The Excel formula looks at three
different option and calculate the result based on the if condition.

=(IF(AND(O6=1,P6=2),(((H6+I6)/60)/L6)+(J6*(1-M6)),
IF(AND(O6=2,P6=1),(((H6+I6)/60)/K6)+(J6*(1-M6)),
IF(AND(O6=1,P6=1),(((H6+I6)/60)*Q6+(J6*(1-M6))), "Did not meet defined
criteria"))))/N6

Can someone help me make it work in Access? Thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane,

Here is the sample data. Letter represent column in query. R (LT) is the
wanted calculated field.
Note it now only return #Error if it is in the scenerio 2 where Task = 2 and
Crit = 1.

A B C D E F G H I J K L M N
O P Q R
SU RT Wait Man Mach Over Day Ratio
Task Crit LT
30 5 26 1 0 0 13
2 1 #Error
15 500 12.35 1 1 0 19 1
1 1 1.10
15 90 6.2 1 1 0 19 1
1 2 2.08

Query formula for column R (LT):
LT:
Format(MyCalc([Task],[Crit],[SU],[RT],[Wait],[Man],[Mach],[Over],[Ratio],[Day]),".00")

Code:
Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, L As Double, _
K As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((((H + I) / 60) / IIf(L = 0, 1, L))) + (J * (1 - M)))
/ N
ElseIf O = 2 And P = 1 Then
varReturn = (((((H + I) / 60) / IIf(K = 0, 1, K))) + (J * (1 - M)))
/ N
ElseIf O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * (Q) + (J * (1 - M))) / N
End If

MyCalc = varReturn

End Function


Duane Hookom said:
Please provide us with your current code as well as sample values for the
fields and desired results.

--
Duane Hookom
Microsoft Access MVP


Cam said:
John,

I tried your formula and it still gave me the same #Error result. I was
wondering whether my formula referenced is incorrect, but I couldn't find
any. Could it be the Integer and Double format causing it to not reference
correctly? Thanks

John Spencer said:
varReturn = (((H + I) / 60) / IIF(L=0,1,L)) + (J * (1 - M)) / N

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Cam wrote:
John,

There is no zero value for K (decimal) and N (whole number), but L has some
decimal and zero in the data. How to I format the L field to return 1 if it
is equal zero? Thanks

:

It looks ok to me, although if L, K, Or N are ever zero you will get a divide
by zero error.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Cam wrote:
Duane,

I change the code below to your sugguestion and it didn't error out. I need
help with the result for the first two scenerios as it returns "#Error". Does
this mean the formula is wrong?

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
ElseIf O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
ElseIf O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * (Q) + (J * (1 - M))) / N
End If

MyCalc = varReturn

End Function


:

For every If you must have an End If. You might want to use ElseIf in place
of Else and If.
--
Duane Hookom
Microsoft Access MVP


:

Duane,

I put in the following code and it returns the error below.

Public Function MyCalc(O As Integer, P As Integer, _
H As Double, I As Double, J As Double, K As Double, _
L As Double, M As Double, Q As Double, N As Double) As Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn As Variant
If O = 1 And P = 2 Then
varReturn = (((H + I) / 60) / L) + (J * (1 - M)) / N
Else
If O = 2 And P = 1 Then
varReturn = (((H + I) / 60) / K) + (J * (1 - M)) / N
Else
If O = 1 And P = 1 Then
varReturn = (((H + I) / 60) * Q + (J * (1 - M))) / N

End If

MyCalc = varReturn

End Function


"Compile error:
Block If without End If"

:

Duane,

What would the letters represent? my field name?

:

That's the beauty of the function. It is created in a standard module. You
would create a new module, type in the function using the start of the code I
wrote, and then use the function in your query.

You query would have something like:
NewColumnName: MyCalc([FieldO], [FieldP],....)
--
Duane Hookom
Microsoft Access MVP


:

Duane,

I am not good with function and code, where would you put the function code
in query?

:

I would do exactly the same thing in Access that I would have done in Excel.
Create a small user-defined function that accepts arguements from O6, P6, H6,
I6, M6, K6, N6, and Q6. The function would return the appropriate value.

Cells and query columns are not the place to store your complex equations.
Use a function that can be saved in a module of "Business Functions". When a
pointy haired boss changes the calculations, you should be able to go to one
place to review the code and make changes and comments.

It looks like your expression might return a numeric or a string value. I
don't think this is good practice. You should return a number or null. The
null can be formatted in a control to display "did not meet...."

To get you started, a function might look like
Public Function MyCalc( O as Integer, P as Integer, _
H as Double, I as Double, L as Double, M as Double, _
Q as Double) as Variant
'this is a function that calculates....
'the arguments are...
Dim varReturn as Variant
If O = 1 AND P = 2 Then
varReturn = (((H+I)/0)/L)+(J*(1-M))/N
Else
If O=2 AND P=1 Then

' add more elses or whatever

End If

MyCalc = varReturn
End Function

You would call this function in the same way you would call any other
function.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have the this Excel formula I used to calculate the lead time in days and
would like to transfer into Access query. The Excel formula looks at three
different option and calculate the result based on the if condition.

=(IF(AND(O6=1,P6=2),(((H6+I6)/60)/L6)+(J6*(1-M6)),
IF(AND(O6=2,P6=1),(((H6+I6)/60)/K6)+(J6*(1-M6)),
IF(AND(O6=1,P6=1),(((H6+I6)/60)*Q6+(J6*(1-M6))), "Did not meet defined
criteria"))))/N6

Can someone help me make it work in Access? Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top