Query Help Please

[nancybrown]

In my query, I am using a table called 2008 Vendors, and 2009 Vendors; each has a payment term field.

I've added an extra column called "Updated Terms." Can someone tell me how to write a query that would the 2008 against the 2009 fields and to return one of the 3 results (I'd like to avoid SQL if possible):

If 2008 payment terms = 2009 payment terms "No Change"
If 2008 payment terms < 2009 payment terms "Updated"
If 2008 payment terms > 2009 payment terms "blank"

Thank you in advance.

 

[Will_B]

In your query, add a new column and paste this where the field name would go:

My_Results: IIF([2008 payment terms] = [2009 payment terms], "No Change",IIF([2008 payment terms] < [2009 payment terms],"Update","blank"))

This formula uses the "immediate if statement" (IIF), nesting one within the other. The translation is: Criteria, do this if true, do this if false. In the formula I gave you, the "do this if false" is to perform another IIF test.

"blank" is the result if the = and < tests fail; it doesn't have an explicit > test.

 

[nancybrown]

Hi, thank you, I tried it and it gave me a dialog box to enter data, naturally, I didn't enter any so it returned "" values.

 

[james_lankford]

are the names of your columns
[2008 payment terms]
and
[2009 payment terms]
?

 

[nancybrown]

Sorry it took so long to respond, yes, one field is called 2008 Payment Terms, and the other is called 2009 Payment Terms.

 

[Marbles]

How have you linked the tables?

 

[nancybrown]

Primary Key: Vendor IDs

 

[Marbles]

What exactly does the dialog box ask?

 

[alansidman]

Also, show us your SQL. This will help to determine the issue.