How to display text in a query based on a condition

[exrcizn]

I am creating a query in Access 2003 that basically displays data in certain fields in a table (plain and simple basic stuff). I have a field in the table that may or may not contain data. If the field contains data I want that data to display. If it does not contain data I want the query to check another field for a certain condition. If the condition is true I want to display specific text in this column where normally nothing would be displayed because the field is blank. If the condition is false then displaying blank information is fine.


If this cannot be done the way I describe then I would be ok with creating a new column to display in the query that will show the desired text based on a true condition.

[jaxjagfan]

I am creating a query in Access 2003 that basically displays data in certain fields in a table (plain and simple basic stuff). I have a field in the table that may or may not contain data. If the field contains data I want that data to display. If it does not contain data I want the query to check another field for a certain condition. If the condition is true I want to display specific text in this column where normally nothing would be displayed because the field is blank. If the condition is false then displaying blank information is fine.


If this cannot be done the way I describe then I would be ok with creating a new column to display in the query that will show the desired text based on a true condition.

You need to do an IIF(notice there are 2 i's).

Something similar to this:

Field1:IIF(Mytable.MyField1 is not null, Mytable.MyField1,IIF(Mytable.MyField2 = "sometext", "sometruetext", "somefalsetext"))

[exrcizn]

Ok, I have the IF statement which I'm sure is correct but...where do I put it on the query screen? I know this is a basic question but it's been a while since I've done this. Funny how I had no trouble with the IIF statement but can't figure out this part!

In the Query view window where you set up the columns to show in the output of the query do I put the IIF statement in the criteria section of the field I'm checking (the first mentioned field in your IIF statement example) or do I "create" a separate field...that's where I'm missing the knowledge of how to do this. If I put it in the criteria area of the field I'm checking against I get an error that says this:

You tried to execute a query that does not include the specified expression 'JobPosting.RequestPostingNumber=[Request Posting Number] And Application.Race=IIF(Not Application.Race Is Null....<insert the rest of the IIf statement you gave me here>...as part of an aggregate function.

The first referenced code part of this query is where I'm prompting for a specific Posting Number. This part works.

Basically I need to know where to put the IIf statement...

Thanks for your response!

[exrcizn]

Never mind...I got it!

Thanks so much for your help!

[jaxjagfan]

Never mind...I got it!

Thanks so much for your help!

Guess you realized that the IIF goes in the column name vice criteria.

Glad to help.