RE: Aging Receivables Not working

[Lloyd]

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" wrote:

> Hi Lloyd - have you checked the dates? Is it the date for greater than 3
> months? It's worth checking. Yours - Dika
>
> "Lloyd" wrote:
>
> > 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));
> >

[Kernow Girl]

[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" <Ll...@discussions.microsoft.com> wrote in message
news:6A855B44-28B4-43C2...@microsoft.com...

[Lloyd]

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

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" <Ll...@discussions.microsoft.com> wrote in message

news:006A46F5-5FCF-4ED8...@microsoft.com...

[Lloyd]

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.

[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" <Ll...@discussions.microsoft.com> wrote in message

news:F15EBE27-E559-4B55...@microsoft.com...

[Lloyd]

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]

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.

[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>

"Lloyd" <Ll...@discussions.microsoft.com> wrote in message

news:1AAB7F92-9438-4414...@microsoft.com...

[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" <Ll...@discussions.microsoft.com> wrote in message

news:E8264599-1E73-484B...@microsoft.com...

[Ken Snell (MVP)]

Finally had chance to post a reply... see elsewhere in this thread.

"Lloyd" <Ll...@discussions.microsoft.com> wrote in message

news:A0BFE101-97A8-4069...@microsoft.com...
> 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
>
> "Ken Snell (MVP)" wrote:
>

[Lloyd]

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

[Ken Snell (MVP)]

See this GoogleGroups search URL (watch for line-wrapping):
http://groups.google.com/group/microsoft.public.access.reports/browse_thread/thread/9170e91ec9ec6db0/2d62147d39f6639f?lnk=st&q=group%3Amicrosoft.public.access.reports+insubject%3Aaging+insubject%3Areceivables+author%3Asnell&rnum=1&hl=en#2d62147d39f6639f

--

Ken Snell
<MS ACCESS MVP>

"Lloyd" <Ll...@discussions.microsoft.com> wrote in message

news:3E15B1ED-8C9E-4511...@microsoft.com...

[Ken Snell (MVP)]

Or try this slightly shorter URL:

http://groups.google.com/group/microsoft.public.access.reports/browse_thread/thread/9170e91ec9ec6db0/2d62147d39f6639f?lnk=st&q=&rnum=46&hl=en#2d62147d39f6639f

--

Ken Snell
<MS ACCESS MVP>

"Lloyd" <Ll...@discussions.microsoft.com> wrote in message
news:3E15B1ED-8C9E-4511...@microsoft.com...