Forum Discussion
Bills Paid Database
Hi there: I’m using Access 2016. I started database for bill payments. In that database i have a first table called Bills where I have fields concerning my bills, including a BillID field, DueDate,...
Actually:
Dateadd(“m”,1,Dateserial(Year(p.paiddate),Month(p.paiddate), b.dueday))
Dateadd(“m”,1,Dateserial(Year(p.paiddate),Month(p.paiddate), b.dueday))
Hopefully, this query will do want you want:
SELECT b.BillID, b.Amount, b.DueDate
FROM Bills AS b LEFT JOIN Payments AS p ON b.BillID = p.BillID
WHERE (p.BillID Is Null) AND (SELECT DateAdd("m", 1, Dateserial( Year(MAX(DatePaid)), Month(MAX(DatePaid)), b.DueDay)) FROM Payments)<=(SELECT TOP 1 PayDate FROM PayDays WHERE PayDate >= Date() ORDER BY PayDate);
Good luck,
Tieme
- Thanks! That certainly works better than anything I’ve been able to come up with on my own.
However, for some reason it is not returning all expected records.
So far, I only notice my mortgage Bill is missing from the results. It is due on the 1st of each month. And my next payday is on September 3rd. So, the mortgage bill should be showing up in the results, but doesn’t.
