Solved

using logical operators in MS Acess query

Posted on 2010-09-18
15
682 Views
Last Modified: 2012-05-10
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
Comment
Question by:SeanDoherty49
  • 6
  • 3
  • 3
  • +3
15 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 250 total points
ID: 33707923
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
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 33708104
Hi,

maybe this thread is of help for you:

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

Cheers,

Christian
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33708196
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 65

Expert Comment

by:rockiroads
ID: 33708231
fyi - found another way to check bitwise using mod and power

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

0
 
LVL 1

Author Comment

by:SeanDoherty49
ID: 33708803
Looks like I had a blind spot there.

Thanks for your help
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33708831
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
 
LVL 75
ID: 33708923
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
 
LVL 75
ID: 33708938
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
 
LVL 75
ID: 33708940
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
 
LVL 75
ID: 33708970
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
 
LVL 30

Expert Comment

by:hnasr
ID: 33710771
List few records of input, and the expected output of the query.
0
 
LVL 75
ID: 33712065
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
 
LVL 1

Author Closing Comment

by:SeanDoherty49
ID: 33714768
It pointed me in the right direction, without actually providing the solution.
0
 
LVL 75
ID: 33718363
SeanDoherty49
Did you see any of my posts?  Bitwise operations are supported.

mx
0
 
LVL 1

Author Comment

by:SeanDoherty49
ID: 33720986
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

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question