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,...
Thanks, Tieme!
I tried Making the query but it didn’t work.
But I messed up. I should have mentioned that my Bills table only has a Payment Due “Day” (as is day Of the month due), not a Payment Due Date. I was calculating the actual due date by pulling the latest paymentdate from payments table and using the bills.dueday In a Dateadd function. Therefore
I tried Making the query but it didn’t work.
But I messed up. I should have mentioned that my Bills table only has a Payment Due “Day” (as is day Of the month due), not a Payment Due Date. I was calculating the actual due date by pulling the latest paymentdate from payments table and using the bills.dueday In a Dateadd function. Therefore
I meant “Therefore I’d have to figure out how to modify your query to also use the DateAdd function.
My dateadd funtion looks like:
Dateadd(“m”,1,Dateserial(Year(b.paiddate),Month(b.paiddate), b.dueday))
My dateadd funtion looks like:
Dateadd(“m”,1,Dateserial(Year(b.paiddate),Month(b.paiddate), b.dueday))
- Actually:
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.
