Help with calculation in query!!!

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
 
D

Duane Hookom

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

Cam

Duane,

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

Duane Hookom

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],....)
 
C

Cam

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

Cam

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

Duane Hookom

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Tighter formula than this? 5
Last "non-blank" value in a Row... 2
my first excel experience (share portfolio) 2
Is there an Excel Function that can be used in lieu of this formul 5
Ignore NA in a formula when I type it in a cell 4
Excel 2007 - AVERAGEIF on rows that are non-continuous 2
I am having problems with a IF(AND(OR nested function 2
To combine various cell formulae in to the final cell 1

Top