[AccessD] Strange Table Behavior...

Colby, John JColby at dispec.com
Tue Jan 20 13:34:03 CST 2004


Try dropping the index on that field and re-creating it?  

John W. Colby
The database guy


-----Original Message-----
From: Greg Smith [mailto:weeden1949 at hotmail.com]
Sent: Tuesday, January 20, 2004 2:24 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Strange Table Behavior...


Hi everyone!  Happy Tuesday...;)

I have a table in a rather large database which is having some sort of odd
issue.  It's 6 columns, one autonumber field, 4 text fields, one date.  It
currently contains about 524,000 records, and is on the MANY side of a
one-to-many relationship from the main table.  This is nothing
extraordinary...just fairly simple for a table.

One of the text fields contains indexes used for looking up data in the
primary table.  The data in this field is a variety of ATT, SD, CLD, CLM,
CLR&A, etc.  When someone looks up, let's say, a City Lot Mortgage (which
would be CLM), that CLM is used in the criteria portion of the query to
lookup the matching index and data from the primary table. And, because of
other fields on this lookup form, this field also has to be able to search
via the wildcard paramater of *, so the criteria in the query is actually
"Like forms![FeeBookSearch]![Index]", without the quotes and the [Index]
field then contains CLM, or * if it's to be ignored in the search.

To our knowledge, this has been working just FINE for almost two years.

Last week, the SD quit working.  But ATT, CLD, CLM and all the others worked
fine.  I still keep telling myself that's not possible, but it's true.

And it's now happened again today.

I fixed it the last time by creating a NEW table from this data (everyone
was out of the db), deleting the old one and reestablishing all the
relationships.  PITA to say the least.

What's really piqued my interest is how in the world can SD NOT work and all
the others work?

There is nothing visible in the SD field other than SD.  I've even exported
it to other programs like Excel to see if anything else was in there, but
nope...zip, zero nada.

Now get this:  If I create a test query on that table, and use Like [TestMe]
as the criteria for this index field, when I run the query it'll obviously
ask me for TestMe.  If I put in SD, I get zero records (should be about
48,000), if I run it again with ATT, I get all of the Attorneys, the same
with CLD and CLM, etc.  Everything else works.  And here's the clinker:  If
I use SD* or *SD, I get'em all!  But SD by itself still won't work.

It acts like there's something else in the field not allowing the match, but
I'll be dipped if I can figure it out.

I'd rather not cobble to code to fit this data quirk, but I'm rapidly
running out of options and time at this point.  Needless to say the Recorder
has already stated her opinion in my ear once today and I'd rather not have
her repeat it.

Sorry about the length of this post...but I tried to explain it fully to
head off questions (yea, right, like I write anything clearly the first
time) anyone may have.

Any ideas or tests to try would be greatly appreciated.

TIA

Greg Smith
Weeden1949 at hotmail.com



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list