Calculated Field On My Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form I have created (data source is a query) that is being used to
create a "checklist" form. So, I have these items in each row:

Item Risk Factor Complete Status
-------------- ------------ ----------- --------
ITEM 1 Data 1 Yes [blank]
ITEM 2 Data 5 No REQUIRED!

Risk factor is numeric field with values from 0 to 5
Complete is a yes/no box, which I actually have appearing as a checkbox.
Status is the calculated field I am trying to get working

The status field is the field I would like to calculate. If, for example,
the Risk Factor is 5, and the Complete field is NO, then I want the words
"REQUIRED" to appear under status. I will also add conditional formatting to
turn that value red if the status=Required (but I am ok with that).

What I cannot seem to do is get the words REQUIRED to appear. I have tried
a number of expressions (in the Control Source property of the Status Text
Box) to calculate the value, but the best I can get is a value = #NAME? to
appear. Not sure what that means. For example:

=iff ([qry RiskFactor1] = 5,"Required","Not Required")

Any idea...am I way off in the way I am trying to do this? I am pretty new
to Access when it comes to creating expressions (I have yet to find a good
resource for doing this).

Thanks!

Pat
 
Is Risk Factor a numeric field? If not, you need =iff ([qry RiskFactor1] =
"5","Required","Not Required")

But, you are only looking at one factor. I thought you said that the
"Complete field also needs to be "No".

You need both criteria...

=iff ([qry RiskFactor1] = "5" and [Complete] = false,"Required","Not
Required")
 
You are correct...there were more conditions, but I was trying to make baby
steps :-)

Here is the exact condition that I have that is not working (and I am doing
this on the form, but right clicking the text box, selecting properities, and
inserting this in the Control Source field):

=iff([tbl Risk Factors_AGENT_SDClient_Setup]=5 And [tbl Asset
Checklist_AGENT_SDClient_Setup]=False,"Required","Not Required")

The current known value of tbl Risk Factors_AGENT_SDClient_Setup = 5
The current known value of tbl Asset Checklist_AGENT_SDClient_Setup = FALSE

Thus, I am expecting to see "REQUIRED" in the text box. Instead I see:
#Name?

Any ideas, and thanks for your help!

Pat

Rick B said:
Is Risk Factor a numeric field? If not, you need =iff ([qry RiskFactor1] =
"5","Required","Not Required")

But, you are only looking at one factor. I thought you said that the
"Complete field also needs to be "No".

You need both criteria...

=iff ([qry RiskFactor1] = "5" and [Complete] = false,"Required","Not
Required")

--
Rick B



PatK said:
I have a form I have created (data source is a query) that is being used to
create a "checklist" form. So, I have these items in each row:

Item Risk Factor Complete Status
-------------- ------------ ----------- --------
ITEM 1 Data 1 Yes [blank]
ITEM 2 Data 5 No REQUIRED!

Risk factor is numeric field with values from 0 to 5
Complete is a yes/no box, which I actually have appearing as a checkbox.
Status is the calculated field I am trying to get working

The status field is the field I would like to calculate. If, for example,
the Risk Factor is 5, and the Complete field is NO, then I want the words
"REQUIRED" to appear under status. I will also add conditional formatting
to
turn that value red if the status=Required (but I am ok with that).

What I cannot seem to do is get the words REQUIRED to appear. I have
tried
a number of expressions (in the Control Source property of the Status Text
Box) to calculate the value, but the best I can get is a value = #NAME? to
appear. Not sure what that means. For example:

=iff ([qry RiskFactor1] = 5,"Required","Not Required")

Any idea...am I way off in the way I am trying to do this? I am pretty
new
to Access when it comes to creating expressions (I have yet to find a good
resource for doing this).

Thanks!

Pat
Click to expand...
Click to expand...
 
THAT WAS IT! OMG...I don't know how I missed that! Thanks so much!

Pat

JohnE said:
Not to get picky but shouldn't the "iff" be IIf for Immediate If?


PatK said:
I have a form I have created (data source is a query) that is being used to
create a "checklist" form. So, I have these items in each row:

Item Risk Factor Complete Status
-------------- ------------ ----------- --------
ITEM 1 Data 1 Yes [blank]
ITEM 2 Data 5 No REQUIRED!

Risk factor is numeric field with values from 0 to 5
Complete is a yes/no box, which I actually have appearing as a checkbox.
Status is the calculated field I am trying to get working

The status field is the field I would like to calculate. If, for example,
the Risk Factor is 5, and the Complete field is NO, then I want the words
"REQUIRED" to appear under status. I will also add conditional formatting to
turn that value red if the status=Required (but I am ok with that).

What I cannot seem to do is get the words REQUIRED to appear. I have tried
a number of expressions (in the Control Source property of the Status Text
Box) to calculate the value, but the best I can get is a value = #NAME? to
appear. Not sure what that means. For example:

=iff ([qry RiskFactor1] = 5,"Required","Not Required")

Any idea...am I way off in the way I am trying to do this? I am pretty new
to Access when it comes to creating expressions (I have yet to find a good
resource for doing this).

Thanks!

Pat
Click to expand...
Click to expand...
 
Back
Top