Report Date Range Form -- Error

J

jwr

I have a report - Customer Sales - with a Report Date Range Form to open
the report. The date range can only be no greater than 8-5-05. I cannot
see why or how this is happening. I have used the same report date range
form (changing the report name, of course) and it works fine. Below is the
SQL behind my report.

Please look at this and see if you see why I am getting an error message.
If I go beyond 8-5-05, the message is that it is typed incorrectly or it is
too complicated to evaluate.


SELECT DISTINCTROW Customers.CompanyName, Sum([Sales by Customer
Subquery].[Total Sales]) AS [Total Sales], Sum(CLng([Sales by Customer
Subquery].[Total Sales]*[Sales by Customer Subquery].SalesTaxRate*100)/100)
AS [Sales Tax], Sum([Sales by Customer Subquery].FreightCharge) AS [Total
Freight], Sum([Sales by Customer Subquery].[Total Units]) AS [Total Units]
FROM Customers INNER JOIN [Sales by Customer Subquery] ON
Customers.CustomerID = [Sales by Customer Subquery].CustomerID
WHERE ((([Sales by Customer Subquery].OrderDate)>=[forms]![Report Date
Range]![Beginning Order Date] And ([Sales by Customer
Subquery].OrderDate)<=[forms]![Report Date Range]![Ending Order Date]))
GROUP BY Customers.CompanyName;


Joy
 
A

Allen Browne

The message indicates that JET does not understand the data.
That's probably because it is confused about the data types.

To help it out:
1. Define the data type for the text boxes.
If the text boxes are unbound, open your form in design view, and set their
Format property to Short Date or similar. This helps Access understand that
they are intended to be dates, and also prevents the user from ending bad
dates. Save the form.

2. Declare the parameters in the query.
Open the query in design view.
Choose Parameters on the Query menu.
Enter 2 rows into the little dialog:
[forms]![Report Date Range]![Beginning Order Date] Date/Time
[forms]![Report Date Range]![Ending Order Date] Date/Time
This indicates to JET (the query engine) to treat the values as dates.

The query should then be able to understand the data. We have assumed that
OrderDate is a date/time field in a table. If it is a text field or a
calculated field, JET may need further help by typecasting. More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
 
J

jwr

Allen - I verified that all my information agreed with your reply. I then
went back to the query - SQL listed below. I took the date range our of the
query and got the message "Invalid Use of Null". I then added back the
criteria of between beginning dates and ending dates and the query displayed
the labels with NO information. I definitely have orders with order dates!
The report also responds - "No data"

SELECT DISTINCTROW Orders.CustomerID, Orders.OrderDate,
Orders.FreightCharge, Orders.SalesTaxRate,
Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100) AS [Total Sales],
Sum([Order Details].Quantity) AS [Total Units]
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.CustomerID, Orders.OrderDate, Orders.FreightCharge,
Orders.SalesTaxRate;

Allen Browne said:
The message indicates that JET does not understand the data.
That's probably because it is confused about the data types.

To help it out:
1. Define the data type for the text boxes.
If the text boxes are unbound, open your form in design view, and set their
Format property to Short Date or similar. This helps Access understand that
they are intended to be dates, and also prevents the user from ending bad
dates. Save the form.

2. Declare the parameters in the query.
Open the query in design view.
Choose Parameters on the Query menu.
Enter 2 rows into the little dialog:
[forms]![Report Date Range]![Beginning Order Date] Date/Time
[forms]![Report Date Range]![Ending Order Date] Date/Time
This indicates to JET (the query engine) to treat the values as dates.

The query should then be able to understand the data. We have assumed that
OrderDate is a date/time field in a table. If it is a text field or a
calculated field, JET may need further help by typecasting. More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

jwr said:
I have a report - Customer Sales - with a Report Date Range Form to open
the report. The date range can only be no greater than 8-5-05. I cannot
see why or how this is happening. I have used the same report date range
form (changing the report name, of course) and it works fine. Below is
the
SQL behind my report.

Please look at this and see if you see why I am getting an error message.
If I go beyond 8-5-05, the message is that it is typed incorrectly or it
is
too complicated to evaluate.


SELECT DISTINCTROW Customers.CompanyName, Sum([Sales by Customer
Subquery].[Total Sales]) AS [Total Sales], Sum(CLng([Sales by Customer
Subquery].[Total Sales]*[Sales by Customer
Subquery].SalesTaxRate*100)/100)
AS [Sales Tax], Sum([Sales by Customer Subquery].FreightCharge) AS [Total
Freight], Sum([Sales by Customer Subquery].[Total Units]) AS [Total Units]
FROM Customers INNER JOIN [Sales by Customer Subquery] ON
Customers.CustomerID = [Sales by Customer Subquery].CustomerID
WHERE ((([Sales by Customer Subquery].OrderDate)>=[forms]![Report Date
Range]![Beginning Order Date] And ([Sales by Customer
Subquery].OrderDate)<=[forms]![Report Date Range]![Ending Order Date]))
GROUP BY Customers.CompanyName;

Joy
Click to expand...
Click to expand...
 
A

Allen Browne

The "invalid use of Null" message would be triggered by the CLng() type
conversion. It can't handle Nulls, so include Nz() inside it, i.e.:
Sum(CLng(Nz([Quantity]*[UnitPrice]*(1-[Discount])*100,0))/100) AS [Total
Sales],

Once that's working, you can try putting the WHERE clause back in, and don't
forget the PARAMETERS clause at the start.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
jwr said:
Allen - I verified that all my information agreed with your reply. I then
went back to the query - SQL listed below. I took the date range our of
the
query and got the message "Invalid Use of Null". I then added back the
criteria of between beginning dates and ending dates and the query
displayed
the labels with NO information. I definitely have orders with order
dates!
The report also responds - "No data"

SELECT DISTINCTROW Orders.CustomerID, Orders.OrderDate,
Orders.FreightCharge, Orders.SalesTaxRate,
Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100) AS [Total Sales],
Sum([Order Details].Quantity) AS [Total Units]
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.CustomerID, Orders.OrderDate, Orders.FreightCharge,
Orders.SalesTaxRate;

Allen Browne said:
The message indicates that JET does not understand the data.
That's probably because it is confused about the data types.

To help it out:
1. Define the data type for the text boxes.
If the text boxes are unbound, open your form in design view, and set their
Format property to Short Date or similar. This helps Access understand that
they are intended to be dates, and also prevents the user from ending bad
dates. Save the form.

2. Declare the parameters in the query.
Open the query in design view.
Choose Parameters on the Query menu.
Enter 2 rows into the little dialog:
[forms]![Report Date Range]![Beginning Order Date] Date/Time
[forms]![Report Date Range]![Ending Order Date] Date/Time
This indicates to JET (the query engine) to treat the values as dates.

The query should then be able to understand the data. We have assumed
that
OrderDate is a date/time field in a table. If it is a text field or a
calculated field, JET may need further help by typecasting. More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

jwr said:
I have a report - Customer Sales - with a Report Date Range Form to
open
the report. The date range can only be no greater than 8-5-05. I cannot
see why or how this is happening. I have used the same report date range
form (changing the report name, of course) and it works fine. Below is
the
SQL behind my report.

Please look at this and see if you see why I am getting an error message.
If I go beyond 8-5-05, the message is that it is typed incorrectly or
it
is
too complicated to evaluate.


SELECT DISTINCTROW Customers.CompanyName, Sum([Sales by Customer
Subquery].[Total Sales]) AS [Total Sales], Sum(CLng([Sales by Customer
Subquery].[Total Sales]*[Sales by Customer
Subquery].SalesTaxRate*100)/100)
AS [Sales Tax], Sum([Sales by Customer Subquery].FreightCharge) AS [Total
Freight], Sum([Sales by Customer Subquery].[Total Units]) AS [Total Units]
FROM Customers INNER JOIN [Sales by Customer Subquery] ON
Customers.CustomerID = [Sales by Customer Subquery].CustomerID
WHERE ((([Sales by Customer Subquery].OrderDate)>=[forms]![Report Date
Range]![Beginning Order Date] And ([Sales by Customer
Subquery].OrderDate)<=[forms]![Report Date Range]![Ending Order Date]))
GROUP BY Customers.CompanyName;

Joy
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report subtotal errors 0
report subtotals incorrect 2
report group subtotals incorrect 6
Calculating a grand total for an unbound control in a report 9
Change a report to a query 1
Adding report with another 0
HELP NEEDED WITH REPORT 1
Creating Crosstab Reports from Crosstab Queries 4

Top