By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

MS Access SQL (Not Equal To)

P: 21
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)

Expand|Select|Wrap|Line Numbers
  1. IIf([CM52Parts].[Internal Part Number]=[Details].[Internal Part Number] 
  2. And [CM52Parts].[Revision] Is Null Or Not [Details].[Revision],"Revision Change.",
  3. IIf([CM52Parts].[Internal Part Number]=[Details].[Internal Part Number] 
  4. And [CM52Parts].[Revision]=[Details].[Revision]," In Database.","Not In Database.")) 
  5. 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
Mar 16 '12 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 634

I think I might try it this way
Expand|Select|Wrap|Line Numbers
  1. IIf([CM52Parts].[Internal Part Number]<>[Details].[Internal Part Number],"Not In Database.",
  2. IIf([CM52Parts].[Revision]=[Details].[Revision],"In Database","Revision Change."))

Mar 16 '12 #2

P: 21
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
Mar 16 '12 #3

Expert Mod 15k+
P: 31,398
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.
Mar 16 '12 #4

Expert Mod 15k+
P: 31,398
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.
Mar 16 '12 #5

P: 759
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.
Mar 17 '12 #6

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. IIf([CM52Parts].[Internal Part Number]=[Details].[Internal Part Number], 
  2.      IIf(Nz([CM52Parts].[Revision])=Nz([Details].[Revision]),"In Database","Revision Change"), 
  3.     "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.

Mar 17 '12 #7

P: 51
' pseudo code: let
Expand|Select|Wrap|Line Numbers
  1. cm52iP = [CM52PARTS].[[INTERNAL PARTS]
  2. cm52Rv = [CM52PARTS].[REVISION]
  4. dtRv = [DETAILS].[REVISION]
  6. If((cm52iP = dtiP) && ((cm52Rv = null) || (cm52Rv <> dtRv))) Then
  7. print "Revision change."
  9. Else if((cm52iP = dtiP) && (cm52Rv = dtRv)) Then
  10. print "In database."
  12. else
  13. print "Not in database."
  15. End if
' i hope it helps.. =)
Mar 17 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.