Determine if a shipment has been billed for the month

Question

I have a warehousing database that uses the "ShipRefs" (Labeled as shipment on the form) field to associate items to a particular shipment. I am now creating an invoicing form in order to generate a bill for that customer per shipment per month. I can select the shipment based on the customer via a combo box but I cannot filter those shipments based on if they have been billed for the month.

What would be the best way to determine if a shipment has been billed for the current month and only show those that have not been billed in the combo box? (Please be aware the the "Last Billdate" field was a failed attempt at this) Data:

QUERY for the shipment combo box row source:

SELECT DISTINCT ItemList.ShipRef, ItemList.CRef, InvoiceData.[Last Billdate]
FROM ItemList AS ItemList_1, ItemList INNER JOIN InvoiceData ON ItemList.ShipRef = InvoiceData.Shipment
WHERE (((ItemList.CRef)=[Forms]![InvoiceData]![Customer]) AND ((InvoiceData.[Last Billdate])>=Date()-30));

and the only attempt I made at determining if the current record has been billed is the "Last Bill Date" field in which I tried to use the `DateDiff` function in a query criteria. I will add that query to the OP — Rynoc, Sep 01 '15 at 13:25
Yes, I want only the records that do not fall within the current month and yes only records where the invoice date is null. — Rynoc, Sep 01 '15 at 13:38

HansUp · Accepted Answer · 2015-09-01T13:52:32.810

From your response ...

I want only the records that do not fall within the current month and yes only records where the invoice date is null

I think DateSerial() should be useful here to determine target dates. Here is an Immediate window example:

? DateSerial(Year(Date()), Month(Date()), 1)
9/1/2015 

? DateSerial(Year(Date()), Month(Date()) + 1, 1)
10/1/2015

Here is a query which uses those DateSerial expressions and also restricts the result set to those rows where Invoice Date Is Null:

SELECT id.*
FROM InvoiceData AS id
WHERE
    id.[Invoice Date] Is Null
    And
        (
               id.[Last BillDate] < DateSerial(Year(Date()), Month(Date()), 1)
            Or id.[Last BillDate] >= DateSerial(Year(Date()), Month(Date()) + 1, 1)
        );

Determine if a shipment has been billed for the month

1 Answers1