Using TinyInt datatype to accept ticks in an Access Check Box

Posted on 2007-11-23
Last Modified: 2008-02-01
I have a set of check boxes in an Access mdb file.  The mdb fle is linked to a set of tables in an SQL Express 2005 database.  Bit datatypes have caused all sorts of issues, so I have to use tinyint.  However, if I try to put a tick in a checkbox I get "Value Entered isn't valid for this Field".  This would appear to be because Access treats a tick as a -1, and of course tinyint fields can only accept 0 to 255.  Is there a way of making the value of a tick in access +1 instead of -1?
Question by:cmcltd
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6

Expert Comment

ID: 20340582
have you considered using smallint then for the 2005 field ????  -2^15 (-32,768) through 2^15 - 1 (32,767).

Author Comment

ID: 20340599
Thanks Paul

I tried using Int first of all, but the problem is that I actually want the value to be recorded as +1, just as it would be with a bit field.  Bit fields are giving me all sorts of problems, most notably the red herring of an error message telling me that another user has made changes to the record since I opened it.

Expert Comment

ID: 20340698
:P  .... ok, well the only other cludge that I can think of would be to put a trigger on the SQL table to populate either another column (AFTER trigger), or to change the value from -1 to +1 when the cell was updated (INSTEAD OF trigger - though of course that might mess up the Access app).  Or create a view converting the field using ABS() function - but if you really can change the definition of a system datatype in Access like that, that would be interesting to know... good luck !! :)
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

LVL 74

Accepted Solution

Jeffrey Coachman earned 250 total points
ID: 20341658

<Is there a way of making the value of a tick in access +1 instead of -1?>
The short answer is... NO

Here is the translation table:
Yes      -1      On      True
No      0      Off      False

Correct me if I'm wrong, but ultimately you need to check the box "On" with a value of between 1, instead of -1, correct?

So then you don't really need to "Store" it (which, in Access, you can't do anyway), but rather you just need to "Display" the check, and "generate" a  +1.

I will presume that you want the Boolean (0,-1) Field in Access to control the creation of this new +1 Field

You can try this:
Create a function that converts -1's in Access to +1.:
Function FakePositive1(ValueToConvert As Integer) As Byte
Dim bytTempVal As Byte

    If ValueToConvert = -1 Then
        bytTempVal = 1
    ElseIf ValueToConvert = 0 Then
       bytTempVal = 0
    End If
    FakePositive1 = bytTempVal

End Function

Create a query of your table and add a new field that will use this new function:
SELECT tblYourTable.ID, tblYourTable.SomeNameField, tblYourTable.YourBooleanFieldInAccess, FakePositive1([YourBooleanFieldInAccess]) AS FakePositive1
FROM tblYourTable;

Then use this Query as the source for your form.
Now "checking" the box will store a -1 in the table, but Calculate a +1 for SQL Server.

Of course, as I stated above, the "FakePositive1" data is not "Stored", but calculated.
I'm guessing you can create a "View" in SQL Server that hides the original Access Boolean Field and displays/uses the new "FakePositive1" Field.

This entire process can be reversed to let the SQL Server table control the Access data, but again, I am presuming that you are using Access as your Data Entry Front End.

Here is a sample

But I'm curious, why not just use the "SmallInt" Datatype in SQL?
SmallInt will allow the -1.

(Note: Access checkboxes and Option buttons have a "Triple-State" property that, if turned on, displays a shaded
checkbox/Optionbutton for NULLs)


Expert Comment

ID: 20342443
yes - as I said "Or create a view converting the field using ABS() function" ... BTW: why create a function - just include this field in the query: FakePositive1: ABS(ValueToConvert)    !!!!!
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20343052
<Or create a VIEW converting the field using ABS() function>
I was doing this from the "Access" side, so my conversion was done in an Access Function.

<why create a function - just include this field in the query: FakePositive1: ABS(ValueToConvert)    !!!!!>
...For portability and consistency.
If there is even the slightest chance that this type of calculation will be used again, the function can easily be imported to a new DB.

Yes, If I had just used Abs(), a function might have been over kill.
But to be consistent I try to make functions and call them.
For example
FakePositive1: ABS(ValueToConvert)... is easy enough to type directly.
But typing this over and over and over for every query...
FullName:[L_Name] & ", " & nz([M_Name],"") & ", " & [L_Name]
... is quite another story!
I'd rather make a function and call it!
How about calculating age?
Age:DateDiff("yyyy", [Birthdate], Now())+ Int( Format(now(), "mmdd") < Format( [Birthdate], "mmdd") )
Why not just call a function?:
Age: Age([Birthdate])

So for consistency I always try to make functions and call them, instead of saying:
"Type it directly for short calculations, but write functions for Long calculations, except when it's a medium calculation then...

I thought about using Abs(), but I thought Abs(0) looked a little strange.

But I too am still confused as to these "issues" with using SmallInt.

In your first suggestion:
<have you considered using smallint then ...>
... was followed by:
<I tried using Int first of all...>

But what about trying SMALLINT?

In conclusion, since cmcltd's profile states that they are an "Intermediate" user, I tried to create a solution that could be customized easily to deal with reversing the program control, if need be.
I always try to provide a sample so that my suggestions can be easily implemented.

Take care!


LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20343079

You never actually stated if you tried SMALLINT?

Expert Comment

ID: 20343162
hmmm .... k - I understand the logic, but wait a minute Jeff - you have created a UDF for a function which is already a system function (getting the Absolute of a value), and then you've spent four lines of code in the UDF to do what you could have done in I'm a bit freaked out I must admit !!!!.

Actually, I still think this is a strange approach for what cmcltd claimed they were trying to do - I would have thought the whole idea would be to have the database to be updated, hence the objection to the bit datatype in the first place, and hence the trigger suggestion.  Of course this would have been implemented directly on the table in MSSQL, and would have been something like:

create trigger ABSOLUTE_FLAG on MyTable
UPDATE MyTable SET FakePositive1 =ABS(i.ValueToConvert)
FROM inserted.i mytable t
WHERE i.MyKey = t.MyKey

..but hey - what do I know ??

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20344163
PaultheBroker ,
<you have created a UDF for a function which is already a system function >
I actually created a function that says if the value is -1 convert it to a +1
Or else if the value is zero, convert it to Zero.
It could be modified easily for almost any combination.

When I first read the post I thought that perhaps they might want to convert -1 to "any" positive number, not just +1. That's why I chose the Function approach. If the criteria changed, so could my function.
I could modify my function, I can't modify the built in functions.

What I was trying to do in the sample was to show cmcltd that you could use this type of logic (If-Then-Else) and apply it anywhere, be it in a function, a procedure, or in a from Control, in this app. or any other.

The sample also had a function reversing the logic.
If I had used Abs(), I would have had to do something like Abs()*-1, which, to an Intermediate user, might look a little odd.

cmcltd's status as an "Intermediate" user said to me that they might not know anything about Triggers or Views.

Please remember, I simply posted my technique as just another suggestion, nothing more.
I never said it was more efficient, or better than any other.

Had cmcltd's status been "Beginner" I would have posted every single step involved in implementing my solution.
Had cmcltd's status been "Advanced" I would have just said: "Build a function that..."

But the Status was Intermediate, so I:
1. Made my proposal
2. Explained it fully
3. Posted the Code and SQL
4. Provided a sample

Perhaps cmcltd was looking for this type of solution?
Perhaps cmcltd was not comfortable using Abs()?
I don't know.

I post here for fun not profit :)
I try to provide all the info a person would need to implement my proposal.

I try not to cross over the line of actually doing it for them.
(The old "Give me a fish or teach me to fish" proverb.)

I provided a sample. However, cmcltd still has to modify it to work in their app.
I'll go the extra mile WITH them, not FOR them!


Expert Comment

ID: 20344526
I think we are all in this for the learning experience, so please don't take this the wrong way, but I'm having trouble understanding comments like 'If I had used Abs(), I would have had to do something like Abs()*-1' .  As you know, ABS(myVariable) returns the positive value of any number - ABS(0) = 0 and abs(-1) = +1 ...(why do you think you need to  multiplying by -1 ? That would just -1 again!!!!)  So actually I'm saying that there is a built in function which does exactly what you wanted it to do:

>I actually created a function that says if the value is -1 convert it to a +1
>Or else if the value is zero, convert it to Zero.

, and yet you implemented this functionality through an IF..THEN  statement (4 lines) while 1 line (ABS) would have done it.  Now please understand that I don't actually care at this point what cmcltd was or was not looking for....I'm just trying to explain to you that I think you implemented this particular problem incorrectly, and would like you to learn from my experience.  Unfortunately it seems that I've been unable to convince you that (a) it may be incorrect to create a UDF for THIS PARTICULAR CASE, and (b) given that you ARE implementing a UDF, that you should use a perfectly good inbuilt function (ABS) within that UDF to acheive your end goal in the most efficient manner possible.

So I guess I've failed....and for that I apologise !!!!  But please don't misunderstand me on this either - I'm just trying to help - I'm not trying to tell you what to do !!  Take care, and keep spreading the joy..

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20344855

*Sigh* more time,...the If Then Else was an attempt to illustrate to the poster that they "could" use something like this and modify it for any combinations of numbers or ranges of numbers, negative or positive.
Is it not possible that the poster found some value in this approach?
You have to ask them.

Would Abs() been slicker, cooler, and more efficient, Yes "Abs()olutely"

Perhaps cmcltd has taken your advice and "Fixed" my solution.
Then this is all a moot point.

<you implemented this particular problem incorrectly>
Incorrectly? What happened to saying Inefficiently?
Incorrect implies that it was wrong, or did not work.

My solution did work.
Was it NOT as efficient as Abs(),... Yes
Was it "Incorrect",... No.

2*2*2=6 is "Incorrect"

2^3=8 is "Efficient"

2*2*2=8 is "IN-efficient" (not "IN-correct")

In the industry we work in, Incorrect is a strong word, my friend.
If a person needs to copy two records from one table to another, is it "Incorrect" to use Copy/Paste, instead of INSERT INTO?
how about 5 records,...10?
When is it correct/incorrect to use SQL vs a Recordset it insert records into a table?
Who should decide? You, Me, The poster, angelIII, Bill Gates,... Ken Getz?

Again#2, My post was just another way of doing it.

If you count, I believe that I have admitted at least 3 times that Abs() would have been more efficient.
So, I am confused as to why you are dedicating so much time and energy restating it.
But I am flattered!

You posted the Abs() solution, and I posted mine.
Both were evaluated by the poster.
I really can't control the decision the poster makes.
You must ask them.

"Inefficient" solutions are accepted here all the time.
Inefficient does not mean Incorrect.

Sometimes posters will accept the solution that is easier to understand or implement over the one that is "Better".
Most of us acknowledge it, and simply... move on.
If there is a concern, it is voiced, then we ...move on.
There are no 3 page diatribes, we just ...move on.
No statements about "correctness" or "incorrectness"...we move on.

A common request here is storing calculations in a table.
They create a TotalPrice Field in the table and want to store in it, the product of Price * Quant.
Now with your knowledge in SQL, you know that that is the ultimate in inefficiency!
But you will get an expert who will tell them how.
Then this "Incorrect" answer gets accepted.
The poster needed it in a hurry.
They did not understand the "Efficient" SQL string the Expert posted.
That's the way it was always done.
The poster could not understand why a calculated value in a query can be functionally equivalent to a "Real" (stored) value.
They just don't trust these creepy "Calculated Fields", and feel better if the value was just "stored".
Their boss said: "I can do this in Filemaker, so you HAVE to do it in Access." (grumble, grumble)

The same is true for people who would rather print the form, than a report.
Or people who want Labels to act like command buttons.
Or people who want colored checkboxes.
Or people who want to add an order for a customer who does not exist.
Or people who want to create main forms with 17 subforms, on 17 different tabs on a tabbed control.
Or people who want comboboxes to display pictures.

...For these questions, there is always someone who will tell them how.

For these people, for what ever reason, one solution is chosen over another.

In that case, for them, there is no right or wrong, just "Accepted".

We all see this and sigh... and move on.

Please remember, I posted:
An explanation
The Code and SQL
A Sample.
Is it possible, that the poster could have preferred this approach?, however inefficient?
You can ask them.

Just perhaps, they wanted something they could just drop into their app, however inefficient?
You have to ask them.

Perhaps the poster has made a mistake.
You can contact the support staff:
..and request that the question be re-opened.
I have no objection.

Now please don't misunderstand ME on this:
If you had dedicated this much "persuasive energy" into you first post,....... (drumroll)
...I would have been too embarrassed to even post mine!

Good night my friend!

Jeffrey Cornell Coachman

Expert Comment

ID: 20345181
>If you count, I believe that I have admitted at least 3 times that Abs() would have been more efficient.
Sorry - I missed that.  I would never have spent so much time on this if I had picked that up from your posts - and yes, you are 100% right - I meant 'inefficient' - sorry about that too - my fault entirely.  Perhaps we could have avoided this entire post if I'd been clearer !!!!!

Hey - I don't care at all about what cmcltd accepted or didn't accept - you're completely welcome to the points and I'm not disputing that.  I'm just happy that you (Jeff) DO actually understand that ABS would have been more efficient !!! :) ... phew!!!!!  Glad I got that off my chest !!

See you round !!!  ROUND(CU)  :)

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20346139

I think that evereyone has a something they can contribute to this world, no matter how small or large.


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how the fundamental information of how to create a table.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question