No problem, and thanks for whatever snippets of time you can manage now.
Here's the SQL code that generates the Receivables Aging Report. When I
try
to run it I get an "Enter parameter" message for each of the "91+Days"
field.
This is followed by an error message which says... "The expression is
either
typed incorrectly or is too complex to be evaluated..."
SELECT DISTINCTROW
Sum(IIf((Date()-[ShipDate])<31,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments]),0)) AS [Current], Sum(IIf((Date()-[ShipDate])<61 And
(Date()-[ShipDate]>30),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments]),0)) AS [31-60 Days], Sum(IIf((Date()-[ShipDate])<91 And
(Date()-[ShipDate]>60),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments]),0)) AS [61-90 Days],
Sum(IIf((Date()-[ShipDate])>90,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments]),0)) AS [91+Days],
Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total Payments]))
AS
Balance, [Receivables Aging Report Query].CompanyName
FROM [Receivables Aging Report Query]
WHERE ((([Receivables Aging Report Query].ShipDate) Is Not Null))
GROUP BY [Receivables Aging Report Query].CompanyName
HAVING (((Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments])))>0));
Here's the SQL query that the above report is based on. When I try to run
it I get an "Invalid use of Null" message.
SELECT DISTINCTROW Orders.ShipDate, Customers.CompanyName,
Orders.FreightCharge, Orders.SalesTaxRate,
Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100) AS LineTotal,
[Sum
Of Payments Query].[Total Payments]
FROM Customers INNER JOIN ((Orders LEFT JOIN [Sum Of Payments Query] ON
Orders.OrderID = [Sum Of Payments Query].OrderID) LEFT JOIN [Order
Details]
ON Orders.OrderID = [Order Details].OrderID) ON Customers.CustomerID =
Orders.CustomerID
GROUP BY Orders.ShipDate, Customers.CompanyName, Orders.FreightCharge,
Orders.SalesTaxRate, [Sum Of Payments Query].[Total Payments];
Thanks again.
Lloyd
Ken Snell (MVP) said:
My preference is to not jump right into looking at a database file <
smile
.... could you post a few examples of data and results?
My time is going to be very limited until the weekend (busy work
schedule),
so I wouldn't have a chance to look at a file until then anyway.
--
Ken Snell
<MS ACCESS MVP>
Lloyd said:
Thanks for your thoughts and time, Ken. I believe this is more
complicated
than what can be solved without you actually seeing the file. Is it
possible
for me to send it to you? I fully understand if not, but thought I'd
ask.
You'd quickly ber able to see what I'm talking about with the file in
hand.
Thanks.
Lloyd
:
I have no idea what you mean by "bogus values"? Can you give us more
details
about the data?
Is the database set to use 4-digit years by default (Tools | Options)?
Is
it
by chance a problem with two-digit years being misinterpreted?
--
Ken Snell
<MS ACCESS MVP>
Thanks, Ken.
I'm managed to get the query to work without error messages, but to
my
chagrin the report based on this query is still not producing valid
results.
Specifically, the values it brings up are bogus. The SQL statements
that
are
supposed to pull the data into the report are listed at the
beginning
of
this
conversation. What's odd to me is that the code has not been
altered
since
the DB was first created 3 years ago.
I've tried compacting the DB, turning off the auto correct features
in
Tools
| Options | General, all to no avail. Any suggestions on how to
troubleshoot
this would be most appreciated?
Lloyd
:
Use the Nz function to convert a Null value from a field into a
default
value (e.g., a zero). See Nz function in Help file.
--
Ken Snell
<MS ACCESS MVP>
Thanks for your feedback. Before I follow your lead I've
realized
that
that
the problem may be starting from inside the initial query which
is
returning
me an
"Invalid use of Null" error. Here's the code from the query.
Again,
this
is the default code that came with the original DB generated my
MS
Access.
I'm guessing I need to take account of nulls in this code, but am
not
sure
how to do that. Thanks again for any suggestions.
Lloyd
SELECT DISTINCTROW Orders.ShipDate, Customers.CompanyName,
Orders.FreightCharge, Orders.SalesTaxRate,
Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100) AS
LineTotal,
[Sum
Of Payments Query].[Total Payments]
FROM Customers INNER JOIN ((Orders LEFT JOIN [Sum Of Payments
Query]
ON
Orders.OrderID = [Sum Of Payments Query].OrderID) LEFT JOIN
[Order
Details]
ON Orders.OrderID = [Order Details].OrderID) ON
Customers.CustomerID
=
Orders.CustomerID
GROUP BY Orders.ShipDate, Customers.CompanyName,
Orders.FreightCharge,
Orders.SalesTaxRate, [Sum Of Payments Query].[Total Payments];
:
Hi Lloyd - have you checked the dates? Is it the date for
greater
than
3
months? It's worth checking. Yours - Dika
:
I've been using an Access DB that I customized from the
default
Order
Entry
project. The DB is used to record book sales and prepare
Sales
Tax
returns
for a professor. The Aging Receivables report and query have
been
working
fine for a number of years, but now is pulling up incorrect
amounts.
If
anyone has suggestions on how to troubleshoot this it would be
greatly
appreciated? The query code that generates the report is
listed
below.
It is
the default code that comes with the original DB project.
Thanks for any help.
Lloyd
SELECT DISTINCTROW
Sum(IIf((Date()-[ShipDate])<31,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments]),0)) AS [Current], Sum(IIf((Date()-[ShipDate])<61
And
(Date()-[ShipDate]>30),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments]),0)) AS [31-60 Days], Sum(IIf((Date()-[ShipDate])<91
And
(Date()-[ShipDate]>60),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments]),0)) AS [61-90 Days],
Sum(IIf((Date()-[ShipDate])>90,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments]),0)) AS [91+Days],
Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments])) AS
Balance, [Receivables Aging Report Query].CompanyName
FROM [Receivables Aging Report Query]
WHERE ((([Receivables Aging Report Query].ShipDate) Is Not
Null))
GROUP BY [Receivables Aging Report Query].CompanyName
HAVING
(((Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments])))>0));
