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