I know I can do this through a query and then make a report of it. But I would like to do it if possible through VBA coding.
I have a table of given X contracts that when their ending date come with in 60 days of renewal I want them to show up on the report, and ONLY thoese ones. How would this be done through code though?
I have tried it with a loop to go through all records in the table and set the visible properties, BUT it sets all of the companys visible properties, so if one of thoese records is below or equal to 60 it shows all companys.
Private Sub Report_Open(Cancel As Integer)
On Error GoTo BillyErrors:
Dim NowDate As Date
Dim DateEnd As Date
Dim TimeLeft As Single
Dim db As Database
Dim Rec As Recordset
Set db = CurrentDb()
Set Rec = db.OpenRecordset("Contacts")
Originally posted by M Owen
Why don't you query for those contracts that are within 60 days of renewal? Something like:
SELECT * FROM MyTableNameHere AS t1 WHERE (DateDiff("d",t1.RenewalDate,t1.ContractDate) < 60);
That's a pretty good solution, but it requires a minor tweek. Datediff returns a negative when the first date evaluated is larger then the second, therefore all accounts that are passed the current date will also be returned as they would be negative. Also, it appears that he only has one date being returned from an actual datasource.
SELECT * FROM MyTableNameHere AS t1 WHERE (DateDiff("d",t1.RenewalDate, date()) < 60) AND (RenewalDate =< Date())
Well figured out the last part......just put the SQL query in the Row Source now I just have a bad query because the report is showing all data and not just the contracts that are coming with in 60 days see below.
SELECT * FROM Contacts AS MeMyselfAndI WHERE (((DateDiff("d",MeMyselfandI.[End_Date],Now()))))<60