populating a form recordset based on bitwise flags

[Guest]

I have a Users table, and the users can belong to one or more Groups. I made
an integer field called Group and assigned a bit to each group they can join
or leave. Maybe this design is not good, it seemed like a good idea at the
time...

So, I have a Users form where the operator wants to select which group's
users they're seeing. I gave it a Group combo, then tried to filter the form
based on it like so:
dim tval as int
tVal = Not GroupCombo.Value
Forms("Usersandgroups").Filter = "[user_groups] = [user_groups] | " & tVal
This works logically but not syntactically.

Is there a way to do this? I'm open to a completely different approach if
necessary, as long as it works for users belonging to one or multiple groups.

What I really wanted to do is Filter = [user_groups] & GroupCombo.Value =
GroupCombo.Value, but that syntax is even less correct (the left side is not
a valid field).

Thanks,

Will

 

[Marshall Barton]

I have a Users table, and the users can belong to one or more Groups. I made
an integer field called Group and assigned a bit to each group they can join
or leave. Maybe this design is not good, it seemed like a good idea at the
time...

So, I have a Users form where the operator wants to select which group's
users they're seeing. I gave it a Group combo, then tried to filter the form
based on it like so:
dim tval as int
tVal = Not GroupCombo.Value
Forms("Usersandgroups").Filter = "[user_groups] = [user_groups] | " & tVal
This works logically but not syntactically.

Is there a way to do this? I'm open to a completely different approach if
necessary, as long as it works for users belonging to one or multiple groups.

What I really wanted to do is Filter = [user_groups] & GroupCombo.Value =
GroupCombo.Value, but that syntax is even less correct (the left side is not
a valid field).

You use of & and | are more C like than VBA, so it's no
surprise that you're getting syntax errors. In VBA code the
And, Or, Not, IMP, etc operators are bitwise operators.

AFAIK, through A2002, Jet does not have any bitwise
operators. I have not tried it, but I believe A2003 has
introduced new operators (e.g. BAnd) that will do what you
want. In previous versions, you need to create user defined
functions to do the operations in VBA.

 

[Guest]

Thanks, these are linked SQl tables, and I forget that I'm talking to Jet. I
can do it in a pass-through query, like so: SELECT * FROM Users
WHERE (user_groups & groupBit) = 1; To do this I'd need to be able to
change the WHERE criteria of the pass-through query in VBA code when the user
selects a group in the combo to test the bit for the particular group. I
haven't done this before, can I do it with a query member variable (how?), or
can I use a stored procedure with a parameter as a recordsource for the query
(I know, queries don't have recordsources)?

Also thanks for pointing out the logical & operator is And in VBA, my bad.

I have a Users table, and the users can belong to one or more Groups. I made
an integer field called Group and assigned a bit to each group they can join
or leave. Maybe this design is not good, it seemed like a good idea at the
time...

So, I have a Users form where the operator wants to select which group's
users they're seeing. I gave it a Group combo, then tried to filter the form
based on it like so:
dim tval as int
tVal = Not GroupCombo.Value
Forms("Usersandgroups").Filter = "[user_groups] = [user_groups] | " & tVal
This works logically but not syntactically.

Is there a way to do this? I'm open to a completely different approach if
necessary, as long as it works for users belonging to one or multiple groups.

What I really wanted to do is Filter = [user_groups] & GroupCombo.Value =
GroupCombo.Value, but that syntax is even less correct (the left side is not
a valid field).

You use of & and | are more C like than VBA, so it's no
surprise that you're getting syntax errors. In VBA code the
And, Or, Not, IMP, etc operators are bitwise operators.

AFAIK, through A2002, Jet does not have any bitwise
operators. I have not tried it, but I believe A2003 has
introduced new operators (e.g. BAnd) that will do what you
want. In previous versions, you need to create user defined
functions to do the operations in VBA.

 

[Marshall Barton]

Sorry, Will, you just went over my head. I have no
experience using SQL Server.

I suggest that you update your question to include more
information about your environment and post a new question.
(The queries newsgroup be more appropriate than
formscoding.)
--
Marsh
MVP [MS Access]

Thanks, these are linked SQl tables, and I forget that I'm talking to Jet. I
can do it in a pass-through query, like so: SELECT * FROM Users
WHERE (user_groups & groupBit) = 1; To do this I'd need to be able to
change the WHERE criteria of the pass-through query in VBA code when the user
selects a group in the combo to test the bit for the particular group. I
haven't done this before, can I do it with a query member variable (how?), or
can I use a stored procedure with a parameter as a recordsource for the query
(I know, queries don't have recordsources)?

Also thanks for pointing out the logical & operator is And in VBA, my bad.

I have a Users table, and the users can belong to one or more Groups. I made
an integer field called Group and assigned a bit to each group they can join
or leave. Maybe this design is not good, it seemed like a good idea at the
time...

So, I have a Users form where the operator wants to select which group's
users they're seeing. I gave it a Group combo, then tried to filter the form
based on it like so:
dim tval as int
tVal = Not GroupCombo.Value
Forms("Usersandgroups").Filter = "[user_groups] = [user_groups] | " & tVal
This works logically but not syntactically.

Is there a way to do this? I'm open to a completely different approach if
necessary, as long as it works for users belonging to one or multiple groups.

What I really wanted to do is Filter = [user_groups] & GroupCombo.Value =
GroupCombo.Value, but that syntax is even less correct (the left side is not
a valid field).

You use of & and | are more C like than VBA, so it's no
surprise that you're getting syntax errors. In VBA code the
And, Or, Not, IMP, etc operators are bitwise operators.

AFAIK, through A2002, Jet does not have any bitwise
operators. I have not tried it, but I believe A2003 has
introduced new operators (e.g. BAnd) that will do what you
want. In previous versions, you need to create user defined
functions to do the operations in VBA.