Solved Multiple options on one field

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