Identical Reports 1 works... 1 doesn't???

[Mike A]

I have 1 database table.
2 identical Queries, with the exception that 1 Query chooses dates Between And
and the 2nd Query is general to the whole database from the first date in the database, to-date.

The Report on the first Query with dates Between___ And___ works fine.
The identical Report on the 2nd Query on the whole database comes back indicating
a formula somewhere is too complex.

Why would 1 Report run ok and not the other?
Is there something obvious I am overlooking?
Except for the dates all other sections of the Queries and of the Reports appear identical!!

Any obxservations and comments are greatly appreciated!!! Thankyou!!

 

[Fornatian]

In your question you put:

...first date in the database, to-date.
as the criteria for your second query.

Do you mean all records as in from the first date in the database to-date(meaning now?)
If so you don't need a query, just a report based on the table.

If you mean all records up to a certain date you need to enter:

<[Show records where date is less than]

That should do it, you don't need to specify the start date as Access assumes you mean all records.

Ian

 

[Richie]

Try running the query on it's own and view that first.

 

[Mike A]

Thank you for the feedback!

Yes, I am wanting to run the Report to a certain date...
I will replace the DateDiff formula with
[Show records where date is less than] and see what happens.

The Query works fine by itself, it's the Report that is coming up with the 'too complex' message.

Tks again! Will get back to you with result.

 

[Mike A]

Sorry.. feeling 'slow'!!!...
Tried all kinds of combinations!
Where should I fit the
<[Show records where date is less than] ??
I need the "m" in the DateDiff() to to give me the number of months from date of purchase to a subsequent reporting period.

I have tried restating the formula in the Report to:
=(Sum([Accumulated Depreciation Query]![Purchase Price])/
Sum([Accumulated Depreciation Query]![Depn Period]))*
Sum([Accumulated Depreciation Query]![# Months])
This worked BUT...
Summing each of the components like this gives an incorrect answer in the Report.
Is there a Report friendly way to Sum() the equation as a whole?

Accum Depn: ([Purchase Price]/[Depn Period])*([# Months])

[Purchase Price] is original data input.
[Depn Period]is original data input.
# Months: DateDiff("m",[Asset Database]![Date],[Date Query Ranges]![As At Date])+1

The # Months multiplies the first part of the equation by the # of months from the date of equipment purchase.

Thank you for your time and comments!!!

 

[Fornatian]

What I would do is break the formula down into its component part in unbound fields so you can see where any errors in calculation are being performed.

If all is well then you have two options, 1.leave the unbound controls on the form/report and set their visible properties to false OR
2. amalgamate the formulae together when you have corrected the problem.

To be honest for the sake of a few controls I always leave the controls on the form.

Ian

 

[Mike A]

Thankyou! Thankyou! Thankyou!

In the process of reconstructing the Report so it would be driven from the data Table instead of from the Query and therefore putting the Controls in the Report, (per your suggestion)
I discovered the root of my problem was a formula that was trying to divide one of the asset categories by 0 (by zero).

When I deleted the zeros everything worked like a charm!

Thank you very much for your time, patience and feedback!!!

 

[Fornatian]

No probs

Ian