Expression builder, forms and nested expressions

[The Archn00b]

OK, so I assume the MS Access "Expression Builder" works in SQL.

I understand the Expression Builder allows for conditional logic with the iif function.

My questions are:

1. Can you create nested iif functions like you can in Visual Basic? If so, how? Do you just replace value if true and value if false with further iif expressions?
   
2. Is there a way to reference the value of a form control within the Expression Builder?

Thanks for reading!

 

[pr2-eugin]

OK, so I assume the MS Access "Expression Builder" works in SQL.

Yes, you can have a look at Bob's website : http://www.btabdevelopment.com/ts/frmrefinqry

Can you create nested iif functions like you can in Visual Basic? If so, how? Do you just replace value if true and value if false with further iif expressions?

Yes that's exactly how you do it.. However, if the IIF is going more than 3 deep checking, use a Function instead..

Is there a way to reference the value of a form control within the Expression Builder?

The link above should also answer that question for you..

Hope this helps ! Good luck !

 

[James Dudden]

1. Yes
2. Yes - You need to reference the form like this:
Forms!FormName!ControlName

 

[The Archn00b]

Thanks. What I'm trying to do is put an expression in the criteria box of a query in design mode.

The expression is based upon whether a check box "Alderney_Only" is marked yes or no in the form "Find_Member." If the check box "Alderney_Only" is marked "yes" (It is yes/no and not true/false?) I would like to filter query results to records that just contain "Alderney" which has an Id number of "92." Otherwise, no filtering should take place.

This is the expression I have, but it won't work for me. Any ideas?

IIf([Forms]![Find_Member]![Alderney_Only]=Yes,"92","")

 

[James Dudden]

Remove the criteria you have set in the query and in a new column place the following in the field box:

IIf([forms]![Find_Member]![Alderney_Only] = Yes,[ID]="92",[ID] Like "*" Or IsNull([ID]))

Now in the Criteria box for this new column add True

NB: You need to replace ID with the name of your actual ID field.

 

[The Archn00b]

Remove the criteria you have set in the query and in a new column place the following in the field box:

IIf([forms]![Find_Member]![Alderney_Only] = Yes,[ID]="92",[ID] Like "*" Or IsNull([ID]))

Now in the Criteria box for this new column add True

NB: You need to replace ID with the name of your actual ID field.

When I click the checkbox to Yes, I get an error message:

The expression is typed incorrecly or it is too complex to be evaluated.

This is the expression in the field box:

Expr1: IIf([forms]![Find_Member]![Alderney_Only]=Yes,[County]="92",[County] Like "*" Or IsNull([County]))

 

[James Dudden]

Have you removed the other criteria?

 

[The Archn00b]

Yes, I've removed everything from the criteria boxes, with the exception of "True" in the new one.

 

[Robison]

I also have a similar problem with expressions. I am developing a order entry system for my sons business. He makes trophies. What I need is help with part of the pricing. How do I put in to my query that If [ColumnStyle] is Wide then [ColumnPrice] = .11 per inch. There are only two choices for ColumnStyle either Round or Wide. Wide price would be .15 per inch. The length of the trophy is in another field called [ColumnLength]. This is very confusing and I hope I have presented it properly. The [ColumnStye] determines the price and the length of the column determines to total price of that column. So basically If [ColumnStye]=Wide the price would = .15 * [ColumnLength].
I would appreciate any help I can get. I know I am over my head but with someone assistance I sure hope I can learn. Thanks in advance for helping me.

 

[James Dudden]

Robinson,
In a new column in the query in the field box put the following:

Price: iif([ColumnStye]="Wide", .15 * [ColumnLength],0)

What happens though if the column style is not 'Wide'? With the line above the proce will be 0.

 

[James Dudden]

Archn00b
What happens if you take the True out?

Also is True written without quotes in your query?

 

[The Archn00b]

If I take "True" out and open the query, it runs the query, but there is no filtering effect. I haven't typed True in quotation marks.

 

[James Dudden]

Is there grouping in your query?

 

[Robison]

JD....thanks so much...there is only two criteria either "wide" or Round. So would I then have to make another statement that says If wide then .15 or If round then .11?
Or something like that?
S
THANK YOU THANK YOU THANK YOU FOR HELPING ME. I have several other area that this logic will be applied so I really appreciate it. S

 

[James Dudden]

Robinson, If I were you I would do a Google search for If statements in VBA to gain a greater understanding but essentially you are passing a condition and if met the corresponding code will be run and if the condition is not met the 'Else' part of the If statement will execute.

This is what you require:

Price: iif([ColumnStye]="Wide", .15, .11) * [ColumnLength]

So we are saying if ColumnStye = 'Wide' use .15 otherwise use .11

 

[Robison]

OH thanks so much....I never thought of it that way. I definitely will do research on google. I use google first before I use this forum as I really do want to learn how to use If statements. Again thanks so much for ALL your help.
S

 

[The Archn00b]

Is there grouping in your query?

What's that?