Aging Receivables Not working


G

Guest

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));
 
Ad

Advertisements

G

Guest

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];

Kernow Girl said:
Hi Lloyd - have you checked the dates? Is it the date for greater than 3
months? It's worth checking. Yours - Dika

Lloyd said:
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));
 
G

Guest

Hi Lloyd - have you checked the dates? Is it the date for greater than 3
months? It's worth checking. Yours - Dika
 
K

Ken Snell \(MVP\)

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>

Lloyd said:
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];

Kernow Girl said:
Hi Lloyd - have you checked the dates? Is it the date for greater than 3
months? It's worth checking. Yours - Dika

Lloyd said:
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));
 
G

Guest

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

Ken Snell (MVP) said:
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>

Lloyd said:
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];

Kernow Girl said:
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));
 
K

Ken Snell \(MVP\)

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>



Lloyd said:
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

Ken Snell (MVP) said:
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>

Lloyd said:
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));
 
Ad

Advertisements

G

Guest

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

Ken Snell (MVP) said:
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>



Lloyd said:
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

Ken Snell (MVP) said:
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));
 
K

Ken Snell \(MVP\)

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

Ken Snell (MVP) said:
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>



Lloyd said:
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));
 
G

Guest

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

Ken Snell (MVP) said:
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));
 
G

Guest

Just had a thought here. You can easily see the code I've sent you in
context if you generate a new Order Entrey DB from the Databases tab of the
New from General Templates window. This only takes half a minute and
ironically, the Enter a parameter value message even appears when you try and
run the report without any data in it.

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

Ken Snell (MVP) said:
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));
 
K

Ken Snell \(MVP\)

I apologize... have been overwhelmed with work these past few days... should
have a chance to reply to you this weekend.

--

Ken Snell
<MS ACCESS MVP>

news:[email protected]
 
Ad

Advertisements

K

Ken Snell \(MVP\)

I'm not fully following you here, I think, but let's see....

The first SQL statement that you show is the query that is generating data
for the Receivables Aging Report, right? So this is the report's
RecordSource query?

The second SQL statement that you show is the "Receivables Aging Report
Query" query from which the report gets its initial data, right?

The fact that you get the parameter prompt for the "91+Days" field suggests
that the problem is not with the queries, but with something in the report.
And my guess is that something in the report is expecting a different name
than what you have in the RecordSource query. Often, this happens when a
typo occurs or a field name is changed in a query, and the report and query
no longer match up. The fact that you see it for every record suggests that
the problem is in a subreport or in data that is tied to a subreport.

First, look carefully at the parameter prompt that you're getting. Is it for
"91+Days", or is it for "91+ Days" (note the space in the second name) -- I
ask because all your other fieldname aliases have a space in them. If it's
the latter, then the query no longer matches the field name of something in
the report.

Then, go into the report and look for controls that are bound to the field
name that is in the prompt. Change the Control Source of the field(s) to
match what is in the query.

Also, look at the Sorting & Grouping list (View | Sorting & Grouping) to see
if the wrong name is there. Change it if it is.

Also, look at any subreports that are in the report. Be sure that the
LinkChildFields and LinkMasterFields properties of the subreport control (in
the main report) are correctly spelled.

Then go into all subreports and repeat the above three steps for each
subreport.

--

Ken Snell
<MS ACCESS MVP>



Lloyd said:
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));
 
G

Guest

No problem at all. I've actually been hugely busy myself and was able to get
an extension on this particular project so all is well for timing right now.

Lloyd
 
G

Guest

Can you give me some terms or a phrase I can search for to track down the
posting you made? Thanks.

Lloyd
 
Ad

Advertisements


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

Top