IIF function

[ajetrumpet]

I am having a bit of trouble with some SQL now that looks like the following....

SELECT [fields]
FROM [table]
WHERE ([field]=[control1]) 
AND (IIf([control2] Is Null,"[set value]","[>=control2 value]"));

Can someone tell me where I have gone wrong??

The idea is to get a range of values with the "IIF" function (>=control value) if a value is manually entered into the control and a set value from the query if the control is left blank.

 

[Guus2005]

I am having a bit of trouble with some SQL now that looks like the following....

SELECT [fields]
FROM [table]
WHERE ([field]=[control1]) 
AND (IIf([control2] Is Null,"[set value]","[>=control2 value]"));

Can someone tell me where I have gone wrong??

The idea is to get a range of values with the "IIF" function (>=control value) if a value is manually entered into the control and a set value from the query if the control is left blank.

Your query is incomplete. You have to add an "=" before or after the IIF statement. Furthermore the [>=control2 value] is a strange name for a variable or a control.

Where A = B
AND Y = IIF(...)

 

[Brianwarnock]

What are control 1 and control2, where are they and how are their values entered?
Oh and BTW it is IIF(IsNull([.....],


Brian

 

[Guus2005]

SELECT [fields]
FROM [table]
WHERE ([field]=[control1]) 
AND <PutYourExpressionOrFieldnameHere> = (IIf([control2] Is Null,"[set value]","[>=control2 value]"));

You have to compare the outcome of the IIF statement with something. You can also put <PutYourExpressionOrFieldnameHere> after the complete IIF statement because

A = B

is the same as

B = A

Enjoy!

 

[ajetrumpet]

Furthermore the [>=control2 value] is a strange name for a variable or a control.

It's not the name of a control Guus...sorry, mistake on my part. That's totally wrong anyway, that's the "false" value...looking at it now, it doesn't make much sense. What is supposed to be there is

"MAX([field name related to control2])"

Thanks Guys...fixed it.

 

[Brianwarnock]

Guess we will never know what you were trying to do and what the solution was.

 

[ajetrumpet]

Is that necessary Brian, the people here are the "fix it" people aren't they???

 

[Rabbie]

It is necessary for the sake of people who search the forum looking for a solution.

 

[ajetrumpet]

How many times have seen a reply on this forum that says..."look here", and they reference a thread, then there is no reply by the poster???

Don't you ever wonder if their problem had gotten solved by the previous thread reference??

 

[Brianwarnock]

How many times have seen a reply on this forum that says..."look here", and they reference a thread, then there is no reply by the poster???

Don't you ever wonder if their problem had gotten solved by the previous thread reference??

It is obvious that what they were trying to do was understood and the referenced thread solved the problem, that does not apply to this thread, but hey lets forget about it.

Brian

 

[Rich]

Is that necessary Brian, the people here are the "fix it" people aren't they???

You might actually be suprised that most of the regulars posting here come not just to share their knowledge but to seek further knowledge also

 

[ajetrumpet]

You got it Brian. My understanding of statements is above the average. I won't start a fire just now.