Assigning blanks to values in lookup tables

[LeFennec]

I have a lookup table to identify my customer type. E is edu, G is Gov, and
NULL/blank is Std.
How to I get my Access Query to bring back the rows with this cell blank as
std?
Now it just brings back the rows that are populated with the E or G.

 

[Jeff Boyce]

In Access, Null <> "blank" (zero-length string) <> "space". But they all
LOOK the same on the screen!

If you are only looking for Nulls (or only looking for zero-length
strings)(or only looking for "spaces"), you're not looking for all the
possibilities.

Please post the SQL statement your query uses.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[LeFennec]

Here is the Query:
SELECT SAPImport.[SalesOrder#], SAPImport.BillingDoc, SAPImport.[Billing
Dt], Vertical.Vertical
FROM SAPImport INNER JOIN Vertical ON SAPImport.Vertical =
Vertical.[Vertical Code];

The Vertical table has values like this:
Vertical Code = SAPImport.vertical
Vertical
Some of the SAPImport.vertical fields are blank and are being excluded. The
Vertical table has a Vertical Code field that is blank with the Vertical =
Standard.

 

[LeFennec]

It is a simple table:
<table style="width:auto;"><tr><td><a
href="http://picasaweb.google.com/lh/photo/MLb8ioij0s8sQSpUatW_5w"><img
src="http://lh4.ggpht.com/_rh4MqX27mF8/SUrNGCYtrfI/AAAAAAAABx0/x_GUIfL1dwo/s144/Vert.jpg"
/></a></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px;
text-align:right">From <a
href="http://picasaweb.google.com/bart.taylor/SampleAccess">sample
Access</a></td></tr></table>

 

[Jeff Boyce]

It appears your query tries to join two tables. If you aren't seeing the
ones YOU consider "blank", perhaps what is stored in the two tables is NOT
the same "blank" (see my previous response). What happens if you remove the
second table (no table, no join) and limit the selection with something
like:

WHERE SAPImport.Vertical = ""

(that's two double-quotes, a 'zero-length string').

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[LeFennec]

This brings back the rows with those blank cells.
Now I need to make it bring back the value STD for "", EDU for E, and GOV
for G.
Thanks.
Bart

 

[Jeff Boyce]

The implication is that what you have in your second table is NOT what you
expected.

Rather than struggle with the differences among Null, zero-length string and
"space", have you considered using an actual, visible entry to represent
"STD"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[LeFennec]

Yes, I did consider that. We have a data dump from SAP to Excel. The
default data is the blanks, E, G. I want the queries to handle the raw data
from SAP so it can be as automated as possible.
There are 5 other fields that are coming back with this same problem, so one
loses 50 or so rows, the next loses another pile of rows, etc. The vertical
field loses about 75% of the rows because the default, most used value is the
blanks. At the end, I have about 85% of the rows being lost because of
blanks.

 

[Jeff Boyce]

If it would take more time/effort to modify the raw data, then you'll need
to determine which of the three varieties of "blank" you are working with,
and update your Access records to reflect THAT "blank", instead of expecting
the raw data to match.

Or, you could import the raw data, then run an update query on it (in
Access), and modify it to match what you are already storing.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP