UNION OF TWO FIELDS

[gneff]

I am trying to write a query that works like this

var1 = y/n
var2 = y/n
var3 = y/n

ItemNo

So what I want to do is this

IIF(var2=-1,[ITEMNO] AND VAR1=-1,[ITEMNO]

What I have is several Item no all the same with a diffrent change order number and 3 variables y/n to mark them, I cannot figureout how todo the AND ,I think this is my problem.

 

[shannonp]

Your Iif Statement looks a little peculiar...not sure what you're trying to do but if you could clarify I'm sure someone here will be able to answer your question

Anyhow, an if statement has 3 main components:

1. Criteria
2. True Action
3. False Action
IIF(Criteria,"True Action","False Action")

IIF(var2=-1,[ITEMNO] AND VAR1=-1,[ITEMNO])
The 'AND' operator cannot be used in the context you have written it, because its function is to join expressions, maybe you intended to try and nest a few iif statements...

IIF(var2=-1,IIF(var1=-1,[ITEMNO],"False"),"False")

The nested iif statement (above) re-written in plain english:
If var2 equals -1 AND var1 equals -1 then return the [ITEMNO] value. Otherwise return "False"

An alternative way to handle this without using nested iif's (assuming your fields are of type Yes/No):
IIF(var2 + var1 = -2,[ITEMNO],"False")

Hope this makes at least some sense...

 

[gneff]

I am having a hard time explaining the problem so here goes:
I have an [ITEMNO] which can be copied forward in the table and I use a [CONO] to mark the change and also I added a yes/no variable which can occur three times or in otherwords I have three possible yes/no, I can IIf it just fine but I am trying to get the query to go back to the last time the [ITEMNO] is listed with a diffrent [CONO]. I added the variable yes/no to make the lookup easier ,It works fine for inputing the data but I need to look up the last [ITEMNO] so I can deduct the sum of that Item from the revised Item. So I was thinking I need to not only find the [ITEMNO] and then the var yes/no. I have marked all the var with diffrent names. [revision] ,[revision2],revision3] but I cannot figure out the way to make the query know this. Any and all help is very much appreciated.