Please Help!? Show/Don't show fields

[A. Smart]

How do I use criteria to show or not show a fiel, for example, I have an IIf
function:

IIf([Forms]![FormName]![ComboBox]="Text",([Query].[Fieldname])=True)

I would only like to show this field when the criteria is met and block out
other criteria. Thank you in advance!

 

[Jeff Boyce]

Are you saying you want a query to change which fields it includes,
depending on the value on a form?

One approach would be to use a SQL statement that you build "on the fly"
from the form itself.

Regards

Jeff Boyce
<Access MVP>

 

[A. Smart]

Could you give me an example please, I'm not hot on SQL statements!
Cheers
--
Regards

Ashley Smart

Are you saying you want a query to change which fields it includes,
depending on the value on a form?

One approach would be to use a SQL statement that you build "on the fly"
from the form itself.

Regards

Jeff Boyce
<Access MVP>

How do I use criteria to show or not show a fiel, for example, I have an
IIf
function:

IIf([Forms]![FormName]![ComboBox]="Text",([Query].[Fieldname])=True)

I would only like to show this field when the criteria is met and block
out
other criteria. Thank you in advance!

 

[Chaim]

Are you trying to dynamically alter the select list of a query? You can't do
it this way. You can try nested IIFs by using the false option of the IIF
statement
(IIF (expression, true-value, false-value)) as below:

select IIF (Forms!FormName!ComboBox = "text", fieldname1, IIF
(Forms!FormName!ComboBox = "text", fieldname2,"") etc.

This will work but you're going to drive yourself crazy balancing
parentheses if there are too many options (unless you've got a reasonable
editor that balances parentheses for you), you need one of these nested IIFs
for each option in the combo box, and you've got to remember to change the
query if the set of options changes ("text" doesn't have a match- a zombie;
or the text changes). It gets really screwy if you have to worry about the
',' field separators, although with a ComboBox you only have a single
selection, so you won't have to.

Good Luck!

 

[Jeff Boyce]

Your query is actually a SQL statement, and you can see it by changing the
view of your query in design mode to SQL.

A "dynamic" SQL statement would be build in code, behind the form, probably
on a command button.

You'll need to work in both SQL and in VBA to pull this off.

Your SQL statement would be "assembled" by using an If ... Then statement
in code to check for the value of the form's control, then include (or not)
the reference to the field as part of the SELECT clause -- for example:

If Me!chkMyCheckBox = True Then
strSQL = strSQL & ", MyQueryField"
End If

Good luck!

Jeff Boyce
<Access MVP>

Could you give me an example please, I'm not hot on SQL statements!
Cheers
--
Regards

Ashley Smart

Are you saying you want a query to change which fields it includes,
depending on the value on a form?

One approach would be to use a SQL statement that you build "on the fly"
from the form itself.

Regards

Jeff Boyce
<Access MVP>

How do I use criteria to show or not show a fiel, for example, I have
an
IIf
function:

IIf([Forms]![FormName]![ComboBox]="Text",([Query].[Fieldname])=True)

I would only like to show this field when the criteria is met and block
out
other criteria. Thank you in advance!