Avatar of Declan Basile
Declan Basile
Flag for United States of America asked on

DataType for bit Comparison

I need to store a string of 16 bits in two different tables and do bit operations on the values.  For example, a field in one table might be the value "0101010101010101" and a field in the other table might be "0001100111100010".  In VBA, I'll need to XNOR them together, get the result, and "OR" the result with another string of 16 bits.  What's the best data type to use to store these values?
Microsoft SQL ServerVBAMicrosoft Access

Avatar of undefined
Last Comment
crystal (strive4peace) - Microsoft MVP, Access

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Anders Ebro (Microsoft MVP)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Jim Horn

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
crystal (strive4peace) - Microsoft MVP, Access

agree with Anders ... data type will not have to be converted to do bitwise math plus Integer takes only 2 bytes (16 bits - just what is needed) to store as opposed to 1+ byte/character for string and then string would have to be cast to a number for bit-math
Jim Horn

An int might not work, as inserting 0001100111100010 into an int will strip the leading zeros and only save 1100111100010 (without the first three zeros).  

Unless there's some kind of conversion function to add the leading 0's back if that is necessary expressions.   If it's not necessary for expressions then int works.

And yes saving it as 4 bits is always better then saving it as 17 bits.
crystal (strive4peace) - Microsoft MVP, Access

adding on

bit <> byte

bit = 0 or 1
byte is a "word" or set number of bits -- for example, 8 bits such as 10001010
 
1 byte can often be equated to 8 bits, but that is not always true; it depends on the environment. Byte can be more bits.
~~~
getting back to original question:

since math will be used on data, best to store as a number. Dropping initial zeros for displaying has no impact on numbers that are stored.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Gustav Brock

You answered your own question:

I need to store a string of 16 bits

So text of length 16.

/gustav
Declan Basile

ASKER
Is there any way that I could change this SQL Server code to be a loop?

ALTER TRIGGER [dbo].[trigUpdateBitValues] 
ON [dbo].[CodeTrackingReqs] FOR INSERT, UPDATE
AS

	DECLARE @CumulativeSame As Integer
	DECLARE @CumulativeDontCare As Integer
	DECLARE @CodeTrackingReqId As Integer
	DECLARE @Attrib As TinyInt

	SET @CodeTrackingReqId = (SELECT Inserted.CodeTrackingReqId FROM INSERTED)
	SET @CumulativeSame = 0
	SET @CumulativeDontCare = 0

	SET @Attrib = (SELECT Inserted.Attrib1 FROM INSERTED)
	If @Attrib = 1
		SET @CumulativeSame = @CumulativeSame + POWER(2,0)
	If @Attrib = 2
		SET @CumulativeDontCare = @CumulativeDontCare + POWER(2,0)
	
	SET @Attrib = (SELECT Inserted.Attrib2 FROM INSERTED)
	If @Attrib = 1
		SET @CumulativeSame = @CumulativeSame + POWER(2,1)
	If @Attrib = 2
		SET @CumulativeDontCare = @CumulativeDontCare + POWER(2,1)

	SET @Attrib = (SELECT Inserted.Attrib3 FROM INSERTED)
	If @Attrib = 1
		SET @CumulativeSame = @CumulativeSame + POWER(2,2)
	If @Attrib = 2
		SET @CumulativeDontCare = @CumulativeDontCare + POWER(2,2)

	SET @Attrib = (SELECT Inserted.Attrib4 FROM INSERTED)
	If @Attrib = 1
		SET @CumulativeSame = @CumulativeSame + POWER(2,3)
	If @Attrib = 2
		SET @CumulativeDontCare = @CumulativeDontCare + POWER(2,3)

	UPDATE CodeTrackingReqs SET SameMask = @CumulativeSame, DontCareMask = @CumulativeDontCare
		 WHERE CodeTrackingReqId = @CodeTrackingReqId

Open in new window

Jim Horn

>Is there any way that I could change this SQL Server code to be a loop?
I recommend asking that as a new EE question, and responding to the experts that spent time and effort responding to your original question.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
crystal (strive4peace) - Microsoft MVP, Access

> "So text of length 16"

not necessarily. Integer stores 16 bits in Access -- should be good plus it is already a number data type.
Declan Basile

ASKER
Thank you everyone for your feedback.  I think integer will work best because I need to do bit operations but don't need to graphically represent the number in binary.
crystal (strive4peace) - Microsoft MVP, Access

you're welcome ~ happy to help
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy