My Query works but returns NOTHING please help

[AidanEnos]

All this is supposed to do is look at the HS_OrderTable and prompt for date range Between, that works fine. It's going to run from a switchboard command button "Report on users for date range" that opens OrderedUsersSummaryReport whose recordsource is this Query OrderedUsersSummaryQuery.

Seems simple enough.

The rest is supposed to take other fields from the table and add the values together - it works great on the forms.

When I run this query it prompts me for the dates, I enter them in the same short date fromat I have done in the table 11/01/2007 and 11/30/2007 (I created several records with Initial Order Date 11/23/2007 for testing purposes). It accepts the dates just fine then spits back a BLANK report at me!!!!

Any help on what I missed???

SELECT DISTINCTROW HS_OrderScopeTable.OrderNumber, HS_OrderScopeTable.CustomerName, HS_OrderScopeTable.CustomerIdentifier, HS_OrderScopeTable.InitialTargetDate, HS_OrderScopeTable.InitialOrderDate, HS_OrderScopeTable.TotalEnterpriseIPOrdered, HS_OrderScopeTable.TotalEnterpriseIPActivated, HS_OrderScopeTable.TotalACDIPOrdered, HS_OrderScopeTable.TotalACDActivated, HS_OrderScopeTable.Installation_totals
FROM HS_OrderScopeTable
WHERE (((HS_OrderScopeTable.InitialOrderDate) Between [Enter Start Date] And [Enter End Date]) AND ((HS_OrderScopeTable.TotalEnterpriseIPOrdered)=[N0137959Ordered]+[N0137960Ordered]+[N0137961Ordered]+[N0137962Ordered]+[N0137963Ordered]+[N0137964Ordered]) AND ((HS_OrderScopeTable.TotalEnterpriseIPActivated)=[N0137959Activated]+[N0137960Activated]+[N0137961Activated]+[N0137962Activated]+[N0137963Activated]+[N0137964Activated]) AND ((HS_OrderScopeTable.TotalACDIPOrdered)=[N0153564Ordered]+[N0153565Ordered]) AND ((HS_OrderScopeTable.TotalACDActivated)=[N0153564Activated]+[N0153565Activated]));

 

[genomon]

Try this:
Between #[Enter Start Date]# And #[Enter End Date]#

Sure, the early bird gets the worm, but the second mouse gets the cheese in the trap.

 

[AidanEnos]

I added the hashes and now it says it has an invalid date format and won't let me save it.

Thanks

P.S. if this is any help I have the date format in the sourcetable is 99/99/0000;0;_

 

[lespaul]

if this is any help I have the date format in the sourcetable is 99/99/0000;0;

ok, but is the data type a DATE, that looks like an input mask...

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[AidanEnos]

yes - it won't let you choose that input mask if you don't have the field type set for date/time

 

[dhookom]

AidanEnos,
It seems to me that the first thing you would try and report to us is if this works with out the date parameters.

Also, IMHO, parameter prompts should never be used faq701-6763.

Finally, I question the table structure. I don't know if you have any command over the structure but it seems more like a spreadsheet than a relational database.

Duane MS Access MVP
Now help me support United Cerebral Palsy