Field name into an IIF expression 1

[baguiar]

HI Folks,
I'm trying to give the user the option to select the Year to run a certain report. One of the queries has an "IIF" expression that I'd like to have the field Selected by the user. Here is what I'd like:

Test: IIf(IsNull("[Type Year in here]"),"Owes","ok")

Where "[Type Year in here]" would prompt the user to type in 2007 (for example) which is one of the fields of the table.

Any thoughts?
Thanks a lot,
B

 

[lameid]

Parameters don't have double quotes around them...

[Type Year in here]

Not

"[Type Year in here]

 

[baguiar]

Lameid,

I know they don't have,but I was just putting the quotations to show what parameter I want the user to input.
Instead of having several expressions like:

IIf(IsNull([2005]),"Owes","ok")
IIf(IsNull([2006]),"Owes","ok")
IIf(IsNull([2007]),"Owes","ok").. and so on,

I want the user to be prompt to input the year, and that would go into the parameter..Just like when you use it as a Criteria in a query..

Thanks!

 

[lameid]

Right....

 Test: IIf(IsNull([Type Year in here]),"Owes","ok")

Hmmm... you might not be able to enter a null....

 Test: IIf(NZ([Type Year in here],"") = "","Owes","ok")

 

[baguiar]

Thanks lameid,

I've tried the 1st option before but not second one. Neither worked though.. the same message comes:

"The MS JET database engine does not recognize "[Type year here]" as a valid field name or expression."

 

[dhookom]

You can't use a variable for a field name. It looks like your table structure is not normalized which often causes problems like this.

If you have multiple Year fields, you might need to use an expression like:

Test: IIf(IsNull(Choose([Type Year in here]-2004,[2005],[2006],[2007])),"Owes","ok")

I always recommend against using parameter prompts and suggest you consider normalizing.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lameid]

If you really want a variable field name, then I agree completely that you should normalize.

While a lot more complicated to grasp, you could also programatically build an SQL string and depending on what you want to do with it, use it in various ways. This is rather involved so for what I hope is a quick oneoff, I think Duane's use of the choose function is brilliant. I was not aware of it.

 

[baguiar]

dhookom,
thanks for the post. I don't like to use it neither. The problem is that this is someonelse's design and this it goes into a crosstab query (where I was trying to add the expression). I tried your code with the "choose" function (which was very nice by the way )and still the same error message.
Maybe I need to change the way the queries behave. the table is:

name year ammount

John 2004 50
John 2005 150
Mary 2004 100
Carl 2007 200

SO the guy did create a crosstab query to get :

Name 2004 2005 2007

John 50 150
Mary 100
Carl 200

and the results we need is If the record is NULL for 2007, then we need to see the name of the person (John, and Mary). but could be any year (2006, 2005,..) upon users choice.

Thanks again guys,
B

 

[lameid]

Can you post the SQL of the crosstab and the layout of the tables in the From clause?

If I understand you correctly, the problem is you do not necessarily see all the people for a selected year. Is that right?

 

[baguiar]

lameid,

acctually all I need is, based on the table;

name year ammount

John 2004 50
John 2005 150
Mary 2004 100
Carl 2007 200


;run a query that would return me the people that do NOT have a record for the year 2007 (for example). In other words, John and mary. It could be other years, like 2006 or 2005, but that would be a user criteria.

I was trying to use the Crosstab query already in place.

Thanks!

 

[lameid]

If you use the crosstab already written, 20 minutes later when that query is done running you will be asking how to speed it up.

 

[rheitzman]

Maybe:
SELECT DISTINCT [Name] WHERE [Year]<>[Enter year]

In the QBE just place the prompt in the criteria cell for [Year].

 

[dhookom]

Do you have a table of all names?

Try something like:

SELECT tblAllNames.*
FROM tblAllNames
WHERE [NameField] Not IN (Select [NameField] FROM tblNoNameGiven WHERE [YearField]=[Type Year in here])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mikej336]

Why don't you use a form to collect your year information. Then create a query definition then run the report based on that.

Mike

 

[baguiar]

dhookom,

Thanks a lot. Simple and effective! It takes a little to run (a lot of data) but works like a champ. Amazing when you are stuck on a road and your mind won't let you look around for shortcuts...

Star for you!

 

[baguiar]

And thanks all the other guys for the help too!