?
Solved

using logical operators in MS Acess query

Posted on 2010-09-18
15
Medium Priority
?
698 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +3
15 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 750 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 48

Expert Comment

by:Dale Fye
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

764 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