No current record 3021

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.

Any ideas?

Thanks
Sue

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.

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

Any ideas?

Thanks
Sue

Thanks very much Allen, I will work through your suggestions.

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

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

Any ideas?

Thanks
Sue

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.

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

Any ideas?

Thanks
Sue

The crosstab query generally doesn't work unless you declare the parameter,
so that's not unusual.

I'm not clear what kind of object "datefilter" is, or how it gets onto the
report's menu (custom toolbar type menu?).

Whatever object it is, there is a chance that it is no longer recognised
once Name AutoCorrect is off, and if so it could have been part of the
problem.

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

Any ideas?

Thanks
Sue

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.


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

Any ideas?

Thanks
Sue

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.

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.

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.

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.