How do I Find Customers Not Billed This Month

[Seth Schrock]

I have a billing database that is used to bill customers every six months. All customers are billed at the same time. I would like to make a report that tells me if a customer didn't have a bill for a certain period. I have tblInvoices and tblCustomer. I need to run through every customer to see if it has a match in the tblInvoices for a specified billing period. How do I do this? I'm guessing that it is a query, but not like one that I've ever created. tblInvoices and tblCustomer are related using the CustomerID fields in both tables with InvoiceID being the PK for tblInvoices and CustomerID being the PK for tblCustomer.

[Rabbit]

The question in your title and the question in the body are different, I'm not sure which one you're actually asking about.

If you want to return all customers that had a bill for the current month, you can do

select *
from sometable
where year(billdate) = year(date())
   and month(billdate) = month(date())

However, if you're asking for all customers who are due for this month, you can do

select customer
from sometable
group by customer
having dateadd("mm", 6, max(billdate)) <= date()

[Seth Schrock]

Now that you mention it, the title is confusing. I was meaning if they had a bill as in I wanted those that did not have a bill. Sorry.

Here is an example of what I'm wanting. If I have three companies ABC, DEF, and GHI and companies ABC and DEF had bills but GHI didn't (because they hadn't bought anything) I want company GHI to be returned in the query. Does that make more sense?

[Rabbit]

I'm even more confused. If they haven't bought anything, why would they have a bill? Or why would you want to send them a bill?

[Seth Schrock]

I don't. I just want to know who they are. So I will have a stack of invoices and a list of those that didn't get one and between the two I will have every customer in my database listed.

[Rabbit]

I assume you have some sort of company table?

SELECT company
FROM companyTable
LEFT JOIN billsTable
ON companyTable.companyID = billsTable.companyID
WHERE billsTable.companyID IS NULL
   AND YEAR(billDate) = YEAR(DATE()) 
   AND MONTH(billDate) = MONTH(DATE())

This returns all companies that did not have a bill in the current year and month.

[Seth Schrock]

Great! I'll give that a try when I go back to work on Monday. Sorry for all the confusion.

[Seth Schrock]

Okay. I can get it to work if I leave off the date criteria. So this works:

SELECT CustomerName
FROM tblCustomer 
LEFT JOIN tblInvoices ON tblCustomer.CustomerID = tblInvoices.CustomerID
WHERE tblInvoices.CustomerID IS NULL

If I add the following to the bottom of that:

AND Year(BeginDate) = Year(Date())
AND Month(BeginDate) = Month(Date())
 

I get nothing. It just so happens that right now I have only one record in tblInvoices. I also tried the following which is closer to what I want:

SELECT CustomerName
FROM tblCustomer 
LEFT JOIN tblInvoices ON tblCustomer.CustomerID = tblInvoices.CustomerID
WHERE tblInvoices.CustomerID IS NULL
AND EndDate = Forms!frmHome!cboDateSelect
 

Still nothing. Here is the structure of the two tables:

tblCustomer
CustomerID
CustomerName
Address

tblInvoices
InvoiceID
CustomerID
BeginDate
EndDate
 

Using the BeginDate and EndDate I'm able to calculate the billing period (which is always six months). The combo box referenced in my last section of code (Forms!frmHome!cboDateSelect) uses a SELECT DISTINCT query on tblInvoices to pull the EndDate values that have been used. I have even tried to make it not equal instead of equal on the off chance that we were thinking of it wrong, but that didn't help.

[Rabbit]

Sorry, I made a mistake. Try putting those date criteria in the join's ON clause rather then the WHERE clause.

[Seth Schrock]

I'm not sure how to do that.

[Rabbit]

SELECT company 
FROM companyTable 
LEFT JOIN billsTable 
ON companyTable.companyID = billsTable.companyID  
   AND YEAR(billDate) = YEAR(DATE())  
   AND MONTH(billDate) = MONTH(DATE())
WHERE billsTable.companyID IS NULL 

[Seth Schrock]

I'm getting an error message that says "Join expression not supported" and then it highlights

YEAR(BeginDate) = YEAR(DATE())

Just for fun I deleted that line and then I got the same error message and it highlighted the MONTH portion of the code.

[NeoPa]

If you're creating the SQL from within VBA then your code to produce the string could be :

Dim strSQL As String
Dim datEOM As Date
 
datEOM = DateAdd("m", 1, Date)
datEOM = DateAdd("d", -Days(datEOM), datEOM)
strSQL = "SELECT [Company] " & _
         "FROM   [CompanyTable] AS [tCT]" & _
         "       LEFT JOIN" & _
         "       [BillsTable] AS [tBT]" & _
         "  ON   (tCt.CompanyID = tBT.CompanyID)" & _
         " AND   (tCT.BillDate Between #1/%F#" & _
         "                     And     #%T#) " & _
         "WHERE  (tBT.CompanyID Is Null)"
strSQL = Replace(strSQL, "%F", Format(Date, "m\/yyyy"))
strSQL = Replace(strSQL, "%T", Format(datEOM, "d\/m\/yyyy"))

It looks more complicated than it needs to be, but handling dates has many gotchas involved and this handles them all (hopefully).

[Seth Schrock]

I wasn't using VBA. I guess I could.

This might be a stupid question, but can you base a report off of a query built in VBA? If so, I'm not sure how.

[NeoPa]

Not exactly, but if you are opening the report from VBA you can leave the date filtering out of the base query and apply it as the WhereCondition parameter of the DoCmd.OpenReport() call.

[Rabbit]

What's the SQL look like that gave you the error?

[Seth Schrock]

SELECT CustomerName
FROM tblCustomer LEFT JOIN tblInvoices ON tblCustomer.CustomerID = tblInvoices.CustomerID
AND EndDate = Forms!frmHome!cboDateSelect
WHERE tblInvoices.CustomerID IS NULL
 

I have to type it in everytime because it won't let me save it with the added criteria in the ON part.

@NeoPa, I'll give that a try.

[Rabbit]

I guess Access doesn't allow that type of join condition. No matter, a subquery can be used as a workaround.

SELECT company  
FROM companyTable  
LEFT JOIN (
   SELECT companyID 
   FROM billsTable
   WHERE YEAR(billDate) = YEAR(DATE())   
      AND MONTH(billDate) = MONTH(DATE()) 
) bt
ON companyTable.companyID = billsTable.companyID   
   AND WHERE bt.companyID IS NULL  

[Seth Schrock]

I tried the subquery like you had it and I get a syntax error highlighting the table name in the subquery. So instead of having a subquery, I created a separate query to fix the date problem and then joined the main query to it instead of billsTable.

[NeoPa]

FYI. The combination would be a query of :

SELECT tC.CustomerName
       tI.BillDate
FROM   [tblCustomer] AS [tC]
       LEFT JOIN
       [tblInvoices] AS [tI]
  ON   tC.CustomerID = tI.CustomerID
 AND   tI.EndDate = Forms!frmHome!cboDateSelect
WHERE  tI.CustomerID IS NULL

The calling code would incorporate :

Dim strSQL As String
Dim datEOM As Date
 
datEOM = DateAdd("m", 1, Date)
datEOM = DateAdd("d", -Days(datEOM), datEOM)
strSQL = "([BillDate] Between #1/%F# And #%T#)"
strSQL = Replace(strSQL, "%F", Format(Date, "m\/yyyy"))
strSQL = Replace(strSQL, "%T", Format(datEOM, "d\/m\/yyyy"))

I've assumed [BillDate] is a field in [tblInvoices] and it's a separate field from [EndDate], which I've also assumed is in [tblInvoices].

PS. I'm just off shortly, but I've just noticed why Rabbit moved away from this idea. It doesn't work well for your requirement, which is to find missing records. I jumped in late and interpreted what was already there without considering the whole situation clearly enough. If I get a chance to later, I'll have a proper think about it, but I expect you'll find Rabbit's proposed solution is required.

[NeoPa]

I've had a quick look back and see that the fields for [tblInvoices] was actually posted at one point. I don't see any explanation that actually ties in the two date fields to your question though. Is one or other of them the one you want to test? Is there some sort of overlap issue that you haven't mentioned? Important information for the first post I would suggest.

[Rabbit]

@Seth, that's fine, you can do it in its own separate query if you wish. The problem you had with the subquery was probably a conflicting alias.

[Seth Schrock]

@NeoPa, Together, the two date fields make up the billing period, but I was planning on just testing EndDate. I'll try to remember to make sure my first post contains enough information. Thanks for the tip.

@Rabbit, I didn't have an alias which might be why it had the problem. In my translation to my fields/tables, I missed that detail. I'll test it when I get back to work. Thanks.