Solved

using logical operators in MS Acess query

Posted on 2010-09-18
15
656 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now