Susan Harkins
ssharkins at bellsouth.net
Tue Jan 20 13:34:01 CST 2004
This is really over simplistic, but have you tried just deleting SD in the lookup table and re-entering it? You might also run an update on your many table -- *SD* = "SD" I've seen phantom characters work their way in before. The only way to get rid of them is to just delete them and re-enter the data. You're lucky, since you know the field it's in and even if it's in the many data, an update query should take care of it. If it's a phantom character, you won't find it. On the other hand, do any of your operators/users have access to action queries? Almost sounds like someone accidentially changed the SD string in your many table. Have you tried entering a dummy SD record to see if the old method can find it? Just curious. How are you entering that "SD" string? Might check there too. Susan H. > 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 >