Problems with Expression Builder and Proprty Sheets

[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

 

[Douglas J. Steele]

It's actually a violation of relational database theory to have a field in a
table whose value can be completely derived from the values of other fields
in the same table. For this reason, Access doesn't allow you to set values
of fields as functions based on other fields.

My advice would be to remove the Status field from your table. Then, create
a query based on your table. Add a computed field to that query that returns
the status. To do this, you'd type

Status: IIf([DateTargeted_for_Res] > Date(), "Overdue", "Active")

into a blank column of the query. (Not sure how you determine a status of
Closed, since you didn't give that information in your post). Use that query
wherever you would otherwise have used the table.

 

[Adam]

Thanks Doug,

I'll get started with your advice.

Best regards,

Adam

It's actually a violation of relational database theory to have a field in a
table whose value can be completely derived from the values of other fields
in the same table. For this reason, Access doesn't allow you to set values
of fields as functions based on other fields.

My advice would be to remove the Status field from your table. Then, create
a query based on your table. Add a computed field to that query that returns
the status. To do this, you'd type

Status: IIf([DateTargeted_for_Res] > Date(), "Overdue", "Active")

into a blank column of the query. (Not sure how you determine a status of
Closed, since you didn't give that information in your post). Use that query
wherever you would otherwise have used the table.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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