Solved Multiple options on one field (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:21
Joined
Feb 19, 2002
Messages
36,868
Try this:
Code:
Dim db As DAO.Database
Set db = CurrentDb()
db.Containers.Refresh
 

KitaYama

Active member
Local time
Tomorrow, 00:21
Joined
Jan 6, 2022
Messages
537
If you never have more than 8 flags, you could use bit wise logic, and store these flags in a single byte.
1- If you use integer data type, you can use much more flags.
for example 4478 in binary is 1000101111110. there's 13 flags.

2- Unfortunately I couldn't find a way to use bitwise in a query.
Another time to avoid bit flags is when you are storing the information in a database. Most database back-ends do not support bitwise operations in SQL statements, so you can’t test individual flag settings to filter your result set. Plus, ODBC (version 3 anyway) doesn’t support any scalar functions that provide this capability, regardless of what the back end can do.

Imagine you have a table with more than 10 thousands records. Now you want to view a list of records that bit 3,5,6,7,8 are true.
Do you have any solution?
I had to use a function that made the query so slow.


Edit : @theDBguy 's solution seems we have a way to use bitmasks in a query.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 08:21
Joined
Oct 29, 2018
Messages
19,164
1- If you use integer data type, you can use much more flags.
for example 4478 in binary is 1000101111110. there's 13 flags.

2- Unfortunately I couldn't find a way to use bitwise in a query.
Another time to avoid bit flags is when you are storing the information in a database. Most database back-ends do not support bitwise operations in SQL statements, so you can’t test individual flag settings to filter your result set. Plus, ODBC (version 3 anyway) doesn’t support any scalar functions that provide this capability, regardless of what the back end can do.

Imagine you have a table with more than 10 thousands records. Now you want to view a list of records that bit 3,5,6,7,8 are true.
Do you have any solution?
I had to use a function that made the query so slow.
Have you seen this? Hope it helps...

 

KitaYama

Active member
Local time
Tomorrow, 00:21
Joined
Jan 6, 2022
Messages
537
Have you seen this? Hope it helps...

Not this one, but similar ones.
As your blog shows, SQL statement doesn't support bitwise. You have to write your own function that not only brings down the speed, but it makes the code too complicated.
Back to my example, imagine you have a field that covers 20 flags and you need a query to show a list of records to show the state of 1,2,3,8,9,11,17,18 bits. You have to add 8 fields to your query and in each of them call your function to return a value for that field to receive something like :

IDFlag1Flag2Flag3Flag8Flag9Flag11Flag17Flag18
135TrueFalseTrueTrueTrueFalseFalseFalse

And imagine the sql runs on a table with several hundred thousands of records.
If you have a better way, I'm all ears. Because I really need it.

I know you're an expert and a very good one and also you don't need to hear it from a newbie, but just take a look at this page for fun:

Edit : My apologies to OP for going off topic.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:21
Joined
Oct 29, 2018
Messages
19,164
As your blog shows, SQL statement doesn't support bitwise.
Sounds like you didn't read all the way to the end of the article where I showed how to do bitwise operation in a query (SQL) without using a VBA function.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:21
Joined
Oct 29, 2018
Messages
19,164
You have to write your own function that not only brings down the speed, but it makes the code too complicated.
Again, as my blog article shows, this is not really necessary. But, it's still an option.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:21
Joined
Oct 29, 2018
Messages
19,164
If you have a better way, I'm all ears. Because I really need it.
Are you saying you don't want to create a separate column for each bit to check? If so, what would you prefer the query result to look like?
 

KitaYama

Active member
Local time
Tomorrow, 00:21
Joined
Jan 6, 2022
Messages
537
Sounds like you didn't read all the way to the end of the article where I showed how to do bitwise operation in a query (SQL) without using a VBA function.
Sorry. Seems I've missed that. Will read it again.
 

KitaYama

Active member
Local time
Tomorrow, 00:21
Joined
Jan 6, 2022
Messages
537
@theDBguy
My apologies for missing the last part.
I did some tests on a 50,000 recordset and so far, it seems that's absolutely perfect.
But before implementing it into our database, I need to run some more tests. If I run into some issues, I'll open a new thread to ask for your advice.
Thanks again.

@gemma-the-husky
Seems that your suggestion works. I'm really sorry for judging too soon.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 16:21
Joined
Sep 12, 2006
Messages
14,901
@theDBguy
My apologies for missing the last part.
I did some tests on a 50,000 recordset and so far, it seems that's absolutely perfect.
But before implementing it into our database, I need to run some more tests. If I run into some issues, I'll open a new thread to ask for your advice.
Thanks again.

@gemma-the-husky
Seems that your suggestion works. I'm really sorry for judging too soon.

I didn't realise you HAD judged too soon. I noted you mentioned using 16 bits in a 16 bit value. I imagine you could also go to a 32bit long. I have generally been a little reluctant to do that, for fear of not selecting the correct one of the 2 or 4 8-bit bytes. With a simple 8-bit byte you don't have that problem. Anyway, I am pleased you got it working.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:21
Joined
Oct 29, 2018
Messages
19,164
@theDBguy
My apologies for missing the last part.
I did some tests on a 50,000 recordset and so far, it seems that's absolutely perfect.
But before implementing it into our database, I need to run some more tests. If I run into some issues, I'll open a new thread to ask for your advice.
Thanks again.

@gemma-the-husky
Seems that your suggestion works. I'm really sorry for judging too soon.
Hi. Good luck. I hope you could share some screenshots of your tests to help me understand what you're trying to do. I am still trying to figure out what you meant by (as I read it) "not wanting to create a separate column for each bit to check." I'd like to see what you meant by that. Cheers!
 

debsamguru

Member
Local time
Today, 16:21
Joined
Oct 24, 2010
Messages
75
Thank you all, I finally managed to get it working and it was my own silly fault that the querydef wasn't working! In the calling form, I called the OpenForm as follows:-
Code:
    DoCmd.OpenForm "OrdersSelectionF"
    
    Me.SupplierParameter.Value = ""
    Me.SupplierListCombo = ""
    Me.AllSuppliersCheck = 0
    Me.CustomerParameter.Value = ""
    Me.CustomerListCombo = ""
    Me.AllCustomersCheck = 0
    Me.ActiveYN = 0
    Me.PartShippedYN = 0
    Me.ShippedYN = 0
    Me.InvoicedYN = 0
    Me.PaidYN = 0

I was clearing out the parameters to the form straight after so as to display the form ready for a new query not realising that this would clear the parameters to the query that the OrdersSelectionF used! Doh indeed!

Once I removed these (and put them into the Close Form sub of the OrdersSelectionF form, it all worked!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:21
Joined
Oct 29, 2018
Messages
19,164
Thank you all, I finally managed to get it working and it was my own silly fault that the querydef wasn't working! In the calling form, I called the OpenForm as follows:-
Code:
    DoCmd.OpenForm "OrdersSelectionF"
    
    Me.SupplierParameter.Value = ""
    Me.SupplierListCombo = ""
    Me.AllSuppliersCheck = 0
    Me.CustomerParameter.Value = ""
    Me.CustomerListCombo = ""
    Me.AllCustomersCheck = 0
    Me.ActiveYN = 0
    Me.PartShippedYN = 0
    Me.ShippedYN = 0
    Me.InvoicedYN = 0
    Me.PaidYN = 0

I was clearing out the parameters to the form straight after so as to display the form ready for a new query not realising that this would clear the parameters to the query that the OrdersSelectionF used! Doh indeed!

Once I removed these (and put them into the Close Form sub of the OrdersSelectionF form, it all worked!
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom