Solved Multiple options on one field

[Pat Hartman]

Try this:

Dim db As DAO.Database
Set db = CurrentDb()
db.Containers.Refresh

 

[KitaYama]

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.

 

[theDBguy]

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...

Bitwise Operation in an Access Query

Let me preface this article by saying this is not a topic I am really familiar with. I only decided to write it to share an experience I had...

thedbguy.blogspot.com

 

[KitaYama]

Have you seen this? Hope it helps...

Bitwise Operation in an Access Query

Let me preface this article by saying this is not a topic I am really familiar with. I only decided to write it to share an experience I had...

thedbguy.blogspot.com

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 :

ID	Flag1	Flag2	Flag3	Flag8	Flag9	Flag11	Flag17	Flag18
135	True	False	True	True	True	False	False	False

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:

Advantages and disadvantages of using bit masks in database

Not so long ago I talked to my colleague and he was definitely against using bit masks because it is hard to understand all the values that are stored in the database. In my opinion it is not alway...

softwareengineering.stackexchange.com

Edit : My apologies to OP for going off topic.

 

[theDBguy]

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]

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]

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]

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.

 

[theDBguy]

Sorry. Seems I've missed that. Will read it again.

Let me know what you think.

 

[KitaYama]

@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]

@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]

@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]

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:-

    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]

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:-

    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.