Query Question regarding IIf's

[Alberta Rose]

In the query I'm trying to make, I have 7 fields. ContractNumber, CostCode,
CostType, EstimatedCost, ActualCost, EstimatedHours, ActualHours. I have
written the following IIf statement to put in this query.

=IIf(IsNull([costcode]) Or IsNull([costtype]) Or
IsNull([EstimatedCost])=0,0, IIf([costcode]="013210" and
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst].

The txtEstimatedCost and txtCraftLabEst are fields on my report that I want
to use this IIf statement in. Also, this is only a very small portion of the
actual IIf statement that needs to be applied. It is too long to add to the
box in the detail area of the report as an IIf statement.

So my next attempt is to have this code in a query and then tie the query to
that field on the report. Once this is coded, it will be static (won't
change again) and multiple users will be using it.

The report that I need this information on is a View Closeout report that
the user can pull up by contract number. I'm not sure what other information
to give you, hopefully someone will help me find a ahaa moment!!

Thanks...Laurie

 

[XPS350]

In the query I'm trying to make, I have 7 fields.  ContractNumber, CostCode,
CostType, EstimatedCost, ActualCost, EstimatedHours, ActualHours.  I have
written the following IIf statement to put in this query.  

=IIf(IsNull([costcode]) Or IsNull([costtype]) Or
IsNull([EstimatedCost])=0,0, IIf([costcode]="013210" and
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst].

The txtEstimatedCost and txtCraftLabEst are fields on my report that I want
to use this IIf statement in.  Also, this is only a very small portion of the
actual IIf statement that needs to be applied.  It is too long to add to the  
box in the detail area of the report as an IIf statement.  

So my next attempt is to have this code in a query and then tie the queryto
that field on the report.  Once this is coded, it will be static (won't
change again) and multiple users will be using it.  

The report that I need this information on is a View Closeout report that
the user can pull up by contract number.  I'm not sure what other information
to give you, hopefully someone will help me find a ahaa moment!!

Thanks...Laurie

Maybe you can define a Function which does the calculation. The
Function would have 2 input parameters (the values of EstimatedCost
and txtCraftLabEst).
In a Function your are more flexible in writing code and you can re-
use it.

Groeten,

Peter
http://access.xps350.com

 

[Alberta Rose]

Sorry, I'm not familiar with making 'Functions'. I assume it is done thru VBA?

In the query I'm trying to make, I have 7 fields. ContractNumber, CostCode,
CostType, EstimatedCost, ActualCost, EstimatedHours, ActualHours. I have
written the following IIf statement to put in this query.

=IIf(IsNull([costcode]) Or IsNull([costtype]) Or
IsNull([EstimatedCost])=0,0, IIf([costcode]="013210" and
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst].

The txtEstimatedCost and txtCraftLabEst are fields on my report that I want
to use this IIf statement in. Also, this is only a very small portion of the
actual IIf statement that needs to be applied. It is too long to add to the
box in the detail area of the report as an IIf statement.

So my next attempt is to have this code in a query and then tie the query to
that field on the report. Once this is coded, it will be static (won't
change again) and multiple users will be using it.

The report that I need this information on is a View Closeout report that
the user can pull up by contract number. I'm not sure what other information
to give you, hopefully someone will help me find a ahaa moment!!

Thanks...Laurie

Maybe you can define a Function which does the calculation. The
Function would have 2 input parameters (the values of EstimatedCost
and txtCraftLabEst).
In a Function your are more flexible in writing code and you can re-
use it.

Groeten,

Peter
http://access.xps350.com
.

 

[John Spencer]

Queries also have a limit on how long the expression can be.

You probably need to build a custom VBA function to return the desired value.
Without knowing all the calculations that you need and all the variables
involved I can only suggest this rough outline of a function.

Public Function fSomeFunctionName(sCostCode, sCostType, sEstimatedCost,
sCraftLabEst, ....)
IF IsNull(Scostcode) Or IsNull(Scosttype) Or IsNull(sEstimatedCost) Then
fSomeFunctionName = 0
ElseIf sCostCode="012210" and sCostType="05320" Then
'You should be checking here to make sure sCraftLabEst is not zero
fSomeFunctionName = sEstimatedCost/CraftLabEst
ElseIf ....

End if

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

 

[Alberta Rose]

This makes way more sense to me. A couple of questions, in the coding you've
shown me, what does the s stand for? Will it matter that my names are
actually for example, txtEstimatedCost? And I am using sEstimatedCost?

Also, the fSomeFunctionName=sEstimatedCost/CraftLabEst (should there not be
an s before CraftLabEst too?)

Thanks.

Queries also have a limit on how long the expression can be.

You probably need to build a custom VBA function to return the desired value.
Without knowing all the calculations that you need and all the variables
involved I can only suggest this rough outline of a function.

Public Function fSomeFunctionName(sCostCode, sCostType, sEstimatedCost,
sCraftLabEst, ....)
IF IsNull(Scostcode) Or IsNull(Scosttype) Or IsNull(sEstimatedCost) Then
fSomeFunctionName = 0
ElseIf sCostCode="012210" and sCostType="05320" Then
'You should be checking here to make sure sCraftLabEst is not zero
fSomeFunctionName = sEstimatedCost/CraftLabEst
ElseIf ....

End if

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

In the query I'm trying to make, I have 7 fields. ContractNumber, CostCode,
CostType, EstimatedCost, ActualCost, EstimatedHours, ActualHours. I have
written the following IIf statement to put in this query.

=IIf(IsNull([costcode]) Or IsNull([costtype]) Or
IsNull([EstimatedCost])=0,0, IIf([costcode]="013210" and
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst].

The txtEstimatedCost and txtCraftLabEst are fields on my report that I want
to use this IIf statement in. Also, this is only a very small portion of the
actual IIf statement that needs to be applied. It is too long to add to the
box in the detail area of the report as an IIf statement.

So my next attempt is to have this code in a query and then tie the query to
that field on the report. Once this is coded, it will be static (won't
change again) and multiple users will be using it.

The report that I need this information on is a View Closeout report that
the user can pull up by contract number. I'm not sure what other information
to give you, hopefully someone will help me find a ahaa moment!!

Thanks...Laurie

.

 

[John Spencer]

Yes, there should be sCraftLabEst.

The s is appended to tell me (not the computer) that the variable is a string
or null. You could use no prefix or some other scheme like
str String
nbr Number
dte Date
etc.

Again not really needed just a convenience for me when I code. If you use a
consistent scheme it helps keep things clear and tends to help you avoid using
a reserved word.

The names in the function declaration are the names of variables that will get
their value from what you use when you call the function.

To use the function in a query you would see something like the following in
one of the Field "rectangles" in query design view

Field: MyCalc: fSomeFunctionName([CostCode],[CostType],[EstimatedCost],...)

In the Function the variable sCostCode would get the value of [CostCode],
sCostType would get the value of [CostType], etc.

Then the processing in the function would use the variables to do whatever and
the function would pass back the calculated value.

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