Logical Operator "Xor"

[lmcc007]

I have a table of Addresses for various companies. Some of the companies will have more than one address. For this report I only need one address, which is TypeofAddress ID = 1. Therefore, I created a query and use this criteria on the TypeofAddressID field: 1 Xor 2 Xor 3 Xor 7 Xor. The results give me 1 and 7 for a company. Meaning, if a company has a type 1 and type 7, I get both addresses instead of just one address. For this report "1" is the preferred type of address, but if I just have a mailing address then 7 is okay.

Any quick suggestions on what I am doing wrong?

Thanks!

 

[gemma-the-husky]

i think you want AND, not XOR.

test is probably

mynumber AND 1 = 1

 

[HiTechCoach]

I have a table of Addresses for various companies. Some of the companies will have more than one address. For this report I only need one address, which is TypeofAddress ID = 1. Therefore, I created a query and use this criteria on the TypeofAddressID field: 1 Xor 2 Xor 3 Xor 7 Xor. The results give me 1 and 7 for a company. Meaning, if a company has a type 1 and type 7, I get both addresses instead of just one address. For this report "1" is the preferred type of address, but if I just have a mailing address then 7 is okay.

Any quick suggestions on what I am doing wrong?

Thanks!

I would recommend using a sub query to return the desired record.

 

[lmcc007]

Okay,

I am confused with sub query. But the way Xor reads it suppose to give me one or the other--that is, a 1 or a 7 but not both.

 

[Brianwarnock]

This question seems incomplete. What if there is only an ID 2 or a 2 and 3, or are you saying that there will always be a 7 and maybe one of the others?
Are these in separate records or fields in the one record? What's the layout.


Brian

 

[lmcc007]

This question seems incomplete. What if there is only an ID 2 or a 2 and 3, or are you saying that there will always be a 7 and maybe one of the others?
Are these in separate records or fields in the one record? What's the layout.


Brian

You're right Brian, there are types 1 through 16.

These are separate records.

Layout:

AddressID.....CompanyID.....TypeofAddressID.....AddressInfo
1.................3..................1.........................100 Jefferson Street
2.................3.................. 7.........................PO Box 100
3................16..................1.........................2000 TCB Tower
4................16..................7.........................Box 2TCB
​

I think I got it to work. I enter this as the criterial on the TypeofAddressID field:

IIf([TypeofAddressID]=1,[TypeofAddressID],[TypeofAddressID]=2 Xor ([Address].[TypeofAddressID])=3 Xor ([Address].[TypeofAddressID])=7
​

I know there is a better or probably an easier way, but this is all I can come up with.

 

[Brianwarnock]

That looks as though it would give a syntax error, and I don't see how that approach avoids returning multiple records per company.

I would have thought that you need 3 queries.
You agreed that every Company has a type=7 address.
qryType7 selects all of those records
qrytype1 selects all of the type1 records. There is not 1 per company.
qryFinal joins qrytype7 and qrytype1 on Company selecting allqrytype7 ie a leftjoin say.
to get the correct address
Addr: IIf(IsNull([qrytype1].[comp]),[qrytype7].[address],[qrytype1].[address])


Brian

 

[lmcc007]

That looks as though it would give a syntax error, and I don't see how that approach avoids returning multiple records per company.

I would have thought that you need 3 queries.
You agreed that every Company has a type=7 address.
qryType7 selects all of those records
qrytype1 selects all of the type1 records. There is not 1 per company.
qryFinal joins qrytype7 and qrytype1 on Company selecting allqrytype7 ie a leftjoin say.
to get the correct address
Addr: IIf(IsNull([qrytype1].[comp]),[qrytype7].[address],[qrytype1].[address])


Brian

No, every company does not have a type=7 address. Each company may have a type ranging from 1-16 or it may not have a address listed at all.

 

[Brianwarnock]

OK I give up as I don't know what you have got or what you want, nor how what you posted extracts only 1 record per company from more than one record in the table.

Brian

 

[gemma-the-husky]

I am confused about exactly what you are trying to do here

Do you not need to do something like this

have a combo box to select the address type
then update your addess query to use the selected address type.

no boolean logic required here at all, I dont think.

 

[The_Doc_Man]

Doing an XOR on a numeric field allows you to do bitwise XOR, which means that

1 XOR 7 equals 6.

Is that what you wanted?

XOR is not a BOOLEAN operator. It is more general. It works right on numbers only if you want to do bitwise math.

If A, B, and C are variables of data type BOOLEAN, the following statements have the same effect

A = ( B <> C )

A = B XOR C

This is because the operators are defined for integer data classes. BOOLEAN and INTEGER and LONG are all integer data classes, so their operators intertwine unexpectedly. If A, B, and C are all LONG integers, these statements are also legal

A = ( B <> C ) 'A will be cast as either TRUE or FALSE, where FALSE = 0

A = ( B XOR C ) 'A will be a bit mask with a 1 in every bit-column where the bits in B don't match the bits in C.

It's an unfortunate side effect of sub-typing or sub-classing numbers. Happens often in C, BASIC, FORTRAN; less likely in ADA, PASCAL, and PL/1.

 

[HiTechCoach]

Okay,

I am confused with sub query. But the way Xor reads it suppose to give me one or the other--that is, a 1 or a 7 but not both.

I think the confusing is based on a misunderstanding of the use of XOR.

The_Doc_Man as giving you a good explanation of how it works.

I have used XOR to create simple encryption.


Here is a a very simple encrypt/decrypt routine.

Public Function Decrypt(strIn As String) As String
'  The key is 5656
'  you can change that to anything you like
'

Dim strChr As String
Dim i As Integer
Dim j As Integer

j = 1

For i = 1 To Len(strIn) / 4
strChr = strChr & Chr(CLng(Mid(strIn, j, 4)) Xor 5656)
j = j + 4
Next i
Decrypt = strChr
End Function

Public Function Encrypt(strIn As String) As String
Dim strChr As String
Dim i As Integer

For i = 1 To Len(strIn)
strChr = strChr & CStr(Asc(Mid(strIn, i, 1)) Xor 5656)
Next i
Encrypt = strChr
End Function

It appears that we are all confused on your criteria for selecting an Address type. At first read, it appeared that your address types were in numerical order by desired use. The reason I suggested using a sub query was that if you were to sort the address types in ascending order the top record work be the one you wanted.

I think it would really help if you were to explain how the the 16 address types related to each other. How do you you select the address type to use?

Note: Please explain is words not using VBA or programming terminology.

 

[lmcc007]

All I am doing is saying that if a Business address (Type = 1) is listed, put it. Or, if a mailing address (Type = 7) is given instead, put it. If there is no Business (Type 1) address, list the type there but not both. Prefer the Business Address, but if I only have a PO Box address, use it.

 

[HiTechCoach]

All I am doing is saying that if a Business address (Type = 1) is listed, put it. Or, if a mailing address (Type = 7) is given instead, put it. If there is no Business (Type 1) address, list the type there but not both. Prefer the Business Address, but if I only have a PO Box address, use it.

What you are saying is that you only what to select one address record from two types out of all sixteen possible types.

based on that, I would use logic like this:

... that if a Business address (Type = 1) is listed, put it. Or, if a mailing address (Type = 7) is given ...

You would then select the address records for a CompanyID with TypeofAddressID type of 1 or 7

.. Where CompanyID = 3 and [TypeofAddressID] =1 or [TypeofAddressID] = 7 ...

If there is no Business (Type 1) address, list the type there but not both. Prefer the Business Address, but if I only have a PO Box address, use it.

then sort the data in ascending order and select the TOP record. By doing this Type 1 will be returned if it exists. If no type 1 then type 7 will be returned if it exists. If neither then no records will be found.

 

[Brianwarnock]

The way I read his posts is that he can have multiple records per company and the addressIds can be any combination of the 16,
The order of choice is 1 , 7 ,a n other presumably tho not stated in ascending order
At one stage he appeared to agree with my question that they all had a 7, but that turned out not to be so.
If the above is correct then he will need several queries, of course if sensible system design made the 7 a 2 then a simple group and min would do it.

Brian