• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 727
  • Last Modified:

using logical operators in MS Acess query

I am using a single variable (UtilityCount) to check for utility suppliers.
e.g Elec (1), Gas (2), Water (3), Phone (4), Local Council (5) using 2 raised to the power 0 to 4
e.g Elec and Gas supplier has a value 2 + 1 = 3
Phone 4 etc.

I want to extract this value in a query using AND operator but it is not recognised.
E.g iif ((UtilityCount AND 16),-1,0) but it is not recognised.
0
SeanDoherty49
Asked:
SeanDoherty49
  • 6
  • 3
  • 3
  • +3
1 Solution
 
rockiroadsCommented:
access dont support bitwise type operations as far as I  know.
you might need to create a vba function which will do the working out for you, maybe use something like mod.
the vba function can be used in a query
0
 
BitsqueezerCommented:
Hi,

maybe this thread is of help for you:

http://p2p.wrox.com/sql-language/1815-bitwise-access.html

Cheers,

Christian
0
 
rockiroadsCommented:
You have about 5 options, only way I can see you doing this like I said is to create vba functions to check each type and use it in a query

Like this (has to go in a module)



Public Function HasGas(ByVal x As Integer) As Boolean
'logic goes here

'this is not right code just example
    If x Mod 2 = 1 Then HasGas = True Else HasGas = False
End Function


Now in your query you can do this

SELECT HasGas(UtilityCount) AS TakesGas



It seems you already know about the bitwise operators. As I said not supported in queries but you can do it in vba  http://msdn.microsoft.com/en-us/library/wz3k228a%28VS.80%29.aspx


Alternatively you can have one function that builds a string of what is used and get the function to return a string
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
rockiroadsCommented:
fyi - found another way to check bitwise using mod and power

http://support.microsoft.com/kb/194206

0
 
SeanDoherty49Author Commented:
Looks like I had a blind spot there.

Thanks for your help
0
 
Dale FyeCommented:
Well, that is one way to do it, but in a relational database, you create another table that contains the Primary Key (Assuming you have one) from your main table, and a UtilityID field.

Lets say your main table has fields (tbl_Addresses)
AddressID - autonumber
AddStreetName - Text
AddStreetNum - long
AddAptNum - text

And you also have a table of utilities (tbl_Utilities)
UtilityID - autonumber
Utility - Text

Then you would create a third table tblAddressUtilities which contains
AddressID - Long
UtilityID - Long

Then you would use a subform based upon this third table in your address form, linked by AddressID
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The Xor Operator performs bitwise operations ... From Help:

"The Xor operator performs as both a logical and bitwise operator. A bit-wise comparison of two expressions using exclusive-or logic to form the result, as shown in the following table:"

"bitwise comparison
A bit-by-bit comparison between identically positioned bits in two numeric expressions."

If bit in expression1 is              And bit in expression2 is              Then result is
0                                              0                                                     0
0                                              1                                                     1
1                                              0                                                     1
1                                              1                                                     0
Look up Xor in VBA help for full details.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
More:

Xor Operator Example
This example uses the Xor operator to perform logical exclusion on two expressions.

Dim A, B, C, D, MyCheck
A = 10: B = 8: C = 6: D = Null    ' Initialize variables.
MyCheck = A > B Xor B > C    ' Returns False.
MyCheck = B > A Xor B > C    ' Returns True.
MyCheck = B > A Xor C > B    ' Returns False.
MyCheck = B > D Xor A > B    ' Returns Null.
MyCheck = A Xor B    ' Returns 2 (bitwise comparison).

Now, you say you are doing this in a Query?  If so, queries do not recognize 'variables' directly.  Also, I'm not quite following your logic re:

e.g Elec and Gas supplier has a value 2 + 1 = 3
Phone 4 etc.

?

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You can also lookup all of the logical operators:

Logical Operators
And Operator

Eqv Operator

Imp Operator

Not Operator

Or Operator

Xor Operator


mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Rocki:

"It seems you already know about the bitwise operators. "
Well, actually they are.  For example this:

SELECT [A] Xor [B] AS MyXOR, [A] And [B] AS MyAND
FROM Table8;


Returns this:
MyXOR      MyAND
0               0
-1               0
-1             0
0             -1

mx
0
 
hnasrCommented:
List few records of input, and the expected output of the query.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
btw ... the simple table I used for the XOR and AND example was:
Table8 - four records

A      B
0      0
0      1
1      0
1      1

mx
0
 
SeanDoherty49Author Commented:
It pointed me in the right direction, without actually providing the solution.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
SeanDoherty49
Did you see any of my posts?  Bitwise operations are supported.

mx
0
 
SeanDoherty49Author Commented:
I did not want to have five if then statements and I was trying to do it in a query.

I have now set up a public function that does the Logical AND and then use this function in the query.

I thought when I posted my response that this was the only reply.

Thanks for your help

Cheers
John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 6
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now