No current record 3021

aha! Done some more playing and testing, seems that it is maybe a limitation
of Access after all and not my db. If I remove Activity from the detail it
allows me to add the YTD ok. I think it is a number of grouping level
problems with access or amount of row headings you are allowed on a report
from a crosstab query. If you do a wizard crosstab query it only allows you
to select 3 row headings - seems the problem is linked to this maybe.

I am trying to get round the problem by adding up the fields for the YTD
figure manually in a calculated control on the report rather than a field
from the query. Simply by =[1]+[2]+[3] etc. This only works though if there
is a value in all 12 month fields, if ANY are null it does not display the
total. Is there a better way to do this?

Thanks for all your perseverance with this for me...

Sue

"Allen Browne" wrote:

Unless YTD is actually ActivityCount, then it would appear that the problem
is with whatever is the ControlSource of that expression.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in message
...
OK... I have another report to create which is very similar so I started
from
scratch again. I created new queries, this time I created a select query
and
added the field
ReviewDays which holds the number of days the activity lasted for. Then I
created a new crosstab query based on this and used Sum([ReviewDays]) as
the
Value. All other fields the same as before.

I created the new report based on the new crosstab query. I added bits at
a
time to keep testing. It let me add all grouping levels ok and displayed
the
activity correct this time (by the way - I had used Itinerary.Activity not
tblActivity.Activity but I had tried both to no avail before). It was all
going wonderful until I added the YTD field then the report wouldn't run
again with the No Current Record error. The YTD field is the running total
crosstab queries create on each row.

None of this makes sense and is driving me up the wall. I really need to
be
able to produce these reports! :-(

Sue
--
Thanks in advance for any help.
Sue


"Allen Browne" wrote:

Would it be very difficult to make a new report using the same query, and
see if that works? There is no reason why Activity should fail, assuming
you
have Name AutoCorrect off still. You could try changing the query so that
it
uses Itinerary.Activity intead of tblActivity.Activity and see if it
makes a
difference. (It shouldn't.)

Yes: new text box, with just the number.

The DateSerial() function takes, the Year as the first argument, month as
the second, and day as the third. Your regional settings have no effect
on
the function. It outputs a Variant of type Date, so the query should be
able
to work with the resultant value.

"hughess7" wrote in message
...
I have got it to work by removing the Activity field from the detail
section
of the report. This allowed me to put the Productive field in a
Productive
group header and produces the desired results. It still shows each row
per
activity but the problem is you don't know which Activity the row
relates
to
(eg Audit, Holiday, etc). Not quite sure why it objects to having the
Activity field in the detail?

Regarding your optimisation you mean add a new unbound text field
called
txtYear and store the year only eg 2005 ?

DateSerial([forms]![frm report menu]![txtYear],12,31))
Is this ok for the UK date format?

Thanks
Sue

"Allen Browne" wrote:

Okay, so the Distributor field holds the name of the country.
In that case, the items in the report's Sorting And Grouping box are
the
same items as in the GROUP BY clause, and in the same order. That's
optimal.
There is no reason at all why adding the 3rd item from the GROUP BY
clause
as the 3rd row of the Sorting And Grouping box should cause a problem
in
the
report.

There is nothing in the tables, in the query statement, or in the
report's
sorting'n'grouping that should cause any problem.

The report has no code in its events, and none of the controls call
any
user-functions, so there are no other causes of the problem.

You have also repaired the database, and removed the things that are
likely
to contribute to corruptions. There is no evidence of corrupted
indexes.

If you have SP1 for Office 2003, then you already have the JET service
patch. Nothing to persue there either.

You are leaving [frm report menu] open in the background, aren't you?

There are some ways you could optimize the query statement, but there
is
no
point in optimizing something that doesn't work.

Unless there is in fact a corrupt index, I'm out of ideas. If there is
any
index on the ReviewDate field, or on the Productive field, you could
try
removing those indexes, compacting the database, and then creating the
indexes again. You could also try deleting the relationships between
the
3
tables, removing the indexes (not the fields, just the indexes),
compacting
and then recreating the indexes and relations. But I'm really grasping
at
straws he a compact'n'repair should normally be enough to repair
the
indexes.

The optimisation would be to set up the WHERE clause so it can use the
index
on the ReviewDate field. It's actually just picking up the year from
the
form, so:

PARAMETERS [forms]![frm report menu]![txtYear] Short;
TRANSFORM CLng(Count(Itinerary.ReviewDate)) AS ActivityCount
SELECT [Distributor Codes (ECS created)].Distributor,
Specialists.Specialist,
tblActivity.Productive,
Itinerary.Activity,
Sum([ActivityCount]) AS [Total Of Activity]
FROM Specialists INNER JOIN ([Distributor Codes (ECS created)]
INNER JOIN (Itinerary INNER JOIN tblActivity
ON Itinerary.Activity = tblActivity.Activity)
ON [Distributor Codes (ECS created)].CountryCode =
Itinerary.CountryCode)
ON Specialists.SpecialistID = Itinerary.Specialist
WHERE (ReviewDate Between DateSerial([forms]![frm report
menu]![txtYear],1,1)
And DateSerial([forms]![frm report menu]![txtYear],12,31))
GROUP BY [Distributor Codes (ECS created)].Distributor,
Specialists.Specialist,
tblActivity.Productive,
Itinerary.Activity
PIVOT Month([ReviewDate]) In (1,2,3,4,5,6,7,8,9,10,11,12);

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in message
...
Answers to your questions:

1. Yes
2. Yes - default value is =DateAdd("d",2-Weekday(Date()),Date()+7)
3. Yes - Activity = Text but both were different in field size, made
them
both 20 now
SpecialistID = Autonumber and Specialist = Long Integer
CountryCode = Text, 2 in both
4. CountryCode holds the values A, B, C, etc and this is meaningless
to
the
user so I have another field called Distributor in the Distributor
Codes
table that holds the name of the Country (eg UK, Germany etc). This
is
used
as the first grouping level
5. The sorting order is the same in the query as the grouping in the
report
6. It is 2003 SP1, part of Office Prof 2003 run on terminal services
(Win
2003 Server)
7. Not sure how to check version of Jet ? Not applied anything other
than
all windows updates...

"Allen Browne" wrote:

When I'm debugging, I try to break things down into small
mouthfuls,
and
check each step from the bottom up. Some things have clearly
changed
since
we started, so lets see where we are at now.

I assume:
1. Specialists, Itinerary, and [Distributor Codes (ECS created)]
are
all
tables, i.e. there are no lower-level queries where things could go
wrong.

2. [forms]![frm report menu]![StartDate] is the unbound text box.
On
your
form this text box has its Format property set to Short Date. The
form
is
open. There is a value in the text box. The focus has left the box
(so
the
Value has been processed.)

3. The data *type* and *size* of the fields in the join is exactly
the
same,
i.e.:
- Itinerary.Activity and tblActivity.Activity are the same type and
size.
- [Distributor Codes (ECS created)].CountryCode and
Itinerary.CountryCode.
- Specialists.SpecialistID = Itinerary.Specialist.

Now, I am confused how you manage your first grouping level: the
query
is
not returning the CountryCode, so I don't see how that can be the
first
item
in the Sorting And Grouping box on your report.

Ideally, arrange the GROUP BY clause in the same order as the
fields
in
the
Sorting And Grouping box.

Also, we don't know what version of Access this is, or the service
pack
of
JET you have applied.

"hughess7" wrote in message
...
Thanks Allen

The only parameter is the StartDate field which is a shortdate
format.
I
have already deleted all the functions from the report so it is
using
the
query fields only at the moment.

PARAMETERS [forms]![frm report menu]![StartDate] DateTime;
TRANSFORM Count(Itinerary.ReviewDate) AS ActivityCount
SELECT [Distributor Codes (ECS created)].Distributor,
Specialists.Specialist, tblActivity.Productive,
Itinerary.Activity,
Sum([ActivityCount]) AS [Total Of Activity]
FROM Specialists INNER JOIN ([Distributor Codes (ECS created)]
INNER
JOIN
(Itinerary INNER JOIN tblActivity ON Itinerary.Activity =
tblActivity.Activity) ON [Distributor Codes (ECS
created)].CountryCode
=
Itinerary.CountryCode) ON Specialists.SpecialistID =
Itinerary.Specialist
WHERE
(((DatePart("yyyy",[ReviewDate]))=DatePart("yyyy",[forms]![frm
report
menu]![startdate])))
GROUP BY [Distributor Codes (ECS created)].Distributor,
Specialists.Specialist, tblActivity.Productive,
Itinerary.Activity
PIVOT DatePart("m",[ReviewDate]) In (1,2,3,4,5,6,7,8,9,10,11,12);

Sue


"Allen Browne" wrote:

Post the SQL statement from the crosstab query.
Let's see if we can typecast the fields.
And please indicate if there is a parameter in the query.