Setting up a pseudo-boolean for conversion to SQL Server (1 Viewer)

JMongi

Active member
Local time
Today, 06:20
Joined
Jan 6, 2021
Messages
802
Since SQL doesn't use boolean fields (as I understand it anyway), what is the best way to set up a field in Access if your BE will end up on SQL server express?

I know in SQL Server itself (from Googling) there is a Bit type that is used. Is there a best practice to make this transition as seamless as possible?

For the real-life issue it would be a for a field indicating whether something was concrete filled or not.
 

Minty

AWF VIP
Local time
Today, 10:20
Joined
Jul 26, 2013
Messages
10,353
I just use an integer field and use -1 for true and 0 for false.
You can use a bit field but it gets confusing as Access doesn't like Null values in Bit fields that is the real issue;
http://allenbrowne.com/NoYesNo.html

As long as you set the field to not accept null values and always have a default value you should't have an issue.
 

JMongi

Active member
Local time
Today, 06:20
Joined
Jan 6, 2021
Messages
802
I was going to use an integer field as well but didn't know if there was a best practice kind of situation when you are developing with an eye towards migrating to sql server in the future.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:20
Joined
Oct 29, 2018
Messages
21,357
Hi. How exactly are you planning to migrate the Access table into SQL Server? If I am not mistaken, wouldn't the SSMA tool automatically convert a Yes/No field from Access into a BIT field in SQL Server? Just thinking out loud...
 

JMongi

Active member
Local time
Today, 06:20
Joined
Jan 6, 2021
Messages
802
Didn't know that...LOL! But, based on Allen Browne's article, I don't think I need to mess with them.
 

Minty

AWF VIP
Local time
Today, 10:20
Joined
Jul 26, 2013
Messages
10,353
Hi. How exactly are you planning to migrate the Access table into SQL Server? If I am not mistaken, wouldn't the SSMA tool automatically convert a Yes/No field from Access into a BIT field in SQL Server? Just thinking out loud...

@theDBguy It does indeed, but it allows nulls and that breaks it in the front end.
 

Isaac

Lifelong Learner
Local time
Today, 03:20
Joined
Mar 14, 2017
Messages
8,738
I was going to use an integer field as well but didn't know if there was a best practice kind of situation when you are developing with an eye towards migrating to sql server in the future.
there is - don't use creative and weird data types, stick to the basics.
 

Users who are viewing this thread

Top Bottom