Hello Forum Members:

I would like to get some help here.
I have multiple invoice dates such as "Invoice Date", "Invoice Date2", "Invoice Date3", "Invoice Date4" and "Invoice Date5" in a table.
And in a query, this is the SQL statement:
SELECT DISTINCTROW Products.ProductName, Products.ProductDescription, [Inventory Transactions].*, Products.[Part #], Products.Supplier
FROM Products INNER JOIN [Inventory Transactions] ON Products.ProductID=[Inventory Transactions].ProductID
WHERE ((([Inventory Transactions].[Invoice Date])>=[forms]![Report Date Range]![BeginDate] And ([Inventory Transactions].[Invoice Date])<=[forms]![Report Date Range]![EndDate]));

I would like to place all "Invoice Date", "Invoice Date2", "Invoice Date3", "Invoice Date4" and "Invoice Date5" in this query to produce a report by typing BeginDate and EndDate.
Would you be able to show me how this can be done on this query?
I truly appreciate your help.

With best regards,

Hello Forum Members:

Sorry that I put the old SQL.
This is what I have right now:

SELECT DISTINCTROW Products.ProductName, Products.ProductDescription, [Inventory Transactions].*, Products.[Part #], Products.Supplier
FROM Products INNER JOIN [Inventory Transactions] ON Products.ProductID=[Inventory Transactions].ProductID
WHERE ((([Inventory Transactions].[Invoice Date])>=[forms]![Report Date Range]![BeginDate] And ([Inventory Transactions].[Invoice Date]) Or ([Inventory Transactions].[Invoice Date2]) Or ([Inventory Transactions].[Invoice Date3]) Or ([Inventory Transactions].[Invoice Date4]) Or ([Inventory Transactions].[Invoice Date5]) <=[forms]![Report Date Range]![EndDate]));

However, when I type like this;
01012012 as BeginDate and 033012 as EndDate, the report shows the data from 2011 as well.
I would like to limit the data from 01/01/2012 to 03/30/2012.

Can you see what else I need to change?
Thank you.

Hi Hiro,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

Having multiple invoice dates is usually considered a bad design. Are you familiar with database Normalization?

If it's not too late, I would recommend redesigning your table to remove the multiple invoice dates.

Just my 2 cents...

Hello theDBguy

Thank you for your reply.
My Access knowledge is still in a beginning stage, so database normalization may be in the future.
By the way, I am using Access 2007.
In this database, I have seen no more than 3 invoice dates for the past 2 years, so I came up with 5 invoice dates.
In the past I simply updated the new invoice date on the "Invoice Date" on a table, but now I would like to keep all the invoice dates.
Can I still accomplish this by [Invoice Date], [Invoice Date2], [Invoice Date3], [Invoice Date4] and [Invoice Date5] using BeginDate and EndDate?

Thank you for looking into this.
With best regards,

you will have to put the criteria separately:

"WHERE ([Inventory Transactions].[Invoice Date] Between #" & Format([forms]![Report Date Range]![BeginDate],"m\/d\/yyyy") & " And #" & Format([forms]![Report Date Range]![EndDate], "m\/d\/yyyy")) OR " & _
"([Inventory Transactions].[Invoice Date2] Between #" & Format([forms]![Report Date Range]![BeginDate],"m\/d\/yyyy") & _ " And #" & Format([forms]![Report Date Range]![EndDate], "m\/d\/yyyy") ) OR " & _
"([Inventory Transactions].[Invoice Date3] Between #" & Format([forms]![Report Date Range]![BeginDate],"m\/d\/yyyy") & _ " And #" & Format([forms]![Report Date Range]![EndDate], "m\/d\/yyyy") ) OR " & _
"([Inventory Transactions].[Invoice Date4] Between #" & Format([forms]![Report Date Range]![BeginDate],"m\/d\/yyyy") & " _ " And #" & Format([forms]![Report Date Range]![EndDate], "m\/d\/yyyy") ) OR " & _
"([Inventory Transactions].[Invoice Date5] Between #" & Format([forms]![Report Date Range]![BeginDate],"m\/d\/yyyy") & " _ " And #" & Format([forms]![Report Date Range]![EndDate], "m\/d\/yyyy") & ") "

> My Access knowledge is still in a beginning stage, so database normalization may be in the future.

Sorry, but if you lack knowledge about normalization or implementation of this, that should be your top priority.

/gustav