If...then statements in query design? (1 Viewer)

[genevx]

Hi, just wondering if there was any way to make if...then statements or the likes of it in the criteria part for the fields in the query design view. I would like to make a field false and then have the data sorted accordingly if a checkbox is clicked on a form. If the checkbox is not clicked, the data would include information of that field. I hope that this made sense.... =P

genevx

 

[RV]

You'll have to use VBA for this, coded in a On_Click Event defined in your form.

You can't "control" form behavior in queries.

RV

 

[The_Doc_Man]

RV is right but there is more to it.

Look up the IIF function, which allows you to choose between two possible results depending on another field.

It is possible to define an expression in a query such that an IIF governs the returned values. I.e., in the query design grid, you can have a field such as

FunkySort: IIF( [CheckBox], [ValueOfSomethingElse],"zzzBadValue")

then check the Ascending or Descending sort on this field in the query grid.

BUT - the values you test have to be in the underlying TABLE, not on a form. So [CheckBox] must actually be the name of a Yes/No field in your table. [ValueOfSomethingElse] must actually be a field in your table. On the other hand, whatever you want to use for "zzzBadValue" is arbitrary.

Not only can you sort on this, but since it appears to be part of a SELECT query, you can even build reports on it. In which case the name of the IIF field is [FunkySort].