- Local time
- Today, 07:51
- Joined
- Feb 19, 2002
- Messages
- 43,263
Try this:
Code:
Dim db As DAO.Database
Set db = CurrentDb()
db.Containers.Refresh
Dim db As DAO.Database
Set db = CurrentDb()
db.Containers.Refresh
1- If you use integer data type, you can use much more flags.If you never have more than 8 flags, you could use bit wise logic, and store these flags in a single byte.
Have you seen this? Hope it helps...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.
Not this one, but similar ones.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
ID | Flag1 | Flag2 | Flag3 | Flag8 | Flag9 | Flag11 | Flag17 | Flag18 |
135 | True | False | True | True | True | False | False | False |
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.As your blog shows, SQL statement doesn't support bitwise.
Again, as my blog article shows, this is not really necessary. But, it's still an option.You have to write your own function that not only brings down the speed, but it makes the code too complicated.
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?If you have a better way, I'm all ears. Because I really need it.
Sorry. Seems I've missed that. Will read it again.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.
Let me know what you think.Sorry. Seems I've missed that. Will read it again.
@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!@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.
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
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.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!