help with blank data fields

[lnkranio]

I am working with a database with a lot of variablilty in the way data is
entered. I am using conversion tables to reconcile entries, but I'm having a
big problem with blank fields. I need to let my conversion tables know that
"no entry" means the same as "0" so I can output these in a single category
for analysis. But I can't seem to figure out how to make a conversion table
combine "blank" with "0" (or another input).

Can anyone help? Thanks,
Linda

 

[KARL DEWEY]

Post the SQL of your query for editing.

 

[Linda]

The query is
SELECT CS_Patient_History.Syndromic, syndCT2.Field2
FROM CS_Patient_History LEFT JOIN syndCT2 ON CS_Patient_History.Syndromic =
syndCT2.Field1;

syndCT2 is a two field table containing the values
0 NS
no NS
Is Null NS
1 synd
2 synd
yes synd

'Is Null' in this example is just my most recent attempt to assign the value
NS to the blank inputs; I've been trying a hit or miss approach in the hope
of magically stumbling upon a syntax that works.

thanks,
Linda

 

[KARL DEWEY]

Your syndCT2.Field1 is a text field and as such the query interprets ‘Is
Null’ as a text string and not a logical function.
A text field can be null or a zero length string. Both look alike to humans.
Modify your table to make the default of Field1 as Null. Create another
record and do nothing with that field but add ‘NS’ to field2. Edit out the
text string you have as ‘Is Null’ in the other record.

Then try it.

 

[John Spencer]

Ok, Nulls are hard to handle in this situation.

You could use something like the following. One problem is that this
will change ALL values that cannot be matched to NS. So if you had a
value in CS_Patient_History.Syndromic that was not listed in the syndCT2
table you would see CS for that as well as for null values.

SELECT CS_Patient_History.Syndromic
, Nz(syndCT2.Field2,"NS") as Field2
FROM CS_Patient_History LEFT JOIN syndCT2 ON
CS_Patient_History.Syndromic = syndCT2.Field1;

The NZ function replaces null values with whatever is after the comma.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Linda]

I have followed your instructions but I'm still seeing the same output - an
empty field in CS_Patient_History.Syndromic still returns an empty field in
syndCT2.field2. I even created and retyped a fresh table to make sure I
hadn't inadvertantly changed anything else in the field properties. Can you
think of anything else I might be doing wrong?

Just to be clear, I'm supposed to type the word Null in the box for Default
Value under field properties for field 1, right? Do I need to go back to
CS_Patient_History.syndromic and change that field default to null as well?

Linda

 

[Linda]

Once again, that did it. And I appreciate your warning about overwriting
values not found in the table - not so much an instance in this particular
situation but it could become a problem in typo-prone fields. So I'll use
this solution sparingly and cautiously.

Thanks again,
Linda