Struggling to perform IF function in property sheet for table in a

[Adam]

Dear all,

I am trying to set up a database of 'logged issues to be resolved'.

In the the Database, there is a field called TargetDate which represents the
target date of resolving the issue.

There is also a field called Status, which is to represent the status of the
error and is assigned a value of 'Active', 'Overdue' or 'Closed' - 'Closed'
(issue has been resolved), 'Active' (still unresolved issue, but date not
passed Target date) and 'Overdue' (still unresolved and date passes Target
Date).

What I want is for my Table to automatically update this Status for issues
which remain unresolved passed their TargetDate. So basically if TargetDate
is before the current date then the Status value of Active should
automatically change to Overdue.

I tried expressions like

IF ( [Table1]![DateTargeted_for_Res] > Date() , "overdue", "Active) but that
caused me lots of problems, making things worse that they were before.

Another problem is that I don't know where I should enter this expression. I
want it to change values in my table, but I can only open the individual
properties for Status when I am in the Form mode. I was unable to open the
property sheet for individual fields in table mode. I am currently using
Access 2000 but hope to be using 2003 later on today or tomorrow.

If you've read this far, many many thanks

Regards,

Adam

 

[Jeff Boyce]

Adam

It sounds like you are saying that you "know" the status based on other
fields and their contents. If so, you don't need to actually store the
status, since you can "calculate" it ... instead, use a query to do that
calculation for any display (i.e., in a form or in a report).

In a new field in the query, you could use the IIF() function (see Access
HELP for syntax) to do your calculation.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Adam]

Hi Jeff,

You are correct, but what I didn't make clear in my post is that when an
issue is resolved, I would like the person who resolved it to be able to
manually update the status to 'Closed'. I guessed for this reason, I would
need to have the status value in the table so it can be manually updated.

Would you advise to have a field called something like 'Completed' with
entry values of YES and NO, and use the results from this in the formula that
defines status.

This is what I'll try to do now. Please let me know if I'm going in the
wrong direction with this, otherwise many thanks for your time and help.

Adam

Adam

It sounds like you are saying that you "know" the status based on other
fields and their contents. If so, you don't need to actually store the
status, since you can "calculate" it ... instead, use a query to do that
calculation for any display (i.e., in a form or in a report).

In a new field in the query, you could use the IIF() function (see Access
HELP for syntax) to do your calculation.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Dear all,

I am trying to set up a database of 'logged issues to be resolved'.

In the the Database, there is a field called TargetDate which represents the
target date of resolving the issue.

There is also a field called Status, which is to represent the status of the
error and is assigned a value of 'Active', 'Overdue' or 'Closed' - 'Closed'
(issue has been resolved), 'Active' (still unresolved issue, but date not
passed Target date) and 'Overdue' (still unresolved and date passes Target
Date).

What I want is for my Table to automatically update this Status for issues
which remain unresolved passed their TargetDate. So basically if TargetDate
is before the current date then the Status value of Active should
automatically change to Overdue.

I tried expressions like

IF ( [Table1]![DateTargeted_for_Res] > Date() , "overdue", "Active) but that
caused me lots of problems, making things worse that they were before.

Another problem is that I don't know where I should enter this expression. I
want it to change values in my table, but I can only open the individual
properties for Status when I am in the Form mode. I was unable to open the
property sheet for individual fields in table mode. I am currently using
Access 2000 but hope to be using 2003 later on today or tomorrow.

If you've read this far, many many thanks

Regards,

Adam

 

[Jeff Boyce]

Adam

Is there any value to you/your customer in knowing WHEN the status was
"completed"?

If so, use a date/time field instead of a yes/no field.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hi Jeff,

You are correct, but what I didn't make clear in my post is that when an
issue is resolved, I would like the person who resolved it to be able to
manually update the status to 'Closed'. I guessed for this reason, I would
need to have the status value in the table so it can be manually updated.

Would you advise to have a field called something like 'Completed' with
entry values of YES and NO, and use the results from this in the formula that
defines status.

This is what I'll try to do now. Please let me know if I'm going in the
wrong direction with this, otherwise many thanks for your time and help.

Adam

Adam

It sounds like you are saying that you "know" the status based on other
fields and their contents. If so, you don't need to actually store the
status, since you can "calculate" it ... instead, use a query to do that
calculation for any display (i.e., in a form or in a report).

In a new field in the query, you could use the IIF() function (see Access
HELP for syntax) to do your calculation.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Dear all,

I am trying to set up a database of 'logged issues to be resolved'.

In the the Database, there is a field called TargetDate which

represents
the

target date of resolving the issue.

There is also a field called Status, which is to represent the status

of
the

error and is assigned a value of 'Active', 'Overdue' or 'Closed' - 'Closed'
(issue has been resolved), 'Active' (still unresolved issue, but date not
passed Target date) and 'Overdue' (still unresolved and date passes Target
Date).

What I want is for my Table to automatically update this Status for issues
which remain unresolved passed their TargetDate. So basically if TargetDate
is before the current date then the Status value of Active should
automatically change to Overdue.

I tried expressions like

IF ( [Table1]![DateTargeted_for_Res] > Date() , "overdue", "Active)

but
that

caused me lots of problems, making things worse that they were before.

Another problem is that I don't know where I should enter this

expression.
I

want it to change values in my table, but I can only open the individual
properties for Status when I am in the Form mode. I was unable to open the
property sheet for individual fields in table mode. I am currently using
Access 2000 but hope to be using 2003 later on today or tomorrow.

If you've read this far, many many thanks

Regards,

Adam

 

[BruceM]

You will need to test to see if it is marked as Closed, because your code as
it stands will not allow "Closed" (plus which the syntax needs adjusting).
You could have a status field marked as either "Open" or "Closed", then
check for the date:

StatusCheck: IIf([Status] = "Open" and [TargetDate] >
Date(),"Overdue",[Status])

You haven't specified the user interface, but it really needs to be a form.
You could base the form on the query, and bind a text box on the form to
StatusCheck. This has the disadvantage of leaving you with two places on
the form with the word "Open" or "Closed" (the combo box from which you
select the status, and the new text box). If you use VBA you will have some
more flexibility, I think. For instance, you could have a label (lblDue)
with the Caption set to Overdue. In the form's Current event you could have
something like:

If Me.Status = "Open" And Me.TargetDate > Date Then
Me.lblDue.Visible = True
Else
Me.lblDue.Visible = False
End If

Or you could modify the field:

If Me.Status = "Open" And Me.TargetDate > Date Then
Me.Status = "Overdue"
End If

Note that Date does not use parentheses when used in VBA.

It could be a tidier interface if you use an Option Group for Status. The
user would only need to click a radio button rather than selecting from a
list or typing the entry. If you use text you should use a combo box or
list box with just the three choices in a Value List for the Row Source, and
limit the user selection to that list, otherwise typos could cause havoc.

Hi Jeff,

You are correct, but what I didn't make clear in my post is that when an
issue is resolved, I would like the person who resolved it to be able to
manually update the status to 'Closed'. I guessed for this reason, I would
need to have the status value in the table so it can be manually updated.

Would you advise to have a field called something like 'Completed' with
entry values of YES and NO, and use the results from this in the formula
that
defines status.

This is what I'll try to do now. Please let me know if I'm going in the
wrong direction with this, otherwise many thanks for your time and help.

Adam

Adam

It sounds like you are saying that you "know" the status based on other
fields and their contents. If so, you don't need to actually store the
status, since you can "calculate" it ... instead, use a query to do that
calculation for any display (i.e., in a form or in a report).

In a new field in the query, you could use the IIF() function (see Access
HELP for syntax) to do your calculation.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Dear all,

I am trying to set up a database of 'logged issues to be resolved'.

In the the Database, there is a field called TargetDate which
represents the
target date of resolving the issue.

There is also a field called Status, which is to represent the status
of the
error and is assigned a value of 'Active', 'Overdue' or 'Closed' - 'Closed'
(issue has been resolved), 'Active' (still unresolved issue, but date
not
passed Target date) and 'Overdue' (still unresolved and date passes
Target
Date).

What I want is for my Table to automatically update this Status for
issues
which remain unresolved passed their TargetDate. So basically if TargetDate
is before the current date then the Status value of Active should
automatically change to Overdue.

I tried expressions like

IF ( [Table1]![DateTargeted_for_Res] > Date() , "overdue", "Active) but that
caused me lots of problems, making things worse that they were before.

Another problem is that I don't know where I should enter this
expression. I
want it to change values in my table, but I can only open the
individual
properties for Status when I am in the Form mode. I was unable to open
the
property sheet for individual fields in table mode. I am currently
using
Access 2000 but hope to be using 2003 later on today or tomorrow.

If you've read this far, many many thanks

Regards,

Adam

 

[Adam]

Dear Jeff,

Many thanks for this. It would be beneficial to keep track of when the
status was completed.

I used the formula

Expr1:
IIf([Completed]=True,"Closed",IIf([DateTargeted_for_Res]<Date(),"Overdue","Active"))

But will look into changing this to encorporate dates (or I may just have a
seperate date of completion column for simplicity)

Many thanks and regards,

Adam

Adam

Is there any value to you/your customer in knowing WHEN the status was
"completed"?

If so, use a date/time field instead of a yes/no field.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hi Jeff,

You are correct, but what I didn't make clear in my post is that when an
issue is resolved, I would like the person who resolved it to be able to
manually update the status to 'Closed'. I guessed for this reason, I would
need to have the status value in the table so it can be manually updated.

Would you advise to have a field called something like 'Completed' with
entry values of YES and NO, and use the results from this in the formula that
defines status.

This is what I'll try to do now. Please let me know if I'm going in the
wrong direction with this, otherwise many thanks for your time and help.

Adam

Adam

It sounds like you are saying that you "know" the status based on other
fields and their contents. If so, you don't need to actually store the
status, since you can "calculate" it ... instead, use a query to do that
calculation for any display (i.e., in a form or in a report).

In a new field in the query, you could use the IIF() function (see Access
HELP for syntax) to do your calculation.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Dear all,

I am trying to set up a database of 'logged issues to be resolved'.

In the the Database, there is a field called TargetDate which represents
the
target date of resolving the issue.

There is also a field called Status, which is to represent the status of
the
error and is assigned a value of 'Active', 'Overdue' or 'Closed' -
'Closed'
(issue has been resolved), 'Active' (still unresolved issue, but date not
passed Target date) and 'Overdue' (still unresolved and date passes Target
Date).

What I want is for my Table to automatically update this Status for issues
which remain unresolved passed their TargetDate. So basically if
TargetDate
is before the current date then the Status value of Active should
automatically change to Overdue.

I tried expressions like

IF ( [Table1]![DateTargeted_for_Res] > Date() , "overdue", "Active) but
that
caused me lots of problems, making things worse that they were before.

Another problem is that I don't know where I should enter this expression.
I
want it to change values in my table, but I can only open the individual
properties for Status when I am in the Form mode. I was unable to open the
property sheet for individual fields in table mode. I am currently using
Access 2000 but hope to be using 2003 later on today or tomorrow.

If you've read this far, many many thanks

Regards,

Adam

 

[Adam]

Dear Bruce,

Thanks for this. I , as you suggest, will use a form for the input. Keeping
things simple I am currently using the following formula

Expr1:
IIf([Completed]=True,"Closed",IIf([DateTargeted_for_Res]<Date(),"Overdue","Active"))

but am seeing some flaws with it, so will look into what you suggest.

Many thanks,

Adam

You will need to test to see if it is marked as Closed, because your code as
it stands will not allow "Closed" (plus which the syntax needs adjusting).
You could have a status field marked as either "Open" or "Closed", then
check for the date:

StatusCheck: IIf([Status] = "Open" and [TargetDate] >
Date(),"Overdue",[Status])

You haven't specified the user interface, but it really needs to be a form.
You could base the form on the query, and bind a text box on the form to
StatusCheck. This has the disadvantage of leaving you with two places on
the form with the word "Open" or "Closed" (the combo box from which you
select the status, and the new text box). If you use VBA you will have some
more flexibility, I think. For instance, you could have a label (lblDue)
with the Caption set to Overdue. In the form's Current event you could have
something like:

If Me.Status = "Open" And Me.TargetDate > Date Then
Me.lblDue.Visible = True
Else
Me.lblDue.Visible = False
End If

Or you could modify the field:

If Me.Status = "Open" And Me.TargetDate > Date Then
Me.Status = "Overdue"
End If

Note that Date does not use parentheses when used in VBA.

It could be a tidier interface if you use an Option Group for Status. The
user would only need to click a radio button rather than selecting from a
list or typing the entry. If you use text you should use a combo box or
list box with just the three choices in a Value List for the Row Source, and
limit the user selection to that list, otherwise typos could cause havoc.

Hi Jeff,

You are correct, but what I didn't make clear in my post is that when an
issue is resolved, I would like the person who resolved it to be able to
manually update the status to 'Closed'. I guessed for this reason, I would
need to have the status value in the table so it can be manually updated.

Would you advise to have a field called something like 'Completed' with
entry values of YES and NO, and use the results from this in the formula
that
defines status.

This is what I'll try to do now. Please let me know if I'm going in the
wrong direction with this, otherwise many thanks for your time and help.

Adam

Adam

It sounds like you are saying that you "know" the status based on other
fields and their contents. If so, you don't need to actually store the
status, since you can "calculate" it ... instead, use a query to do that
calculation for any display (i.e., in a form or in a report).

In a new field in the query, you could use the IIF() function (see Access
HELP for syntax) to do your calculation.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Dear all,

I am trying to set up a database of 'logged issues to be resolved'.

In the the Database, there is a field called TargetDate which
represents
the
target date of resolving the issue.

There is also a field called Status, which is to represent the status
of
the
error and is assigned a value of 'Active', 'Overdue' or 'Closed' -
'Closed'
(issue has been resolved), 'Active' (still unresolved issue, but date
not
passed Target date) and 'Overdue' (still unresolved and date passes
Target
Date).

What I want is for my Table to automatically update this Status for
issues
which remain unresolved passed their TargetDate. So basically if
TargetDate
is before the current date then the Status value of Active should
automatically change to Overdue.

I tried expressions like

IF ( [Table1]![DateTargeted_for_Res] > Date() , "overdue", "Active) but
that
caused me lots of problems, making things worse that they were before.

Another problem is that I don't know where I should enter this
expression.
I
want it to change values in my table, but I can only open the
individual
properties for Status when I am in the Form mode. I was unable to open
the
property sheet for individual fields in table mode. I am currently
using
Access 2000 but hope to be using 2003 later on today or tomorrow.

If you've read this far, many many thanks

Regards,

Adam

 

[BruceM]

I am just now learning that you have Yes/No field for Completed. It would
have helped to know that sooner. I have been assuming that Status is a
field in the table. You will need to use > instead of < in the expression,
I think, but other than that what is the problem? You should be able to
base a form on the query and bind a text box to Expr1 (although you will be
doing yourself a favor if you give the expression a more meaningful name
such as Status). There is no need to store the Status value, considering
that you have the Completed field. It may be a little redundant to have
both a Completed check box and the word "Closed". Another point is that you
may need to requery after changing Completed; I don't think the Status text
will change unless you do.

Dear Bruce,

Thanks for this. I , as you suggest, will use a form for the input.
Keeping
things simple I am currently using the following formula

Expr1:
IIf([Completed]=True,"Closed",IIf([DateTargeted_for_Res]<Date(),"Overdue","Active"))

but am seeing some flaws with it, so will look into what you suggest.

Many thanks,

Adam

You will need to test to see if it is marked as Closed, because your code
as
it stands will not allow "Closed" (plus which the syntax needs
adjusting).
You could have a status field marked as either "Open" or "Closed", then
check for the date:

StatusCheck: IIf([Status] = "Open" and [TargetDate] >
Date(),"Overdue",[Status])

You haven't specified the user interface, but it really needs to be a
form.
You could base the form on the query, and bind a text box on the form to
StatusCheck. This has the disadvantage of leaving you with two places on
the form with the word "Open" or "Closed" (the combo box from which you
select the status, and the new text box). If you use VBA you will have
some
more flexibility, I think. For instance, you could have a label (lblDue)
with the Caption set to Overdue. In the form's Current event you could
have
something like:

If Me.Status = "Open" And Me.TargetDate > Date Then
Me.lblDue.Visible = True
Else
Me.lblDue.Visible = False
End If

Or you could modify the field:

If Me.Status = "Open" And Me.TargetDate > Date Then
Me.Status = "Overdue"
End If

Note that Date does not use parentheses when used in VBA.

It could be a tidier interface if you use an Option Group for Status.
The
user would only need to click a radio button rather than selecting from a
list or typing the entry. If you use text you should use a combo box or
list box with just the three choices in a Value List for the Row Source,
and
limit the user selection to that list, otherwise typos could cause havoc.

Hi Jeff,

You are correct, but what I didn't make clear in my post is that when
an
issue is resolved, I would like the person who resolved it to be able
to
manually update the status to 'Closed'. I guessed for this reason, I
would
need to have the status value in the table so it can be manually
updated.

Would you advise to have a field called something like 'Completed' with
entry values of YES and NO, and use the results from this in the
formula
that
defines status.

This is what I'll try to do now. Please let me know if I'm going in the
wrong direction with this, otherwise many thanks for your time and
help.

Adam

:

Adam

It sounds like you are saying that you "know" the status based on
other
fields and their contents. If so, you don't need to actually store
the
status, since you can "calculate" it ... instead, use a query to do
that
calculation for any display (i.e., in a form or in a report).

In a new field in the query, you could use the IIF() function (see
Access
HELP for syntax) to do your calculation.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Dear all,

I am trying to set up a database of 'logged issues to be resolved'.

In the the Database, there is a field called TargetDate which
represents
the
target date of resolving the issue.

There is also a field called Status, which is to represent the
status
of
the
error and is assigned a value of 'Active', 'Overdue' or 'Closed' -
'Closed'
(issue has been resolved), 'Active' (still unresolved issue, but
date
not
passed Target date) and 'Overdue' (still unresolved and date passes
Target
Date).

What I want is for my Table to automatically update this Status for
issues
which remain unresolved passed their TargetDate. So basically if
TargetDate
is before the current date then the Status value of Active should
automatically change to Overdue.

I tried expressions like

IF ( [Table1]![DateTargeted_for_Res] > Date() , "overdue", "Active)
but
that
caused me lots of problems, making things worse that they were
before.

Another problem is that I don't know where I should enter this
expression.
I
want it to change values in my table, but I can only open the
individual
properties for Status when I am in the Form mode. I was unable to
open
the
property sheet for individual fields in table mode. I am currently
using
Access 2000 but hope to be using 2003 later on today or tomorrow.

If you've read this far, many many thanks

Regards,

Adam