Grouping using Prefix Characters

  • Thread starter Thread starter Gary H
  • Start date Start date
G

Gary H

I'm having some problems with an Access 97 DB that's been
working fine for the past year, but today is giving me
problems and to the best of my knowledge, nothing has
been changed to the database .. just data added.

I started receiving this error during report
generation: "Function isn't available in expressions in
query expression 'Left(PartNum, 1)'." I do not have any
queries associated to this report, nor do I have any
filtering in the Properties or anywhere, even in code, do
I call the Left function.

I finally managed to figure out where the error was being
generated; at the Sorting and Grouping settings. I have
the field 'PartNum' grouped and set to Group On "Prefix
Characters". If I change that to Group On "Each Value",
the error goes away but I get the error code "#Name?" in
the report where I refer to PartNum. I've verified the
existence of the field PartNum and I've tried grouping on
other fields but have the same results. I also have the
same results if I start from scratch and make a new
report within this same database with the same grouping
options. I do not get any error if I start a brand new
DB.

This database is too complicated to simply start over ...
Any suggestions???
 
HI:

First, use 'Repair Database' and then run your reports again. If this
doesn't resolve it, try making a duplicate database with empty records and
bring in the records from the original database.

Hope this helps
 
Braces around the field name didn't make a difference
either.

There is no underlying query ... I'm getting the data
right from the table. I guess the underlying query is
whatever MS does behind the scenes ... as I'm sure they
do the Sorting and Grouping through a query.

Also note that in my Sorting and Grouping Dialog, I can
select what Field I want from the dropdown list and my
PartNum field is there, as well as all the other fields
in the table.

Thanks for your suggestions, keep them comin!
 
'Left(report![PartNum], 1) Did you try using report! in the expression, for
open reports?
 
Gary said:
I'm having some problems with an Access 97 DB that's been
working fine for the past year, but today is giving me
problems and to the best of my knowledge, nothing has
been changed to the database .. just data added.

I started receiving this error during report
generation: "Function isn't available in expressions in
query expression 'Left(PartNum, 1)'." I do not have any
queries associated to this report, nor do I have any
filtering in the Properties or anywhere, even in code, do
I call the Left function.

I finally managed to figure out where the error was being
generated; at the Sorting and Grouping settings. I have
the field 'PartNum' grouped and set to Group On "Prefix
Characters". If I change that to Group On "Each Value",
the error goes away but I get the error code "#Name?" in
the report where I refer to PartNum. I've verified the
existence of the field PartNum and I've tried grouping on
other fields but have the same results. I also have the
same results if I start from scratch and make a new
report within this same database with the same grouping
options. I do not get any error if I start a brand new
DB.
Click to expand...


It's starting to sound like your database has become
corrupted. First, make a backup of the file so if things
get worse you can at least go back to where the problem
started.

Try creating a new blank database and check the appropriate
References. Then try to Import everything from the original
file.

If that doesn't work, check Tony Toews' site for other
corruption recoverery ideas.

If all else fails, restore from your most recent good babkup
of the file.
 
Marshall said:
It's starting to sound like your database has become
corrupted. First, make a backup of the file so if things
get worse you can at least go back to where the problem
started.

Try creating a new blank database and check the appropriate
References. Then try to Import everything from the original
file.

If that doesn't work, check Tony Toews' site for other
corruption recoverery ideas.

If all else fails, restore from your most recent good babkup
of the file.
Click to expand...

Sorry, here's Tony'e URL
http://www.granite.ab.ca/accsmstr.htm
 
Thanks Marshall, re-importing the DB worked great. My DB
is back in production!

Bill - Thanks for your help too.
 
At the time, I didn't see your suggestion as the same
thing. Your suggestion sounded like a large ordeal to do
with a big database. But when Marshall suggested
Importing (using that terminology) the database, then I
checked the Importing options, I saw it wasn't a
difficult thing to try.

I thought I did try what you suggested by first trying a
new report within the same DB, then I emptied all the
records out of the existing DB (thinking maybe there was
a corrupt entry). I even created a new table (again,
within the existing DB). The fallacy here was the I
continued to use the corrupt DB for all my testing.

Now, in hindsight, I see your suggestion was the same
thing. I'm truly sorry about that ... it would have
saved a lot of work and effort on everyone's part!
 
Back
Top