Tying Yes/No Field to Date Field

[JayS]

Hey guys,

I am creating a database for our contracts. Currently the contracts run on a one year cycle. I am creating an "Expired" Yes/No field. I want this field to be tied to my "Exp Date" date field. So when Todays date passes the exp date field, I want the Yes/No field to automatically check YES.

Is this possible in my main data table without user intervention?

Also, is it possible to auto-generate a report based off of the said expired yes/no field at the end of each month?



Hopefully I explained this clear enough. Thanks in advanced.

[CJ_London]

I guess you could make it a calculated field (don't know, don't use them) but why bother? Instead of having a criteria based on the calculated field, you would simply base it on your expirydate field with the same calculation. Plus calculated fields cannot be indexed which can have a significant effect on performance - one of the reasons I don't use them.

[JayS]

I guess you could make it a calculated field (don't know, don't use them) but why bother? Instead of having a criteria based on the calculated field, you would simply base it on your expirydate field with the same calculation. Plus calculated fields cannot be indexed which can have a significant effect on performance - one of the reasons I don't use them.

Thanks Ajax. I will look into calculated fields, as I am unfamiliar. What do you mean by can't be indexed?

[CJ_London]

indexing is a vital part of database design - all fields which are regularly used for filtering/criteria or sorting should be indexed, otherwise the database will be very slow. Not too apparent on small datasets but very noticeable on large datasets - a dataset of say 1m records will take less than a second to filter if indexed and several minutes if not. Calculated fields, by their nature cannot be indexed - in table design you will see the indexing options for each field - except calculated fields and memo fields.

To understand indexing, take a look at this link https://www.access-programmers.co.uk...d.php?t=291268

[JayS]

indexing is a vital part of database design - all fields which are regularly used for filtering/criteria or sorting should be indexed, otherwise the database will be very slow. Not too apparent on small datasets but very noticeable on large datasets - a dataset of say 1m records will take less than a second to filter if indexed and several minutes if not. Calculated fields, by their nature cannot be indexed - in table design you will see the indexing options for each field - except calculated fields and memo fields.

To understand indexing, take a look at this link https://www.access-programmers.co.uk...d.php?t=291268

Thanks Ajax.

Just want to bump this to see if anyone knows how to create a calculated field for this.

I've tried : IIf( [Exp Date], Date()> , Date<= )

But that didn't work lol

[andy49]

iif( [exp date]<=date(),yes,no)

[JayS]

iif( [exp date]<=date(),yes,no)

Thanks Andy,

It is saying this expression can not be used in a calculated column

[andy49]

Try -1 and 0 instead of yes and no


Sent from my iPhone using Tapatalk

[JayS]

Try -1 and 0 instead of yes and no


Sent from my iPhone using Tapatalk

Hm, still giving me the cannot be used in a calc column error

[CJ_London]

calculated fields in table have very limited functionality. referencing the Date function is one of them. To see what you can use, use the expression builder

[JayS]

calculated fields in table have very limited functionality. referencing the Date function is one of them. To see what you can use, use the expression builder

Yeah as I was toying with it I noticed it in the expression builder.

So the best course of action from my research seems to be the Append Query function. Though that is giving me some validation rule errors when I am trying to append.

[andy49]

Jay could you use a bound control on a form. Then change the value on the form using vba if the right conditions were met?

This would change the underlying value in the table


Sent from my iPhone using Tapatalk

[CJ_London]

So the best course of action from my research seems to be the Append Query function

I must admit I'm struggling to understand why you need to do this anyway since it is as easy to query based on the date field as a yes/no field, or simply include the calculation in the query when required.

But good luck with your project

[JayS]

I must admit I'm struggling to understand why you need to do this anyway since it is as easy to query based on the date field as a yes/no field, or simply include the calculation in the query when required.

But good luck with your project

Yes, understood

Unfortunately I won't be the person who will be managing the database for much longer after it's created, therefore, I am trying to make things as fool proof as possible. So having fields changed without any need for input by office staff keeps it as easy to understand as possible. As I can imagined entries being missed

[andy49]

I'd agree with Ajax. Project less likely to go wrong.


Sent from my iPhone using Tapatalk