Yes/No dropdown and table field validation

[ll]

Good Morning,
I'm currently working on putting a 'yes/no' dropdown box in my Access
form, without table lookup for the 'y/n' values. I had read that the
table field for this could be 1 char long, with a validation rule of:
'in("Y","N")'. However, I get a datatype conflict when I test the
form. When I take out the validation rule, it works seemingly
seamlessly. Is the validation rule actually needed, and can it be
made to work?

Thanks,
Louis

 

[Allen Browne]

A Yes/No field stores -1 for Yes (true), and 0 for No (false.)
The text "Y" and "N" are not the same thing.

You could use a combo box, and set its Format property to Yes/No.

 

[John W. Vinson]

Good Morning,
I'm currently working on putting a 'yes/no' dropdown box in my Access
form, without table lookup for the 'y/n' values. I had read that the
table field for this could be 1 char long, with a validation rule of:
'in("Y","N")'. However, I get a datatype conflict when I test the
form. When I take out the validation rule, it works seemingly
seamlessly. Is the validation rule actually needed, and can it be
made to work?

A Yes/No field does NOT in fact contain the letters Y or N; instead, it's a
small integer, with -1 meaning Yes and 0 meaning No. These are the only values
which can be entered into a Yes/No field so no validation rule is needed.

If you use a combo box, its Bound Column should contain -1 and 0; you can use
a rowsource of

"Y";-1;"N";0

Set the Combo's Column Count to 2, the Bound Column to 2, and the Column
Widths to 0.125";0" for example.

John W. Vinson [MVP]

 

[ll]

In clarification, I am using a combo box with "Y" and "N" values , and
the table field into which these values are input is a text field with
the aforementioned validation rule. When Y or N is selected, I get an
error message when the validation rule is in place, although there is
no error msg when the rule is taken away.

 

[John W. Vinson]

In clarification, I am using a combo box with "Y" and "N" values , and
the table field into which these values are input is a text field with
the aforementioned validation rule. When Y or N is selected, I get an
error message when the validation rule is in place, although there is
no error msg when the rule is taken away.

Sorry... thought you meant it was a Yes/No field not a text field.

Doublecheck the RowSource and Bound Column properties of the combo. It sounds
like the bound column is something other than the text column containing Y or
N - the combo box wizard will do that to you sometimes (create a numeric ID
and use it).

John W. Vinson [MVP]

 

[ll]

Sorry... thought you meant it was a Yes/No field not a text field.

Doublecheck the RowSource and Bound Column properties of the combo. It sounds
like the bound column is something other than the text column containing Y or
N - the combo box wizard will do that to you sometimes (create a numeric ID
and use it).

John W. Vinson [MVP]

Thanks John,
I'll give it a shot and let you know.

-Louis