No current record 3021

[hughess7]

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Temporarily drop the functions from the report, to see if that makes a
difference. The function itself may be erroring.

No the report is based on a crosstab query so therefore they are
groupby
fields, I don't know any other way to display the data in the format I
want
to view it (eg rows and columns spreadsheet style), but I still need
the
report to do some grouping and, for example, not to print the
specialist
against every row but in a header instead.

There also will be calculated control calling other functions (to
calculate
working days etc) but I haven't got that far yet as it keeps failing!
If I
add the Productive group the whole report layout is messing up.

Are you saying I shouldn't be able to do all this?

:

So you can get the report to fail with just 3 grouping levels:
Country
Specialist
Productive (Yes/No type field)

This happens even though:
- the report is bound directly to a table (not a query that is doing
prior
ORDER BY or GROUP BY);
- there is no code in the events of the report (i.e. the report's
HasModule
property os No);
- there are no calculated fields on the report that are calling other
functions.

If so, you have a situation I have not seen.

You have already done a repair, so a bad index is unlikely.
Nevertheless,
you might consider breaking any relation on Country and Specialist,
removing
any indexes on these fields (including hidden indexes), compacting,
and
then
recreating the indexes and relations.

If you have not previously checked that you have SP8 for JET 4 and the
latest service pack for your version of Office, get these from:
http://support.microsoft.com/gp/sp

There was a problem with Access 2002 SP3 throwing the 'No Current
Record'
error in a form after deleting a record, but you can't delete in a
report
so
I doubt that is relevant.

Not sure what else to suggest.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

After compact and repair its working again but I rebuilt piece by
piece
to
test each thing. The problem seems to lie with the Productive field.
This
is
a Yes/No field and determines whether an activity is deemed
productive
or
non-productive. I wanted the report to group by country first, then
specialist, then on productive activities and totals and
non-productive
activies and totals. Adding the Productive group header messes
everything
up
and the report starts to error with No Current Record

:

Unfortunately as I have started to further build my report the
problem
became
apparent again with no current record. It doesnt produce the jet db
error
though.

It is a complicated report based on a crosstab query and I am
trying
to
add
quite a few grouping levels.

This is getting very frustrating :-(


:

You can definately run into problems with mismatches and
inconsistent
results if the field type and the criteria type don't match.

Using a Date/Time type field, and a Date/Time type value in the
criteria is
highly desirable if that's the kind of data you are dealing with.

In fact, you have to work hard at getting JET 4 to recognise
calculated
date
fields as the right type, and you have to work at getting Access
to
recognise unbound text boxes as dates, so you can get consistent
results.
This article explains how:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

It is a text field not a date field. Its default value is set
to:

=Format(DateAdd("m",-1,Date()),"mmmyy")

Then in the query the criteria is Right([reviewdate],2) =
Right([forms]![frm
report menu]![DateFilter],2).

It is done this way as another report uses the DateFilter which
has
these
same values (eg JAN05) stored in a tables text field called
ReportingMonth,
which identify whether a record has been reported on or not and
in
which
month/year if so.

For this new report I have the problem with it filters on
ReviewDate
which
is an actual date field so I think maybe I should change it to
one
of
your
dateserial examples using a date field on the form. I also have
one
called
startdate which defaults to the following Monday's day to print
out
weekly
itineraries. Do you think this is maybe the problem because its
not
an
actual
date field?

:

Okay, so the text box displays the date in mmmyy format.

It might make a difference to the behavior of the reports
whether
this
text
box actually gets assigned a *date*, as in:
Me.DateFilter = DateSerial(Year(Date), Month(Date)-1, 1)
or whether it gets assigned as *string* value, such as:
Me.DateFilter = Format(DateSerial(Year(Date),
Month(Date)-1,
1),
"mmmyy")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Sorry for the confusion, DateFilter is an unbound text field
on
a
form
(which
is called frm report menu). When the form is opened
DateFilter
is
defaulted
to the current year and month-1 eg at the moment it will
show
JUL05.

I have various reports/queries that use this to filter which
records to
show.

message
Story so far...

I made a new db and imported all objects then followed
your
step by
step
instructions. The problem still occurs so I am now
looking
at
the
recovering
from corruption articles.

One thing, when I imported all the objects to a new db
there
were

 

[Allen Browne]

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 here: 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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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


:

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.

Temporarily drop the functions from the report, to see if that makes a
difference. The function itself may be erroring.

No the report is based on a crosstab query so therefore they are
groupby
fields, I don't know any other way to display the data in the format
I
want
to view it (eg rows and columns spreadsheet style), but I still need
the
report to do some grouping and, for example, not to print the
specialist
against every row but in a header instead.

There also will be calculated control calling other functions (to
calculate
working days etc) but I haven't got that far yet as it keeps
failing!
If I
add the Productive group the whole report layout is messing up.

Are you saying I shouldn't be able to do all this?

:

So you can get the report to fail with just 3 grouping levels:
Country
Specialist
Productive (Yes/No type field)

This happens even though:
- the report is bound directly to a table (not a query that is
doing
prior
ORDER BY or GROUP BY);
- there is no code in the events of the report (i.e. the report's
HasModule
property os No);
- there are no calculated fields on the report that are calling
other
functions.

If so, you have a situation I have not seen.

You have already done a repair, so a bad index is unlikely.
Nevertheless,
you might consider breaking any relation on Country and Specialist,
removing
any indexes on these fields (including hidden indexes), compacting,
and
then
recreating the indexes and relations.

If you have not previously checked that you have SP8 for JET 4 and
the
latest service pack for your version of Office, get these from:
http://support.microsoft.com/gp/sp

There was a problem with Access 2002 SP3 throwing the 'No Current
Record'
error in a form after deleting a record, but you can't delete in a
report
so
I doubt that is relevant.

Not sure what else to suggest.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

After compact and repair its working again but I rebuilt piece by
piece
to
test each thing. The problem seems to lie with the Productive
field.
This
is
a Yes/No field and determines whether an activity is deemed
productive
or
non-productive. I wanted the report to group by country first,
then
specialist, then on productive activities and totals and
non-productive
activies and totals. Adding the Productive group header messes
everything
up
and the report starts to error with No Current Record

:

Unfortunately as I have started to further build my report the
problem
became
apparent again with no current record. It doesnt produce the jet
db
error
though.

It is a complicated report based on a crosstab query and I am
trying
to
add
quite a few grouping levels.

This is getting very frustrating :-(


:

You can definately run into problems with mismatches and
inconsistent
results if the field type and the criteria type don't match.

Using a Date/Time type field, and a Date/Time type value in
the
criteria is
highly desirable if that's the kind of data you are dealing
with.

In fact, you have to work hard at getting JET 4 to recognise
calculated
date
fields as the right type, and you have to work at getting
Access
to
recognise unbound text boxes as dates, so you can get
consistent
results.
This article explains how:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
It is a text field not a date field. Its default value is
set
to:

=Format(DateAdd("m",-1,Date()),"mmmyy")

Then in the query the criteria is Right([reviewdate],2) =
Right([forms]![frm
report menu]![DateFilter],2).

It is done this way as another report uses the DateFilter
which
has
these
same values (eg JAN05) stored in a tables text field called
ReportingMonth,
which identify whether a record has been reported on or not
and
in
which
month/year if so.

For this new report I have the problem with it filters on
ReviewDate
which
is an actual date field so I think maybe I should change it
to
one
of
your
dateserial examples using a date field on the form. I also
have
one
called
startdate which defaults to the following Monday's day to
print
out
weekly
itineraries. Do you think this is maybe the problem because
its
not
an
actual
date field?

:

Okay, so the text box displays the date in mmmyy format.

It might make a difference to the behavior of the reports
whether
this
text
box actually gets assigned a *date*, as in:
Me.DateFilter = DateSerial(Year(Date), Month(Date)-1,
1)
or whether it gets assigned as *string* value, such as:
Me.DateFilter = Format(DateSerial(Year(Date),
Month(Date)-1,
1),
"mmmyy")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Sorry for the confusion, DateFilter is an unbound text
field
on
a
form
(which
is called frm report menu). When the form is opened
DateFilter
is
defaulted
to the current year and month-1 eg at the moment it will
show
JUL05.

I have various reports/queries that use this to filter
which
records to
show.

message
Story so far...

I made a new db and imported all objects then followed
your
step by
step
instructions. The problem still occurs so I am now
looking
at
the
recovering
from corruption articles.

One thing, when I imported all the objects to a new db
there
were

 

[hughess7]

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

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 here: 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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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


:

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.

Temporarily drop the functions from the report, to see if that makes a
difference. The function itself may be erroring.

No the report is based on a crosstab query so therefore they are
groupby
fields, I don't know any other way to display the data in the format
I
want
to view it (eg rows and columns spreadsheet style), but I still need
the
report to do some grouping and, for example, not to print the
specialist
against every row but in a header instead.

There also will be calculated control calling other functions (to
calculate
working days etc) but I haven't got that far yet as it keeps
failing!
If I
add the Productive group the whole report layout is messing up.

Are you saying I shouldn't be able to do all this?

:

So you can get the report to fail with just 3 grouping levels:
Country
Specialist
Productive (Yes/No type field)

This happens even though:
- the report is bound directly to a table (not a query that is
doing
prior
ORDER BY or GROUP BY);
- there is no code in the events of the report (i.e. the report's
HasModule
property os No);
- there are no calculated fields on the report that are calling
other
functions.

If so, you have a situation I have not seen.

You have already done a repair, so a bad index is unlikely.
Nevertheless,
you might consider breaking any relation on Country and Specialist,
removing
any indexes on these fields (including hidden indexes), compacting,
and
then
recreating the indexes and relations.

If you have not previously checked that you have SP8 for JET 4 and
the
latest service pack for your version of Office, get these from:
http://support.microsoft.com/gp/sp

There was a problem with Access 2002 SP3 throwing the 'No Current
Record'
error in a form after deleting a record, but you can't delete in a
report
so
I doubt that is relevant.

Not sure what else to suggest.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

After compact and repair its working again but I rebuilt piece by
piece
to
test each thing. The problem seems to lie with the Productive
field.
This
is
a Yes/No field and determines whether an activity is deemed
productive
or
non-productive. I wanted the report to group by country first,
then
specialist, then on productive activities and totals and
non-productive
activies and totals. Adding the Productive group header messes
everything
up
and the report starts to error with No Current Record

:

Unfortunately as I have started to further build my report the
problem
became
apparent again with no current record. It doesnt produce the jet
db
error
though.

It is a complicated report based on a crosstab query and I am
trying
to
add
quite a few grouping levels.

This is getting very frustrating :-(


:

You can definately run into problems with mismatches and
inconsistent
results if the field type and the criteria type don't match.

Using a Date/Time type field, and a Date/Time type value in
the
criteria is
highly desirable if that's the kind of data you are dealing
with.

In fact, you have to work hard at getting JET 4 to recognise
calculated
date
fields as the right type, and you have to work at getting
Access
to
recognise unbound text boxes as dates, so you can get
consistent
results.
This article explains how:
Calculated fields misinterpreted

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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 here: 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.

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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.

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


:

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.

Temporarily drop the functions from the report, to see if that
makes a
difference. The function itself may be erroring.

No the report is based on a crosstab query so therefore they are
groupby
fields, I don't know any other way to display the data in the
format
I
want
to view it (eg rows and columns spreadsheet style), but I still
need
the
report to do some grouping and, for example, not to print the
specialist
against every row but in a header instead.

There also will be calculated control calling other functions (to
calculate
working days etc) but I haven't got that far yet as it keeps
failing!
If I
add the Productive group the whole report layout is messing up.

Are you saying I shouldn't be able to do all this?

:

So you can get the report to fail with just 3 grouping levels:
Country
Specialist
Productive (Yes/No type field)

This happens even though:
- the report is bound directly to a table (not a query that is
doing
prior
ORDER BY or GROUP BY);
- there is no code in the events of the report (i.e. the
report's
HasModule
property os No);
- there are no calculated fields on the report that are calling
other
functions.

If so, you have a situation I have not seen.

You have already done a repair, so a bad index is unlikely.
Nevertheless,
you might consider breaking any relation on Country and
Specialist,
removing
any indexes on these fields (including hidden indexes),
compacting,
and
then
recreating the indexes and relations.

If you have not previously checked that you have SP8 for JET 4
and
the
latest service pack for your version of Office, get these from:
http://support.microsoft.com/gp/sp

There was a problem with Access 2002 SP3 throwing the 'No
Current
Record'
error in a form after deleting a record, but you can't delete in
a
report
so
I doubt that is relevant.

Not sure what else to suggest.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

After compact and repair its working again but I rebuilt piece
by
piece
to
test each thing. The problem seems to lie with the Productive
field.
This
is
a Yes/No field and determines whether an activity is deemed
productive
or
non-productive. I wanted the report to group by country first,
then
specialist, then on productive activities and totals and
non-productive
activies and totals. Adding the Productive group header messes
everything
up
and the report starts to error with No Current Record

:

Unfortunately as I have started to further build my report
the
problem
became
apparent again with no current record. It doesnt produce the
jet
db
error
though.

It is a complicated report based on a crosstab query and I am
trying
to
add
quite a few grouping levels.

This is getting very frustrating :-(


:

You can definately run into problems with mismatches and
inconsistent
results if the field type and the criteria type don't
match.

Using a Date/Time type field, and a Date/Time type value in
the
criteria is
highly desirable if that's the kind of data you are dealing
with.

In fact, you have to work hard at getting JET 4 to
recognise
calculated
date
fields as the right type, and you have to work at getting
Access
to
recognise unbound text boxes as dates, so you can get
consistent
results.
This article explains how:
Calculated fields misinterpreted

 

[hughess7]

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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 here: 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.

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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.

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


:

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.

Temporarily drop the functions from the report, to see if that
makes a
difference. The function itself may be erroring.

No the report is based on a crosstab query so therefore they are
groupby
fields, I don't know any other way to display the data in the
format
I
want
to view it (eg rows and columns spreadsheet style), but I still
need
the
report to do some grouping and, for example, not to print the
specialist
against every row but in a header instead.

There also will be calculated control calling other functions (to
calculate
working days etc) but I haven't got that far yet as it keeps
failing!
If I
add the Productive group the whole report layout is messing up.

Are you saying I shouldn't be able to do all this?

:

So you can get the report to fail with just 3 grouping levels:
Country
Specialist
Productive (Yes/No type field)

This happens even though:
- the report is bound directly to a table (not a query that is
doing
prior
ORDER BY or GROUP BY);
- there is no code in the events of the report (i.e. the
report's
HasModule
property os No);
- there are no calculated fields on the report that are calling
other
functions.

If so, you have a situation I have not seen.

You have already done a repair, so a bad index is unlikely.
Nevertheless,
you might consider breaking any relation on Country and
Specialist,
removing
any indexes on these fields (including hidden indexes),
compacting,
and
then
recreating the indexes and relations.

If you have not previously checked that you have SP8 for JET 4
and
the
latest service pack for your version of Office, get these from:
http://support.microsoft.com/gp/sp

There was a problem with Access 2002 SP3 throwing the 'No
Current
Record'

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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

:

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 here: 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.

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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.

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


:

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.

Temporarily drop the functions from the report, to see if that
makes a
difference. The function itself may be erroring.

No the report is based on a crosstab query so therefore they
are
groupby
fields, I don't know any other way to display the data in the
format
I
want
to view it (eg rows and columns spreadsheet style), but I
still
need
the
report to do some grouping and, for example, not to print the
specialist
against every row but in a header instead.

There also will be calculated control calling other functions
(to
calculate
working days etc) but I haven't got that far yet as it keeps
failing!
If I
add the Productive group the whole report layout is messing
up.

Are you saying I shouldn't be able to do all this?

:

So you can get the report to fail with just 3 grouping
levels:
Country
Specialist
Productive (Yes/No type field)

This happens even though:
- the report is bound directly to a table (not a query that
is
doing
prior
ORDER BY or GROUP BY);
- there is no code in the events of the report (i.e. the
report's
HasModule
property os No);
- there are no calculated fields on the report that are
calling
other
functions.

If so, you have a situation I have not seen.

You have already done a repair, so a bad index is unlikely.
Nevertheless,
you might consider breaking any relation on Country and
Specialist,
removing
any indexes on these fields (including hidden indexes),
compacting,
and
then
recreating the indexes and relations.

If you have not previously checked that you have SP8 for JET
4
and
the
latest service pack for your version of Office, get these
from:
http://support.microsoft.com/gp/sp

There was a problem with Access 2002 SP3 throwing the 'No
Current
Record'

 

[hughess7]

When I say the report wouldn't run it does but it treats the activities as
though they are grouped seperately and then when you try to view page 2 you
get the 'No current record' error.

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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 here: 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.

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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.

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


:

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.

Temporarily drop the functions from the report, to see if that
makes a
difference. The function itself may be erroring.

No the report is based on a crosstab query so therefore they are
groupby
fields, I don't know any other way to display the data in the
format
I
want
to view it (eg rows and columns spreadsheet style), but I still
need
the
report to do some grouping and, for example, not to print the
specialist
against every row but in a header instead.

There also will be calculated control calling other functions (to
calculate
working days etc) but I haven't got that far yet as it keeps
failing!
If I
add the Productive group the whole report layout is messing up.

Are you saying I shouldn't be able to do all this?

:

So you can get the report to fail with just 3 grouping levels:
Country
Specialist
Productive (Yes/No type field)

This happens even though:
- the report is bound directly to a table (not a query that is
doing
prior
ORDER BY or GROUP BY);
- there is no code in the events of the report (i.e. the
report's
HasModule
property os No);

 

[hughess7]

YTD is [Total Of ReviewDays] in sql below:

TRANSFORM Sum([qry Resource Allocation Days].ReviewDays) AS SumOfReviewDays
SELECT [qry Resource Allocation Days].Distributor, [qry Resource Allocation
Days].Specialist, [qry Resource Allocation Days].Productive, [qry Resource
Allocation Days].Activity, Sum([qry Resource Allocation Days].ReviewDays) AS
[Total Of ReviewDays]
FROM [qry Resource Allocation Days]
GROUP BY [qry Resource Allocation Days].Distributor, [qry Resource
Allocation Days].Specialist, [qry Resource Allocation Days].Productive, [qry
Resource Allocation Days].Activity
PIVOT [qry Resource Allocation Days].MonthNo In (1,2,3,4,5,6,7,8,9,10,11,12);

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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

:

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 here: 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.

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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.

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


:

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.

 

[hughess7]

Also tried this but it still fails, as soon as I add the YTD field to the
report and run it, it messes up the layout and errors if you try to view page
2 of the report with 'no current record'

TRANSFORM Sum([qry Resource Allocation Days].ReviewDays) AS TotalDays
SELECT [qry Resource Allocation Days].Distributor, [qry Resource Allocation
Days].Specialist, [qry Resource Allocation Days].Productive, [qry Resource
Allocation Days].Activity, Sum([TotalDays]) AS YTD
FROM [qry Resource Allocation Days]
GROUP BY [qry Resource Allocation Days].Distributor, [qry Resource
Allocation Days].Specialist, [qry Resource Allocation Days].Productive, [qry
Resource Allocation Days].Activity
PIVOT [qry Resource Allocation Days].MonthNo In (1,2,3,4,5,6,7,8,9,10,11,12);

--
Thanks in advance for any help.
Sue

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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

:

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 here: 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.

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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.

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


:

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.

 

[Allen Browne]

I think I have walked with you as far as I can on this one.

I would encourage you to stay with a simple and known SQL statement until it
all works. Adding more complexity before you get it stable is not going to
be productive.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

YTD is [Total Of ReviewDays] in sql below:

TRANSFORM Sum([qry Resource Allocation Days].ReviewDays) AS
SumOfReviewDays
SELECT [qry Resource Allocation Days].Distributor, [qry Resource
Allocation
Days].Specialist, [qry Resource Allocation Days].Productive, [qry Resource
Allocation Days].Activity, Sum([qry Resource Allocation Days].ReviewDays)
AS
[Total Of ReviewDays]
FROM [qry Resource Allocation Days]
GROUP BY [qry Resource Allocation Days].Distributor, [qry Resource
Allocation Days].Specialist, [qry Resource Allocation Days].Productive,
[qry
Resource Allocation Days].Activity
PIVOT [qry Resource Allocation Days].MonthNo In
(1,2,3,4,5,6,7,8,9,10,11,12);

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

 

[hughess7]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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

:

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 here: 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.

Reply to group, rather than allenbrowne at mvps dot org.

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

:

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.

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


:

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.