Select start and end dates AND press Enter for all

[macherie]

I've created a query for a form where I want the user to be able to select between start and end dates, BUT I also want the user to have the option of pressing return for ALL dates. I'm a newbie and I'm not sure how/where to put this code.

What I've done thus far is put below in the criteria of my query:

Between [Enter Start Date - mm/yy] And [Enter End Date mm/yy]

This works fine, but I'd also like the user to simply press return for all dates...and I'ld like to tell the user in the parameter window to "press return for all".
When I hit enter I get NO dates.

Thanks for your time.
Dawn.

 

[raskew]

Try copying/pasting this query sql to a new query in Northwind.

When viewed in design mode, it will hopefully illustrate how the query is constructed to allow the user to either:
enter dates when prompted for Start and End dates, which will return a filtered output, or
just click OK for each of the prompts (without entering any dates), which will return of all the records in the table:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate
FROM Orders
WHERE ((([Enter Start Date]) Is Null) 
AND
   (([Enter End Date]) Is Null)) 
OR
   (((Orders.OrderDate) Between [Enter Start Date] 
AND
   [Enter End Date]) 
AND
   (([Enter Start Date]) Is Not Null));

HTH- Bob

 

[macherie]

Thank you Bob,

I did the code as you suggested and I'm getting the following error:

Sntax error in FROM Clause.

There is an order table and there are orderID, CustomerID and Orderdate fields.

Dawn.

 

[macherie]

Hold it

Hold the presses!

I just realized that I changed all the tables in Northwind to be proper naming convention. Orders table is now tblOrder.

ALL works wonderfuly....thank you so much Bob for your help and expertise.

Dawn.

 

[macherie]

Uh oh

I spoke too soon.

Yes I get all records...ALL the time though. Even if I enter a specific date, I will get all records.

Here's my code:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblSale.SaleID, tblSale.InvoiceNumber, tblSale.SaleDate, tblSale.PaymentDate, tblSale.PaidVia, tblModemSale.ModemID, tblModemSale.ModemCost, tblUnitSale.UnitID, tblUnitSale.UnitCost, tblCommunicationSale.CommunicationID, tblCommunicationSale.AnnualCost
FROM ((tblSale INNER JOIN tblCommunicationSale ON tblSale.SaleID = tblCommunicationSale.SaleID) INNER JOIN tblModemSale ON tblSale.SaleID = tblModemSale.SaleID) INNER JOIN tblUnitSale ON tblSale.SaleID = tblUnitSale.SaleID
WHERE ((([Enter Start Date]) Is Null)
AND
(([Enter End Date]) Is Null))
OR
(((tblSale.SaleDate) Between [Enter Start Date]
AND
[Enter End Date])
AND
(([Enter Start Date]) Is Not Null))
ORDER BY tblSale.SaleDate;

I know that I'm not referencing the tblSale for the saledate in the FROM statement, but I have all the other table names in the FROM with all these joins and... I don't know how to fix this. Can you still help.

Thank you
Dawn.

 

[raskew]

Dawn-

It's a little difficult, not having the tables to work with, but I suspect the problem may be in the joins.

To test, I'd start with just a bare-bones query using tblSale. Once determined that this was working as advertised, pull in your joins one at a time and test.

Note that I modified the code slightly from the original posting to account for all possibilities in your scenario:
1) User inputs both a Start and End Date
2) User inputs only Start Date
3) User inputs only End Date
4) User inputs neither date

PARAMETERS [Enter Start Date] DateTime
  , [Enter End Date] DateTime;
SELECT
    tblSale.SaleID
  , tblSale.InvoiceNumber
  , tblSale.SaleDate
FROM
   tblSale
WHERE
   ((([Enter Start Date]) Is Null) 
AND
   (([Enter End Date]) Is Null)) 
OR
   ((([Enter Start Date]) Is Not Null) 
AND
   (([Enter End Date]) Is Not Null) 
AND
   ((tblSale.SaleDate) Between [Enter Start Date] 
AND
   [Enter End Date])) 
OR
   ((([Enter Start Date]) Is Not Null) 
AND
   (([Enter End Date]) Is Null) 
AND
   ((tblSale.SaleDate)>=[Enter Start Date])) 
OR
   ((([Enter Start Date]) Is Null) 
AND
   (([Enter End Date]) Is Not Null) 
AND
   ((tblSale.SaleDate)<=[Enter End Date]));

Please post back with your results.

Bob

 

[macherie]

HURRAY!

It works wonderfully. Joins and all. Problem fixed and I learned along the way.
Thank you so much Bob.
Dawn.

 

[raskew]

Dawn-

Glad that it worked.

Bob

 

[GGib7711]

I have a club membership table tblMember containing a JoinDate field and an Active field (a Yes/No field indicating each member's current status) along with some other fields.

I have set the above [Enter start date] [Enter end date] criteria for the JoinDate field in a query and it runs fine. But I have a problem when I try to add a parameter for the Active field too in the crtieria. I don't know how to add it correctly.

I should appreciate any help.

 

[raskew]

gGib711-

Sorry, I just picked-up on your follow-on inquiry.

Try posting it as a new item, you're liable to get more responses.

Best wishes,

Bob