By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,826 Members | 2,384 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,826 IT Pros & Developers. It's quick & easy.

Find records within a date range

P: 2
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
Dec 2 '08 #1
Share this Question
Share on Google+
3 Replies


100+
P: 152
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)

Expand|Select|Wrap|Line Numbers
  1. 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 :

Expand|Select|Wrap|Line Numbers
  1. Where DateIn <= InvoiceEnd And IIF(DateOut IS NULL, InvoiceEnd, DateOut) >= InvoiceSt
Hope this helps
Graham
Dec 2 '08 #2

P: 2
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
Dec 2 '08 #3

100+
P: 152
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
Dec 4 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.