Find records within a date range

[tonymcc]

Hi all

I am having a head bursting nightmare trying to get a query to work, I will try and explain.

I have developed a database for the calculating storage costs in a warehouse. Customers are charged a daily rate while their pallets are stored in the depot warehouse. I have fields in a table called DateIn and dateOut both are short dates. I have forms to book pallets in and book them out and I can see all is fine up to this point.

I want to make a report called INVOICE based on a query. I have a form you open and select the date range for the invoice and enter then in to text boxes called txtStartDate and txtEndDate. I know how to pass these values to the query that has DateIn and DateOut in it

What I need to find is any pallets that were in the warehouse during the invoice period or any part of the invoice period.

If any one can offer advice or point me in the right direction I would be very grateful.

thanks
Tony

[gpl]

@tonymcc
The simplest way of testing for a period overlap of dates is to do the following :
Assume :
DateIn and DateOut are the dates the the pallet were in the warehouse
InvoiceSt and InvoiceEnd are the invoicing periods

Your where clause will look a bit like this (ignoring the time factor)

Where DateIn <= InvoiceEnd And DateOut >= InvoiceSt

In other words, it arrived in the warehouse before the invoice period ended and left after the invoice period started - if you hold NULL for the DateOut when you dont know when it will ship, dummy in the InvoiceEnd for the DateOut :

Where DateIn <= InvoiceEnd And IIF(DateOut IS NULL, InvoiceEnd, DateOut) >= InvoiceSt

Hope this helps
Graham

[tonymcc]

Thanks Graham you hit the nail on the head.
I sat and worked out all the permutations and then tried to build a query for this and it had about 7 OR's and 8 AND's which ended up being longer than my arm.
You came along and did it in a one liner!! I obviously could not see the forest for all the trees.
Thanks again Graham

Tony

[gpl]

Tony
I had to do this for work a couple of months ago, the original code was miles long, I couldnt work out why it was so complex.

Glad to have helped
Graham