Re: Hit a Jet index limit?


"Salad" <salad@xxxxxxxxxxxxxxxxx> wrote in message
news:6KCdnTPMooIQb7LWnZ2dnUVZ_gudnZ2d@xxxxxxxxxxxxxxxx
paii, Ron wrote:
"Salad" <salad@xxxxxxxxxxxxxxxxx> wrote in message
news:cuadnXBuUYU-SrLWnZ2dnUVZ_sOdnZ2d@xxxxxxxxxxxxxxxx

paii, Ron wrote:


"Salad" <salad@xxxxxxxxxxxxxxxxx> wrote in message
news:IbidncWLgeTCU7LWnZ2dnUVZ_jqdnZ2d@xxxxxxxxxxxxxxxx


paii, Ron wrote:



I have the following query, that has been in use for 3 years that

extracts


GL positing from a Jet/Access97 table that has existed for 10 years.

For

some reason the query quit returning records.

SELECT tblGLPost.*

FROM tblGLPost

WHERE (((tblGLPost.Comment) Like "INV*ENTER") AND
((tblGLPost.Tag)="0014266"));

tblGLPost.Comment is an indexed 30 character text field, duplicates

allowed


tblGLPost.Tag is an indexed 15 character text field, duplicates
allowed

The Tag criteria is added with each run of the query. Removing the

"Tag"

criteria, returns records. Replacing "INV*ENTER" with "INV*", returns
records. During testing I created and run the query from the BE file.

I did a Compact and Repair on the Backend MDB. I Created a new MDB
and
imported the tables from the old. I created a new table and copied
with

a


append query the records from the old.

After reducing the number of records in tblGLPost, the original query

works.


The table had over 900,000 records, I archived a few years and it now

has


569,826 records.

Has anyone seen this in Access?
Have I missed something else to try?




This is from A97 help because it's easy for me to find (topic
Specifications). The following are the limits for Database, Table,
and
Query. The size, I'll assume, grew in future versions but then you
have
to take into consideration Unicode. With 900K records, even if the
size
has increased sin A97, it's possible you hit a limit.

Database (.mdb) file size 1 gigabyte. However, because your database
can
include linked tables in other files, its total size is limited only
by
available storage capacity.

Table:
Table size 1 gigabyte

Query:
Recordset size 1 gigabyte


The file size of the BE MDB file containing this table is 88,852 KB as
reported by Windows.



It appears you have plenty of room to grow. Who knows, maybe there was
a #Error in one of those fields. It would have been hard to track down
if there was one. I don't believe a compact/repair would remove/fix a
corrupt value.

Did you keep a backup prior to archiving/removing records. If so, what
happened if you removed the filter. Then put in the first part of the
filter (((tblGLPost.Comment) Like "INV*ENTER"). Then again on the 2nd
part of the filter ((tblGLPost.Tag)="0014266")).

If it worked for no filter and for one of the filters but not the other
then there could have been a corrupt record...but that's just guessing.



Thank you for you reply

The query works if remove the ((tblGLPost.Tag)="0014266")) part or if I
change the comment filter to (((tblGLPost.Comment) Like "INV*"). It
looks
like having the "*" wildcard in the middle cause the problem. Using
(((tblGLPost.Comment) Like "INV*ENTER*" AND
((tblGLPost.Tag)="0014266")))
does not work.


Who knows. Maybe a recent Windows update caused some glitch.

What happens if you try
InvCheck1:Left(tblGLPost.Comment,3) (criteria "INV")
InvCheck2:Right(tblGLPost.Comment,5) (criteria "ENTER")
InvCheck3:Clng(tblGLPost.Tag) =14266 (criteria True)

Any difference if you use % instead of *?

The tag field seems to contain a problem in a record if it doesn't work.
Same with Comment. If that's the case, it's most likely a recent
error. Maybe look at records from the last month or two to limit your
search and see if you see any field that contains #Error in it.

I modified the query as such and get a "Invalid use of Null" if I put
anything in for a criteria for the calculated Tag field.

SELECT Left([tblGLPost].[Comment],3) AS InvCheck1,
Right([tblGLPost].[Comment],5) AS InvCheck2, CLng([tblGLPost].[Tag]) AS
InvCheck3
FROM tblGLpost
WHERE (((Left([tblGLPost].[Comment],3))="INV") AND
((Right([tblGLPost].[Comment],5))="ENTER") AND
((CLng([tblGLPost].[Tag]))=14266));




.

Relevant Pages

  • Re: Display if count = 5
    ... criteria line of the field that is being counted, ... And, if you count something in the design grid in a query, ... if you have designed a complicated filter. ...
    (microsoft.public.access.queries)
  • Re: other table that stores data differently than ADO.Net datatable?
    ... >> would suggest making some sort of a "search" type of screen and filter ... >> criteria for your where clause, that will help slim down your list. ... >>> query the database everytime the user does a scroll (or page up/page ... >>> will display the next N records once the user has reached the last ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Near Suicidal!
    ... box to filter by pressing a button on the form where the list ... By pressing on the button I want to pass a parameter (or ... to the the query the list's rowSource is based on. ... example) into the fldChargeType's criteria row and ...
    (microsoft.public.access.formscoding)
  • Re: Query only showing specific records - i need all - please help
    ... If the data is in the table but not in the form, you may have a filter on ... If the form is based on a query, then, when you look at the ... have a criteria in the query that filter out those records. ... You could also have use a Format to format the NULL values as displaying ...
    (microsoft.public.access.queries)
  • Re: access 2003
    ... the "ChooseJob_AfterUpdate Event" uses this filter ... the form to show a new set of records: For, as you know, the query points to ... the two combo boxes for criteria information. ...
    (microsoft.public.access.conversion)