No current record 3021

That's a rather worrisome expression you have there.

DatePart("yyyy", ...) returns just the date from the year.
Nz() doesn't do much without a 2nd argument.
CDate() then converts that numeric value into a date, based on the values
that Access uses internally to handle dates. For example:
CDate(Nz(DatePart("yyyy", Date())))
returns:
06/27/1905
which is probably not what you intended.

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

"hughess7" wrote in message
...
Thanks for all the advice Allen. I have changed it in the query to be
based
on StartDate which is a shortdate format:

DatePart("yyyy",[ReviewDate]) =
CDate(Nz(DatePart("yyyy",[forms]![frm report menu]![startdate])))

As long as I have the form frm Report Menu open when I am designing the
report it seems ok at the moment. If not it keeps asking for the parameter
and gets stuck in a loop. Bit odd when I am not trying to run the report -
just design it !

Thanks again, hopefully it will behave itself now...

Sue

--
Thanks in advance for any help.
Sue


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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.

"hughess7" wrote in message
news 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
pop
up
parameter boxes for datefilter which is one of the objects on the
reports
menu which is used by the problematic query and reports. I also
get
the
error
'MS jet database engine does not recognize '[forms]![frm report
menu]![datefilter]' as a valid field name on my crosstab queries
if
I
don't
put this in as a parameter. Is this related to these problems
too?

Thanks
Sue
--
Thanks in advance for any help.
Sue


"Allen Browne" wrote:

The strange looking name Access can't find indicates that
either:
a) something in the report or its source query refers to an
object
that
does
not exits, or

b) Access is confused about what is named what.

If the report does work properly after a compact'n'repair, then
(b)
is
the
likely issue. Typically this happens because of the many bugs
associated
with Name AutoCorrect in Access, and the corruption they cause.

To fix the problem, try this sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the
database
by
entering something like this at the command prompt while Access
is
not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe"
/decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of
Access,
see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the
name-autocorrect
errors
are gone, the indexes are repaired, inconsistencies between the
text-
and
compiled-versions of the code are fixed, and reference
ambiguities
are
resolved.

If it is still a problem, the next step would be to get Access
to
rebuild
the database for you. Follow the steps for the first symptom in
this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

If you are still stuck after that, we need to examine
possibility
(a)
further.

"hughess7" wrote in message
...
Hi all,

I have a strange thing happening and wondered if anyone knows
what
is
wrong:

I am trying to build a report based on a crosstab query, I
seleteced
the
recordsource for the report from the pull down box. After I've
selected
it
Access returns the error 'The MS Jet DB engine could not find
the
object
'~sq_rReport1'. Make sure it exists...'. The name of the query
is
'qry
Resource Allocation' and it does exist (it is in the list and
lets
me
select
it) and the query runs ok.

This problem seems to be intermittent. The report was working
for
a
while
then I got the error 3021 No current record and I discovered
it
didn't
like
the recordsource again. I keep compacting and repairing the db
but
this
is
still happening.

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 :-(


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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.

"hughess7" wrote in message
news 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
pop
up
parameter boxes for datefilter which is one of the objects on the
reports
menu which is used by the problematic query and reports. I also get
the
error
'MS jet database engine does not recognize '[forms]![frm report
menu]![datefilter]' as a valid field name on my crosstab queries if
I
don't
put this in as a parameter. Is this related to these problems too?

Thanks
Sue
--
Thanks in advance for any help.
Sue


"Allen Browne" wrote:

The strange looking name Access can't find indicates that either:
a) something in the report or its source query refers to an object
that
does
not exits, or

b) Access is confused about what is named what.

If the report does work properly after a compact'n'repair, then (b)
is
the
likely issue. Typically this happens because of the many bugs
associated
with Name AutoCorrect in Access, and the corruption they cause.

To fix the problem, try this sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the
database
by
entering something like this at the command prompt while Access is
not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe"
/decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of
Access,
see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the
name-autocorrect
errors
are gone, the indexes are repaired, inconsistencies between the
text-
and
compiled-versions of the code are fixed, and reference ambiguities
are
resolved.

If it is still a problem, the next step would be to get Access to
rebuild
the database for you. Follow the steps for the first symptom in
this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

If you are still stuck after that, we need to examine possibility
(a)
further.

"hughess7" wrote in message
...
Hi all,

I have a strange thing happening and wondered if anyone knows
what
is
wrong:

I am trying to build a report based on a crosstab query, I
seleteced
the
recordsource for the report from the pull down box. After I've
selected
it
Access returns the error 'The MS Jet DB engine could not find the
object
'~sq_rReport1'. Make sure it exists...'. The name of the query is
'qry
Resource Allocation' and it does exist (it is in the list and
lets
me
select
it) and the query runs ok.

This problem seems to be intermittent. The report was working for
a
while
then I got the error 3021 No current record and I discovered it
didn't
like
the recordsource again. I keep compacting and repairing the db
but
this
is
still happening.

I am trying to filter my ReviewDate records for a given year, using the year
of the StartDate value on my form instead of asking for a parameter when run.
That expression seems to work but its difficult to tell as the actual date is
not displayed in the crosstab. What I am trying to achieve is a YTD report
showing how many days were spent by each specialist on each activity (split
by two types - productive and non-productive) by Country. I want to view
Monthly totals and a YTD total (hence the crosstab). I then want to group all
this info on a report and have totals per country and per specialist showing
so I can calculate how productive they are based on actual working days in a
month and CountryWorkingDays (based on holidays etc - value stored in a rates
table per country).

I put CDate and NZ etc in the expression using your example from
http://allenbrowne.com/ser-45.html

Very basic example of raw data in crosstab query below (except the column
headings are 1,2,3,4 etc rather than Jan,Feb,Mar):

Distributor Specialist Productive Activity YTD Jan Feb Mar
Austria Stefan Hell TRUE Audit 33 4 3 5
Germany Martha True Audit 40 6 8
6 False Holiday 20 5 5

etc

"Allen Browne" wrote:

That's a rather worrisome expression you have there.

DatePart("yyyy", ...) returns just the date from the year.
Nz() doesn't do much without a 2nd argument.
CDate() then converts that numeric value into a date, based on the values
that Access uses internally to handle dates. For example:
CDate(Nz(DatePart("yyyy", Date())))
returns:
06/27/1905
which is probably not what you intended.

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

"hughess7" wrote in message
...
Thanks for all the advice Allen. I have changed it in the query to be
based
on StartDate which is a shortdate format:

DatePart("yyyy",[ReviewDate]) =
CDate(Nz(DatePart("yyyy",[forms]![frm report menu]![startdate])))

As long as I have the form frm Report Menu open when I am designing the
report it seems ok at the moment. If not it keeps asking for the parameter
and gets stuck in a loop. Bit odd when I am not trying to run the report -
just design it !

Thanks again, hopefully it will behave itself now...

Sue

--
Thanks in advance for any help.
Sue


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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.

"hughess7" wrote in message
news 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
pop
up
parameter boxes for datefilter which is one of the objects on the
reports
menu which is used by the problematic query and reports. I also
get
the
error
'MS jet database engine does not recognize '[forms]![frm report
menu]![datefilter]' as a valid field name on my crosstab queries
if
I
don't
put this in as a parameter. Is this related to these problems
too?

Thanks
Sue
--
Thanks in advance for any help.
Sue


"Allen Browne" wrote:

The strange looking name Access can't find indicates that
either:
a) something in the report or its source query refers to an
object
that
does
not exits, or

b) Access is confused about what is named what.

If the report does work properly after a compact'n'repair, then
(b)
is
the
likely issue. Typically this happens because of the many bugs
associated
with Name AutoCorrect in Access, and the corruption they cause.

To fix the problem, try this sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the
database
by
entering something like this at the command prompt while Access
is
not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe"
/decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of
Access,
see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the
name-autocorrect
errors
are gone, the indexes are repaired, inconsistencies between the
text-
and
compiled-versions of the code are fixed, and reference
ambiguities
are
resolved.

If it is still a problem, the next step would be to get Access
to
rebuild
the database for you. Follow the steps for the first symptom in
this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

If you are still stuck after that, we need to examine
possibility
(a)
further.

"hughess7" wrote in message
...
Hi all,

I have a strange thing happening and wondered if anyone knows
what
is
wrong:

I am trying to build a report based on a crosstab query, I
seleteced
the
recordsource for the report from the pull down box. After I've
selected
it
Access returns the error 'The MS Jet DB engine could not find
the
object
'~sq_rReport1'. Make sure it exists...'. The name of the query
is
'qry
Resource Allocation' and it does exist (it is in the list and
lets
me
select
it) and the query runs ok.

This problem seems to be intermittent. The report was working
for
a
while
then I got the error 3021 No current record and I discovered
it
didn't
like
the recordsource again. I keep compacting and repairing the db
but
this
is
still happening.

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

"hughess7" wrote:

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 :-(


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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.

"hughess7" wrote in message
news 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
pop
up
parameter boxes for datefilter which is one of the objects on the
reports
menu which is used by the problematic query and reports. I also get
the
error
'MS jet database engine does not recognize '[forms]![frm report
menu]![datefilter]' as a valid field name on my crosstab queries if
I
don't
put this in as a parameter. Is this related to these problems too?

Thanks
Sue
--
Thanks in advance for any help.
Sue


"Allen Browne" wrote:

The strange looking name Access can't find indicates that either:
a) something in the report or its source query refers to an object
that
does
not exits, or

b) Access is confused about what is named what.

If the report does work properly after a compact'n'repair, then (b)
is
the
likely issue. Typically this happens because of the many bugs
associated
with Name AutoCorrect in Access, and the corruption they cause.

To fix the problem, try this sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the
database
by
entering something like this at the command prompt while Access is
not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe"
/decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of
Access,
see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the
name-autocorrect
errors
are gone, the indexes are repaired, inconsistencies between the
text-
and
compiled-versions of the code are fixed, and reference ambiguities
are
resolved.

If it is still a problem, the next step would be to get Access to
rebuild
the database for you. Follow the steps for the first symptom in
this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

If you are still stuck after that, we need to examine possibility
(a)
further.

"hughess7" wrote in message
...
Hi all,

I have a strange thing happening and wondered if anyone knows
what
is
wrong:

I am trying to build a report based on a crosstab query, I
seleteced
the
recordsource for the report from the pull down box. After I've
selected
it
Access returns the error 'The MS Jet DB engine could not find the
object
'~sq_rReport1'. Make sure it exists...'. The name of the query is
'qry
Resource Allocation' and it does exist (it is in the list and
lets
me
select
it) and the query runs ok.

This problem seems to be intermittent. The report was working for
a
while
then I got the error 3021 No current record and I discovered it
didn't
like
the recordsource again. I keep compacting and repairing the db
but
this
is
still happening.

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in message
...
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

"hughess7" wrote:

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 :-(


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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.

"hughess7" wrote in message
news 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
pop
up
parameter boxes for datefilter which is one of the objects on
the
reports
menu which is used by the problematic query and reports. I also
get
the
error
'MS jet database engine does not recognize '[forms]![frm report
menu]![datefilter]' as a valid field name on my crosstab
queries if
I
don't
put this in as a parameter. Is this related to these problems
too?

Thanks
Sue
--
Thanks in advance for any help.
Sue


"Allen Browne" wrote:

The strange looking name Access can't find indicates that
either:
a) something in the report or its source query refers to an
object
that
does
not exits, or

b) Access is confused about what is named what.

If the report does work properly after a compact'n'repair,
then (b)
is
the
likely issue. Typically this happens because of the many bugs
associated
with Name AutoCorrect in Access, and the corruption they
cause.

To fix the problem, try this sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the
database
by
entering something like this at the command prompt while
Access is
not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe"
/decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of
Access,
see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug
menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the
name-autocorrect
errors
are gone, the indexes are repaired, inconsistencies between
the
text-
and
compiled-versions of the code are fixed, and reference
ambiguities
are
resolved.

If it is still a problem, the next step would be to get Access
to
rebuild
the database for you. Follow the steps for the first symptom
in
this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

If you are still stuck after that, we need to examine
possibility
(a)
further.

"hughess7" wrote in
message
...
Hi all,

I have a strange thing happening and wondered if anyone
knows
what
is
wrong:

I am trying to build a report based on a crosstab query, I
seleteced
the
recordsource for the report from the pull down box. After
I've
selected
it
Access returns the error 'The MS Jet DB engine could not
find the
object
'~sq_rReport1'. Make sure it exists...'. The name of the
query is
'qry
Resource Allocation' and it does exist (it is in the list
and
lets
me
select
it) and the query runs ok.

This problem seems to be intermittent. The report was
working for
a
while
then I got the error 3021 No current record and I discovered
it
didn't
like
the recordsource again. I keep compacting and repairing the
db
but
this
is
still happening.

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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in message
...
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

"hughess7" wrote:

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 :-(


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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.

"hughess7" wrote in message
news 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
pop
up
parameter boxes for datefilter which is one of the objects on
the
reports
menu which is used by the problematic query and reports. I also
get
the
error
'MS jet database engine does not recognize '[forms]![frm report
menu]![datefilter]' as a valid field name on my crosstab
queries if
I
don't
put this in as a parameter. Is this related to these problems
too?

Thanks
Sue
--
Thanks in advance for any help.
Sue


"Allen Browne" wrote:

The strange looking name Access can't find indicates that
either:
a) something in the report or its source query refers to an
object
that
does
not exits, or

b) Access is confused about what is named what.

If the report does work properly after a compact'n'repair,
then (b)
is
the
likely issue. Typically this happens because of the many bugs
associated
with Name AutoCorrect in Access, and the corruption they
cause.

To fix the problem, try this sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the
database
by
entering something like this at the command prompt while
Access is
not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe"
/decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of
Access,
see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug
menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the
name-autocorrect
errors
are gone, the indexes are repaired, inconsistencies between
the
text-
and
compiled-versions of the code are fixed, and reference
ambiguities
are
resolved.

If it is still a problem, the next step would be to get Access
to
rebuild
the database for you. Follow the steps for the first symptom
in
this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

If you are still stuck after that, we need to examine
possibility
(a)
further.

"hughess7" wrote in
message
...
Hi all,

I have a strange thing happening and wondered if anyone
knows
what
is
wrong:

I am trying to build a report based on a crosstab query, I
seleteced
the
recordsource for the report from the pull down box. After
I've
selected
it
Access returns the error 'The MS Jet DB engine could not
find the
object
'~sq_rReport1'. Make sure it exists...'. The name of the
query is
'qry
Resource Allocation' and it does exist (it is in the list
and
lets
me
select
it) and the query runs ok.

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.

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

"hughess7" wrote in message
...
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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in message
...
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

"hughess7" wrote:

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 :-(


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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.

"hughess7" wrote in
message
news 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
pop
up
parameter boxes for datefilter which is one of the objects
on
the
reports
menu which is used by the problematic query and reports. I
also
get
the
error
'MS jet database engine does not recognize '[forms]![frm
report
menu]![datefilter]' as a valid field name on my crosstab
queries if
I
don't
put this in as a parameter. Is this related to these
problems
too?

Thanks
Sue
--
Thanks in advance for any help.
Sue


"Allen Browne" wrote:

The strange looking name Access can't find indicates that
either:
a) something in the report or its source query refers to an
object
that
does
not exits, or

b) Access is confused about what is named what.

If the report does work properly after a compact'n'repair,
then (b)
is
the
likely issue. Typically this happens because of the many
bugs
associated
with Name AutoCorrect in Access, and the corruption they
cause.

To fix the problem, try this sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile
the
database
by
entering something like this at the command prompt while
Access is
not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe"
/decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version
of
Access,
see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug
menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the
name-autocorrect
errors
are gone, the indexes are repaired, inconsistencies between
the
text-
and
compiled-versions of the code are fixed, and reference
ambiguities
are
resolved.

If it is still a problem, the next step would be to get
Access
to
rebuild
the database for you. Follow the steps for the first
symptom
in
this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

If you are still stuck after that, we need to examine
possibility
(a)
further.

"hughess7" wrote in
message
...
Hi all,

I have a strange thing happening and wondered if anyone
knows
what
is
wrong:

I am trying to build a report based on a crosstab query,
I
seleteced
the
recordsource for the report from the pull down box. After
I've
selected
it
Access returns the error 'The MS Jet DB engine could not
find the
object
'~sq_rReport1'. Make sure it exists...'. The name of the
query is
'qry
Resource Allocation' and it does exist (it is in the list
and
lets
me
select
it) and the query runs ok.

Thanks Allen

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

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

Sue


"Allen Browne" wrote:

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

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

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

"hughess7" wrote in message
...
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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in message
...
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

"hughess7" wrote:

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 :-(


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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.

"hughess7" wrote in
message
news 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
pop
up
parameter boxes for datefilter which is one of the objects
on
the
reports
menu which is used by the problematic query and reports. I
also
get
the
error
'MS jet database engine does not recognize '[forms]![frm
report
menu]![datefilter]' as a valid field name on my crosstab
queries if
I
don't
put this in as a parameter. Is this related to these
problems
too?

Thanks
Sue
--
Thanks in advance for any help.
Sue


"Allen Browne" wrote:

The strange looking name Access can't find indicates that
either:
a) something in the report or its source query refers to an
object
that
does
not exits, or

b) Access is confused about what is named what.

If the report does work properly after a compact'n'repair,
then (b)
is
the
likely issue. Typically this happens because of the many
bugs
associated
with Name AutoCorrect in Access, and the corruption they
cause.

To fix the problem, try this sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile
the
database
by
entering something like this at the command prompt while
Access is
not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe"
/decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version
of
Access,
see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug

The report works fine until I add the Productive grouping level then it
doesn't display the results as expected and if you try to view page 2 it
errors with No current record and sticks in a loop. You have to press
ctrl+break about twenty times to get the report to go back to design view


"Allen Browne" wrote:

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

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

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

"hughess7" wrote in message
...
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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in message
...
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

"hughess7" wrote:

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 :-(


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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.

"hughess7" wrote in
message
news 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
pop
up
parameter boxes for datefilter which is one of the objects
on
the
reports
menu which is used by the problematic query and reports. I
also
get
the
error
'MS jet database engine does not recognize '[forms]![frm
report
menu]![datefilter]' as a valid field name on my crosstab
queries if
I
don't
put this in as a parameter. Is this related to these
problems
too?

Thanks
Sue
--
Thanks in advance for any help.
Sue


"Allen Browne" wrote:

The strange looking name Access can't find indicates that
either:
a) something in the report or its source query refers to an
object
that
does
not exits, or

b) Access is confused about what is named what.

If the report does work properly after a compact'n'repair,
then (b)
is
the
likely issue. Typically this happens because of the many
bugs
associated
with Name AutoCorrect in Access, and the corruption they
cause.

To fix the problem, try this sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile
the
database
by
entering something like this at the command prompt while
Access is
not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe"
/decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version
of
Access,
see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in message
...
Thanks Allen

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

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

Sue


"Allen Browne" wrote:

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

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

"hughess7" wrote in message
...
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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in message
...
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

"hughess7" wrote:

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 :-(


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in 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?

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hughess7" wrote in
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.

"hughess7" wrote in
message
news 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
pop
up
parameter boxes for datefilter which is one of the
objects
on
the
reports
menu which is used by the problematic query and reports.
I
also
get
the
error
'MS jet database engine does not recognize '[forms]![frm
report
menu]![datefilter]' as a valid field name on my crosstab
queries if
I
don't
put this in as a parameter. Is this related to these
problems
too?

Thanks
Sue
--
Thanks in advance for any help.
Sue


"Allen Browne" wrote:

The strange looking name Access can't find indicates
that
either:
a) something in the report or its source query refers to
an
object
that
does
not exits, or

b) Access is confused about what is named what.

If the report does work properly after a
compact'n'repair,
then (b)
is
the
likely issue. Typically this happens because of the many
bugs
associated
with Name AutoCorrect in Access, and the corruption they
cause.

To fix the problem, try this sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
Decompile
the
database
by
entering something like this at the command prompt while
Access is
not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft
office\office\msaccess.exe"
/decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your
version
of
Access,
see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the
Debug