MS Access 2010 form question

Factual Questions
1

I’m a relative newbie at creating Access forms and I could not find an answer to his on the Access forums I usually reference. Here’s my question:

I’ve created a form that adds records to a table “DATA.” I want the user to select from a combo box the value “Y”, “N”, or “N/A.”

If the user selects “Y” I want the DATA record to have a 1; for “N” a zero, and for “N/A” a null value.

So I have another table, VALUES, that looks like this:


1  Y    1
2  N    0
3  N/A

In the combo box properties, I have Row Source = VALUES, Row Source Type = Table/Query and Bound Column = 3. For the formatting I have Column Count = 3 and Column Widths = 0;3;0.

This works fine for “Y” and “N” values - it populates my DATA table with the value of 1 or 0. And in fact if I select “N/A” it populates DATA with a null value. But…

The problem is in my form, if I select “N/A”, it disappears. It’s as if because the value of the field in DATA is now null, it’s not displaying a value in the combo box. I want it to show the “N/A” that the user selected.

Is there another way I should be doing it?

2

Try a value of @N/A instead?

3

If I change the value of “N/A” to 3 (in the third column), or any other value except null, it works. The form shows “N/A” and the controls source “DATA” gets that value assigned to that field for the current record. So it’s a problem with using null but I’m not sure what the way around it is if I don’t want that field to have a value.

4

I don’t think there’s an easy way around it. The equation NULL = NULL does not evaluate to true in Access, so it can’t match the value from the table to any values in your combo box.

Maybe you could accomplish it with some back-end code. How complex do you want to make this?

5

Yeah, that seems to be the problem. There’s probably an easier workaround than spending time on trying to code something. Instead of NULL I’ll try using an arbitrary value and then ignoring that value later when I create my reports.

6

Actually, here’s an easy way: as you suggested, use an arbitrary value for N/A (say “@NA”). Then add another field to your table with datatype Calculated and the formula below:


IIf([MyValue]="@NA", Null, [MyValue])

It’s essentially what you said, but you can use the calculated field in your reports instead of worrying about ignoring a specific value.

7

Hey, I think that will work, because I already have to have a calculated field with the resulting field value anyway. I should be able to stick your IIF statement into my formula. Thanks for the idea!