Auto change a "Yes / No" field based upon a Due Date

[hawzmolly]

I have a form (attached) that has a "Yes/No' field on it that needs to be changed based on a Due Date that is based upon a "Paid Date". I use a Button Control that will populate the Due Date based on the number of days after the Paid Date i.e., 1 Quarter, 1/2 Year, or 1 Year (4 Quarters). The due date is based upon how many quarters a member pays for. Members like to pay quarterly, by 1/2 year, or yearly which is ok with our club as some members are snowbirds.



I have coded the Due Date "On Click Event Procedure" thus:

Private Sub Option104_Click() Me!DuesDue = DateAdd("q", 4, Me!DuesPaid)
FirstName.SetFocus
End Sub

What I need to do is change the Current "Yes/No" field to "No" 1 day after the actual Due Date date. I am assuming using a "Date" Function I will be able to accomplish this change.

Any help would be greatly appreciated.

Thank you, thank you, thank you!

[ranman256]

It seems to me youd need a tBilling table to create records the member needs to pay.
If the member has Q payments, then produces those records to be paid.

tBilling table
MemID, DueDate, PaidDate
123, 1/1/19, 1/1/19
123, 4/1/19,

the records with the missing paid date are the ones that need paid, not a yes/no flag, but : YesPaid = Not IsNull(PaidDate)

[hawzmolly]

Sorry ranman256, but I have no idea what you are talking about. Please expand. I need a method to cause the "Current" to go "False" or uncheck 1 day after the Due Date automatically after or upon opening the form. Thank you.

[June7]

Where is DueDate supposed to come from?

Expression in checkbox ControlSource: =Date()>[DueDate]

But as ranman points out, if invoice is unpaid beyond due date, that determines status of overdue. So what if current date is past the expected due date? Maybe they made a payment before the due date. DueDate is unchanged but they can still be up to date with payments.

[hawzmolly]

The Due Date comes from the date paid, the amount paid and then from the code written into the Buttons below "Set Renewal Period Below". I enter the date paid and the amount paid, If a member pays, for example, for one full year I will enter the amount paid and then click on the "Add 1 year" button. In this example I am adding 1 year or 4 quarters. This action will then determine the "Due Date" from (one of the three) the code below.

Private Sub Option104_Click() Me!DuesDue = DateAdd("q", 4, Me!DuesPaid)
FirstName.SetFocus
End Sub

As I said above if the actual date has passed I desire to uncheck the "Current" Yes/No field automatically. I am trying to get out of having to go through well over 100 members weekly and check their due date and then manually uncheck the "Current" box.

I have a Report (see attached in design view) that I run weekly which will be populated by the "Yes/No" field. I submit this report to our president who sends out emails requesting payment.

If you can suggest a better method of doing this I would be very willing to try it.

[June7]

I gave you example for one approach.

[hawzmolly]

A cleaned DB is attached

[June7]

The report isn't working because it is not finding the field names. Why did you post a db with a non-functioning report?

Did you try the suggested expression?

[hawzmolly]

Thank you June. I tried your code. Did not do what I need. See if the following explanation helps.

On the “frmMemberData” note the field for “Due Date”. The Due Date is based on the buttons to the right of “Current” and “Voting Member”. A member paid their dues on 4/1/2018 for one full year. When I click on the button labeled “Add 1 year” the “Due Date” will come up as 4/1/2019 based on this code:

Private Sub Option104_Click()
Me!DuesDue = DateAdd("q", 4, Me!DuesPaid)
FirstName.SetFocus
End Sub

What I do next is click on the “Current” box to check Yes (True). Since our members do not pay their dues on a certain date every year they pay when I send them an email that their dues are due which could be 1 quarter, 2 quarters, or 1 year (4 quarters) from when they last paid.

I run a “GBAMRC Members Dues Status” weekly. One of the query fields for this report is the “Current” field being “No” or “False”.

Now the heart of the matter, as you can see my report relies on the “Current” field being “No”. What I need is for the “Current” field to go “No” or False” 1 day after the actual calendar date without any input from me. I am thinking that this could be accomplished on opening the data base thus

Private Sub Option104_Click()
Me!DuesDue = DateAdd("d", 1, Me!DuesDue)
??? Code to change “Current” from “Yes” to “No”
FirstName.SetFocus
End Sub

I don’t know what code, or macro, etc. would do this. Seems to me that an “On Open” event or macro could do this.

Any suggestions? A better way to do this?

Thank you in advance for any help you can provide.

[hawzmolly]

Forgot to mention I am running Access 2016.

[June7]

Must compare the current date to some value from record to determine if something is past due. So exactly what are you comparing?

"1 day after actual calendar date" means when - 1 day after DuesDue date?

Current = Not (Date() >= DuesDue + 1)

which is essentially the same as

Current = Not (Date() > DuesDue)

Current will show unchecked (False) when Date() is greater than DuesDue because Not reverses result of comparison.

[hawzmolly]

Your question "1 day after actual calendar date" means when - 1 day after DuesDue date?" The answer is yes compare the actual date i.e., 2/5/2019 with the "DueDate" + 1 day. If the Due Date was 2/4/2019 then the Current check goes to "False" or unchecked.

What I need is for the “Current” field to go “No” or False” 1 day after the actual calendar date without any input from me every time I open this db.

Sorry if I am being confusing. If this can't be done I'll try another method.

[June7]

Then, as already suggested, use expression in checkbox ControlSource. If you want to save the calculated result to table, that will require code (macro or VBA).

[Minty]

If I can chime in here, what everyone is telling you is that essentially your yes/no field is redundant as you can always work out if it's overdue by the due date that you are already storing.

So rather than trying to keep the yes no field up to date, simply use the date as the switch / filter for your reporting and anything else you would have used the yes/no field for.

[hawzmolly]

I have been able to use "date functions" to get me to who is due when. It works great and thanx to all who made suggestions. Now using the date functions how do I go about changing the Yes to No in my "Current" field. I use the "Current" field for several other reports I have created. I can use the Due Date as a switch but I don't know the proper code to change from True to False in the "Current" field. I am going to play with "DoCmd" to try to get this done. Any suggested code? Thanx again for your help.