Querying for value that shouldn't be in column?

  • Thread starter Thread starter Tim Rogers
  • Start date Start date
T

Tim Rogers

There is a table (let's call it A) in our Access97 application that
has a column that is a combo box lookup. This lookup is a select
statement of an ID (PK) and a friendly name from another table (let's
call it B). The bound column is 1, as expected.

The 'weirdness' comes in with the form that manipulates table A.
There is a combo box on this form that does the same lookup, on table
B, as described above, but it UNIONs this lookup with a hard-coded
string value. So, the user can now select values from table B as well
as this string value to be data in the aforementioned column in table
A.

The issue I'm dealing with is trying to query table A. There is a
query that isn't working as expected. The query was built using the
Query builder and it recognized the relationship between the column in
table A and the Id column in table B. This select query does not
bring back rows in table A that have the hard-coded string value in
the column in question.

This may be poor design, but at this point I've got to fix this query
to bring back the appropriate rows. So, what I thought I could do is
use the existing query and then UNION it with another select query on
table A in which the WHERE clause would simply look for rows in table
A where that column matches the hard-coded string value. This union
would give me the result set I need. The problem is the WHERE clause
fails to make the match I think it should. Is there something special
I need to do to match this string value, in a query, given the fact
that the column in table A is a lookup column?

When I look at table A's data, I see many rows with the column 2
values from the select statement on table B as I expect. I also see
the hard-coded string value in many rows as well. This column in
table A is bound to column 1 of a query, but displays column 2. So, I
don't know what this means when some hard-coded string is entered as a
value for this column. It doesn't seem like anything good could come
from doing this.

Any help would be much appreciated.

Thanks,

Tim Rogers
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe the query in the combo box's Row Source property looks
something like this:

SELECT "<ALL>" As ID, "<ALL>" As Desc_Col FROM TableB

UNION

SELECT ID, Desc_Col FROM TableB

Correct?

If you want a numeric value (the ID column value of TableB) you will
have to replace "<ALL>" (or whatever it is) with a number that is
suitable to your needs. Some use 0 (zero) or 9999999 as defaults.
You should also change the data type of the column in TableA to the
proper numeric data type (to the same data type as in TableB), so it
will not accept strings. You'll have to change all the string values
in the column to numbers before changing the data type.

It is better to have a default value in TableB rather than "hard
coded" in the ComboBox's RowSource SQL statement. I usually create an
entry in the lookup table (your TableB) of ID = 9999 and Description
of "UNKNOWN." Then I don't have to have a UNION query in the
ComboBox's RowSource.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQB63SoechKqOuFEgEQI16wCg/s3QZ3TrbQbjgPKasHFKzyN4DuEAn38W
Fye5eHfMiwhURnCHdc6Z2wE2
=hVi5
-----END PGP SIGNATURE-----
 
Yes, you are correct. The combo box query does look similar to what
you have posted (although the Desc_Col string value is not the same as
the ID value for what it's worth). The column in table A and the ID
(from the query from table B) are actually string values. (i.e. The
PK in table B is a string.) So, I'm trying to make the following
query work:

SELECT tblNotes.NoteID, tblNotes.SiteID
FROM tblNotes
WHERE (((tblNotes.SiteID)="<ALL>"));

where <ALL> represents the hard-coded value in my app. The problem
is, this returns no rows, but it seems like it should since there are
many rows in tblNotes where SiteID displays a value of "<ALL>".
tblNotes.SiteID is a lookup field, whose values come from a query from
another table. SiteID is designed as 'bind to column 1, but display
column 2'. So, when "<ALL>" is inserted into the SiteID field, I
assume Access must somehow resolve either 'column 1' or 'column 2'.
Maybe Access is generating some 'ID string' to store as column 1 in
the SiteID field. Maybe that's why my query won't match.

How can I make the following query above work?

Thanks,

Tim Rogers
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Have you tried the reverse query - WHERE SiteID = "<ALL>" to see if
you can get any recs that have the "wrong" data string?

But, if I understood you correctly, the NoteID column has the value
"<ALL>" also. Could you try to get records WHERE NoteID <> "<ALL>" ?

You understand the ComboBox Bound Column correctly - if it equals 1
then the first column in the RowSource query is the column value that
is placed in the bound table.

Did you create this application, or did you start where someone else
had left off? Because I've come into applications after someone else
has incorrectly set up the app & the results were bad data. I've have
to go thru & correct the old data before proceeding and *believing*
what was happening. Perhaps the combo box is not the culprit in this
situation. Is there another form/control/query that loads that table
w/ data? If so, is it putting the correct data in the correct
columns?

Rgds,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCB4v4echKqOuFEgEQIrXwCg/kioGbRcLbFtJDEPqpM8Lk3n3MwAoP9u
NabvejBCExmrZNyZMFHOAbnp
=5OAn
-----END PGP SIGNATURE-----
 
The query - WHERE SiteID = "<ALL>" is exactly what I have been trying
to get to work this whole time, without success. "<ALL>" is the value
of the field SiteID for several rows in tblNotes. For some reason the
query fails to bring back those rows. I suspect it's because SiteID
is supposed to have values from a lookup query (which is the typical
lookup a PK and a friendly string value from another table - and bind
column 1, but display column 2 from the lookup to this field), but
there is a form in the app that allows the user to stick a completely
unrelated hard-coded string value into that field (what we have been
calling "<ALL>").

I've inherited this app, and am new to access. So, this may be a bad
design and I won't be able to get this query to work until I modify
the design. I'm trying to avoid that.

Thanks for your time.

Tim
 
Back
Top