Using negative values as an ID join field

I switched the Boolean fields in my database to number fields based on Allen Browne's concerns about the problem with Boolean fields causing errors in queries. I would still like to be able to use checkboxes, so I am sticking with 0 and -1 as my values. However, there are times in list views when I don't want a checkbox because it is too easy to uncheck a box when you are scrolling through. In that instance, I would like to select from  a combobox with the values Yes and No in the list. The table would look like this:

ynID     ynYesNo
0          No
-1         Yes

I know you can create a join on any field, but I have never thought about using negative numbers in a join. My concern is causing a condition where I could corrupt the data, otherwise this seems doable to me. Would this work, or does someone have a better solution?
Kim HowardAsked:
Who is Participating?
 
mbizupCommented:
<<If I use a Value List, then the values will be stored in text field as Yes and No >>
Regarding the Value List approach, it's doable with the following property settings:

Column Count: 2  
Column Widths: 0", 0.25"
Row Source:  -1;"Yes";0;"No"
Row Source Type: Value List

This configuration will save the integer values.
0
 
PatHartmanCommented:
Having a negative number would not affect the join but what would you be joining to?  You aren't really going to create a table with just the two values are you?  Why not use a Value List as the combo's RowSource instead?
1
 
Kim HowardAuthor Commented:
I agree that it seems goofy to create a table with only two values.  However, if I use 0 and -1 as the values to reference the other table, I can also use a checkbox control on certain forms. If I use a Value List, then the values will be stored in text field as Yes and No and I can't use the checkbox. I am doing this to create a clearer and simpler user interface.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Whereas the issue Allen describes could happen, I have yet to encounter that situation in 26 years of working with Access ... just sayin'.

"because it is too easy to uncheck a box when you are scrolling through. "
Hummm ....
2
 
John TsioumprisSoftware & Systems EngineerCommented:
Well if it is not much hassle can you post a screenshot to describe the issue.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
However ... one advantage with using an Int as your 'Boolean' field, you can if desired implement a true Tri-State checkbox, wherein you have True (-1). False (0) and Null - indicating the checkbox has never been 'checked' (clicked on). This is because an Int Data Type in a Table can be set to Null, and a Boolean cannot.

===
0
 
Fabrice LambertFabrice LambertCommented:
Positive or negative values doesn't matter, as it is just that: Values.

You can perform a join on fields no matter what, as long as data types are the same.
0
 
Kim HowardAuthor Commented:
mbizup,
Thanks! That was exactly what I was looking for!!!
0
 
Kim HowardAuthor Commented:
I ended up using a Values List with these property settings:

Column Count: 2  
Column Widths: 0", 0.25"
Row Source:  -1;"Yes";0;"No"
Row Source Type: Value List

Thanks again mbizup!
0
 
mbizupCommented:
Kim,

Glad to help out!  I think Pat Harman’s comment, though, should have been marked as accepted since you went with the Value List approach.  I just added detail to her suggestion.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.