Search code examples
sqlms-accessms-access-2007jet-sql

Determine if a shipment has been billed for the month

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. enter image description here

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:

enter image description here

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));
Solution

  • 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)
        );