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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 Architect / Systems AnalystCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.