[AccessD] Strange Table Behavior...

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
>



More information about the AccessD mailing list