MS Access SQL (Not Equal To)

[elak6]

I am trying to write a query that checks whether certain criterias are met in new data that i am importing and based on different conditions which are explained below it then displays a text :

1ST CONDITION: If the Internal Part Number and Revision match that of a pre-existing table in the database then the text displays: In The Database

2ND CONDITION: If the Internal Part Number matches but the Revision does not match that that of the pre-existing table in the database then the text displays : Revision Change

3RD CONDITION: If the Internal Part Number and the Revision does not match that of the pre-existing table in the database then the text displays : Not In The Database

This is the sql that i wrote to help execute this,it works for the first condition but it does not work in the second condition at all
The CM52 Parts is table that i am importing new data into whilst the Details Table already exists (The table that holds all my pre-existing data)

IIf([CM52Parts].[Internal Part Number]=[Details].[Internal Part Number] 
And [CM52Parts].[Revision] Is Null Or Not [Details].[Revision],"Revision Change.",
IIf([CM52Parts].[Internal Part Number]=[Details].[Internal Part Number] 
And [CM52Parts].[Revision]=[Details].[Revision]," In Database.","Not In Database.")) 
AS DisplayText,

For some weird reason for conditions when the internal part number match and the revision does not match it displays Not In Database instead of Revision Change

I have tried a variety of options but none seem to work..
P:S:I am not using a Nz operator because none of my data invloves null values

Any assistance you can offer would be much appreciated

[MikeTheBike]

Hi

I think I might try it this way

IIf([CM52Parts].[Internal Part Number]<>[Details].[Internal Part Number],"Not In Database.",
IIf([CM52Parts].[Revision]=[Details].[Revision],"In Database","Revision Change."))

MTB

[elak6]

Hi Mike,

I just tried it and it isnt working.
For parts that dont have matching internal part number and matching revision, it displays Revision Change instead of Not in database

Thanks for helping me

[NeoPa]

Your tests look totally illogical to me. Why display "Revision Change" in any situation where [Revision] is False? I suspect the results are exactly as would be expected in the circumstances (You share no details of the data so we can only guess).

Also, you have no parentheses to encapsulate your comparison values, so the order of precedence (And is evaluated before Or) may be somewhat different from what you intend. Not as big a problem as comparing the wrong way around, but still worth dealing with to avoid gotchas.

[NeoPa]

Elak6:
P:S:I am not using a Nz operator because none of my data invloves null values

If this is true then why is line #2 testing for Null?

I would expect line #2, as it's written incorrectly as far as I can see - The Or matches both the previous tests and not just the Null check on the same line, to cause the string "Revision Change" to result in cases where it shouldn't rather than the other way around (as you seem to be reporting). I'll leave this with you.

[Mihail]

I agree with NeoPa (#14) about parentheses.

Do you ever heard about Divide et Impera ?
So you can define in your query as many column as you need to calculate partial results then other partial results by used first of columns then... until you can easy (and safely) calculate the final result.

So use the new columns as you use sub procedures in VBA.

More, you can define other queries to do that partial jobs.

Of course that approach will slow down a little bit your program so is your choice.

[Stewart Ross]

I suspect that at least part of your difficulty stems from line 2 of your first post, where you have written

[CM52Parts].[Revision] Is Null Or Not [Details].[Revision]

I think you meant

[CM52Parts].[Revision] Is Null Or [CM52 Parts].[Revision]<>[Details].[Revision]

Anyway, rewriting the nested IIFs and using Nz to guard the Revision field against null values you could try:

IIf([CM52Parts].[Internal Part Number]=[Details].[Internal Part Number], 
     IIf(Nz([CM52Parts].[Revision])=Nz([Details].[Revision]),"In Database","Revision Change"), 
    "Not in Database")

Not sure why you mention that the values are not null when from your own post Revision may well be, but it's no problem to deal with it.

-Stewart

[rekedtechie]

' pseudo code: let

cm52iP = [CM52PARTS].[[INTERNAL PARTS]
cm52Rv = [CM52PARTS].[REVISION]
dtiP = [DETAILS].[INTERNAL PARTS]
dtRv = [DETAILS].[REVISION]
 
If((cm52iP = dtiP) && ((cm52Rv = null) || (cm52Rv <> dtRv))) Then
print "Revision change."
 
Else if((cm52iP = dtiP) && (cm52Rv = dtRv)) Then
print "In database."
 
else
print "Not in database."
 
End if

' i hope it helps.. =)