I have an Access 2007 Database that I am trying to query the following. Here are the tables
Name Address City State Zip
I have a bunch of data in different states but I want the ability to search by state. This part is easy and I know how to do this already just by putting this into the Criteria but what I am trying to accomplish is if I don't put anything in for State that it will just show me all of the data.
I have tried this by putting in = State in the criteria then run the report and when prompted to put state I put CA and it works perfect but when I don't put anything in this prompt it will not give me any data and I want it to show me every state.
So I think it would be easier if I showed you the SQL View that I am using so you could help me in this.
SELECT Orders.agent, Orders.CustomerID, Orders.BillToFirst, Orders.BillToLast, Orders.BillToAddress, Orders.BillToCity, Orders.BillToState, Orders.BillToZip, Leads.PhoneDay, Orders.OrderDate, Items.Description, Items.Qty, Orders.OrderStatus
FROM Leads INNER JOIN (Orders INNER JOIN Items ON (Orders.OrderID = Items.OrderId) AND (Orders.CustomerID = Items.CustomerID)) ON Leads.XTELELINK = Items.CustomerID
WHERE (((Orders.agent)>=[Enter Begining Agent ID] And (Orders.agent)<=[Enter End Agent ID]) AND ((Orders.OrderDate)>=[Enter Start date] And (Orders.OrderDate)<=[Enter End Date]))
ORDER BY Orders.agent, Orders.OrderDate;