using number field for a checkbox

[tagteam]

I am trying to build applications with the idea of moving the data to sql server. So for my boolean field I set it to be a number field - integer with 0 for decimals and 0 to be the default.

I want to use this in a checkbox on my access form. Is there any way to use a checkbox and have it specify that 0 will be unchecked and 1 will be the checked value?

[Gicu]

I suggest you use the Bit data type in your SQL server backend which is a numeral data type with 0 for False and 1 for True. Make sure all Bit fields have a default value and set the Allow Null to False.
https://stackoverflow.com/questions/...-in-sql-server

Cheers,

[tagteam]

yes, but the data is currently in Access and not sql server so what should I set it to if it is in Access? Access only has Byte, Integer, Long Integer, Single, Double and Decimal. I probably wont be able to move the data until next year and I want to see if there is a way to get it working now in a way that will transition to SQL server well.

Either way my main question is "Is there a way to use a checkbox in access with a field that you plan to store 0 or 1 in?"

[Welshgasman]

What is wrong with boolean?

[tagteam]

There is no boolean in SQL server. Boolean in access will show raw data as -1 and 0. It will work with SQL server but in SQL server will show 0 and 1. I dont want to have to redo queries or have confusion.

[Welshgasman]

No idea, but as Gicu mentioned, try and use the BIT type.
In Access -1 and 1 both resolve to True (at least in my tests)

In fact if you add a Boolean (Yes/No) field to a form, it starts life as a checkbox.

I have no idea what would happen if you upload -1 to a BIT field in SQL server, but you could just as easily use ABS(fieldname) to make it 1, surely ?

Edit: I meant boolean on the Access side.

[Gicu]

If you build your queries\code properly (using =True\False not =-1\0) you will not have to redo them when upsizing to SQL server; and the values will be converted by the upsizing or importing tool\ODBC driver so no need to worry. As I say the only thing to do is to make sure that when times come to upsize you go through all your SQL tables and for each Bit field you set the default value according to your logic (sometimes 0=False, sometimes 1=True) and set the Allow Nulls to NO.

Cheers,

[tagteam]

Hey guys, thanks for all the advice. I will take it into consideration, I think I found my answer here though http://allenbrowne.com/NoYesNo.html

[Welshgasman]

Good find.

[tagteam]

Hey guys, maybe you can help me with this. At one point Allen Browne says:
In the lower pane of table design, the field has a property named Display Control on the Lookup tab. The interface does not offer Check Box as an option, but you can set the property programmatically like this:
CurrentDb.TableDefs("Table1").Fields("Field1").Pro perties("DisplayControl") = CInt(acCheckBox)
substituting your table and field names. If you receive an error saying the property does not exist, set it with SetPropertyDAO(). The field now displays as a check box in datasheets, and Access gives you a check box when you create a form. Set the other properties for this field as illustrated (below left). Note that in table design , the Display control shows as 106 - the value of acCheckBox.


I dont understand what that means or how to do it. Do I have it in the format, do i put it in vba and run it once? not sure, any help would be appreciated

[Gicu]

Yes, I just tried it, just run it once (for each field) from VBA.

Cheers,

[tagteam]

Hey Gicu, thanks, I am not a programmer so not sure what to do. I tried it in the immediate window but nothing happened. Do I need to make a function or what? The only vba I usually do is on click events and stuff like that.

I imagine I update that code and then paste it in the vba somewhere but I dont know where or how to "run" it

Thanks

[Gicu]

You can run it in a Click event (of a new button on a form) or you can run it from a module:

Click Run on the ribbon and it should run for you (might prompt you to choose the sub to run).

Cheers,

[tagteam]

Get Gicu, thanks I am going to try this today. Please, for my enlightenment and future use, when I click run on the ribbon it opens the Macros box and wants me to run a macro. I would like to learn how to run something without needing to assign it to a button.
Thanks,

[Gicu]

Try to click inside the procedure you want to run (and I usually put a break on the first line).