If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Yes / No field auto populate based on expiration date (Access 2003)
Hello,
I have been challenged with making a yes/no field either stay on or off based and contract end date field in my table. I am using Access 2003. So for example, my client would like to have a table with products (text), contract end date (date), current date (date), renew (yes/no). If the current date field is greater than the contract end date, then i'd like the renew box to be checked in the table for that record. I realize that I can just setup a simple query to show me the products with contracts that are past the current date, but I was just wondering if this was the only was I can set this up. Thanks in advance, Chris |
#2
|
|||
|
|||
Yes / No field auto populate based on expiration date (Access 2003)
Chris
If you (and/or your client) can already tell that a record is due for renewal (i.e., "current date is greater than contract end date", I don't see the value in storing either the [CurrentDate] or the [Renew (Y/N)] value. That might be how you'd do it in a spreadsheet, but Access is a relational database. If you create a query against a table that has a [ContractEndDate] field, and you create a new field in that query, something like (untested): Renew: IIF(Date()[ContractEndDate],"Yes", "No") and used "Yes" as the Selection Criterion under that (new) field, you'd have a way to identify every record that was due for renewal. Storing "current date" in your table is a maintenance headache ... every day, you have to update that field. Storing a Y/N value based on that is redundant, given the approach above. Your client needs to focus on "what" (what do I need to know to run my business?), rather than telling you "how" (and here's how you have to program it...). Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "Chris" wrote in message ... Hello, I have been challenged with making a yes/no field either stay on or off based and contract end date field in my table. I am using Access 2003. So for example, my client would like to have a table with products (text), contract end date (date), current date (date), renew (yes/no). If the current date field is greater than the contract end date, then i'd like the renew box to be checked in the table for that record. I realize that I can just setup a simple query to show me the products with contracts that are past the current date, but I was just wondering if this was the only was I can set this up. Thanks in advance, Chris |
Thread Tools | |
Display Modes | |
|
|