Fill one field based on the status of another.

[Jim]

Hi.

I have a database in which the records of a form are considered open or
closed based on whether a "closed date" field is null (empty) or
filled, respectively. This works fine in creating a report from a
query showing which records remain open. Now I am asked to produce a
report in which all records display in ascending auto number sequence
and are given a status of "open" or "closed". Simply showing a closed
date is not sufficient for this. Can a new "Status" field be
automatically filled with the words "Open" or "Closed" based on the
condition of the "Closed date" field? I would like to stay away from
writing code as much as possible and hope this can be accomplished with
macros or some other means.

Thanks for any help offered.

Jim

 

[Rick B]

Just build am unbound text box in your report. Put something like the
following...

=IIf([Closed Date],"Closed","Open")

 

[Guest]

If you are talking about populating a field in a table based on data derived
from another field, it's almost always a bad idea. Better to just use the
data already there with something like the following in a query:

Status: IIf(IsNull([closed date] = True, "Open", "Closed')

 

[Joseph Meehan]

Now I am asked to produce a
report in which all records display in ascending auto number sequence
...

Thanks for any help offered.

Jim

Are you aware that autonumber does not guarantee sequential numbers?
There are several events that can cause gaps or other odd unexpected
ordering of autonumbers.

Autonumber are designed for providing a unique number only. Any other
use is discouraged. I suggest that you not allow your users to even see the
autonumbers.

There are other ways of providing the numbers you want depending on the
particual application.

 

[Jim]

Rick, I tried your suggestion and it worked perfectly for me. Many
many thanks.

Jerry, Rick's worked so I did not try yours but many thanks to you
also.

Joseph, Thank you for your information but the auto number in my table
is used only as a unique ID and the loss of any particular number or
numbers is not of that much importance to me, but I will keep your post
in mind for those db's I have to which it could apply.

Again, thanks to all.

Jim