Solved

using logical operators in MS Acess query

Posted on 2010-09-18
15
672 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 - finding skipped numbers 11 23
Access 2016 7 33
Passing variables to a function 6 34
How Do I Select a Record using a Primary Key 3 28
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

785 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