How to generate an Expired or Valid comment

[ATanker62]

Hello all,

I hope this is a small question, I have a database that has a column that
contains dates as to when a permit expires. I have a comment field that I
either type Valid or Expired depending upon to current date. Is there a way
that I can gererate this data automatically by compairing todays date with
the permit expires date?

Thanks

 

[EJ Williams]

This can be done in any number of ways depending on how you want to aproach
it. This can be most easily accomplished though an update query that would
update all records at once.

Create a new update query and add the two coulmns <expiration date> and
<comments> fields to the QBE grid. Set the Update to: row to "Expired" for
the comments field and set the the criteria row under the expiration date
column to "< Date()". You can do the same for "Valid" by setting the criteria
to >= Date().

The bigger question is why you would store this value in a field at all as
this is dynamic and can be determined at any point in time by an expression
in a calculated field.

I hope this is what you were looking for.

Eric

 

[ATanker62]

Thank you EJ,

Okay, forgive my ignorance, but how would I go about creating an expression
in a calculated field?

Thanks

 

[EJ Williams]

No problem. I didn't mean to come off sounding condascending... there's
enough of that around without me adding to it.

If you are doing this in a form or report you need only add an unbound
control. This could be a textbox or checkbox for this application. In the
control source for a textbox add "=IIf([expires]<Date(),"Expired","Valid")".
Leave out the outermost quotes in my example.

For a checkbox you could set the related caption to "Valid" and set the
controlsource to "=[Expires]>=Date())". (Leave out the quotes.) This will
result in the checkbox being checked where the expiration date is greater
than or equal to today.

Does this help?

Eric

 

[ATanker62]

Yes, thank you.

No problem. I didn't mean to come off sounding condascending... there's
enough of that around without me adding to it.

If you are doing this in a form or report you need only add an unbound
control. This could be a textbox or checkbox for this application. In the
control source for a textbox add "=IIf([expires]<Date(),"Expired","Valid")".
Leave out the outermost quotes in my example.

For a checkbox you could set the related caption to "Valid" and set the
controlsource to "=[Expires]>=Date())". (Leave out the quotes.) This will
result in the checkbox being checked where the expiration date is greater
than or equal to today.

Does this help?

Eric

Thank you EJ,

Okay, forgive my ignorance, but how would I go about creating an expression
in a calculated field?

Thanks