Returning True Or False

[MarcusMaximus]

I have a querey used for a report that lists quotes from certain dates. Within this query i have a IIF function that displays whether or not the quote is Live. Live is true or false.

From the form that generates the queries i have a combo box to select live, not live or both. The Live field in the query reference the combo box in the form. I can pull back the live and not live quotes easily enough but want to be able to pull both but am having trouble.

I have tried using number to distiguis live and not eg 1 for live 2 for not live and within the query if the combo box is live then criteria is one, if not live the criteria is 2 if both the criteria is >0 but it doesn't work

 

[HiTechCoach]

One way to handle this is to have the combo box have an * is the user selects All.

Combo box columns

Display   Bound Column Value
Live                 -1
Not Live             0
All                  *

then the SQL would something like:

Where [IsLive] Like forms!myform.cboMyComboBox

[IsLive] would be your calculated value that I assume is -1 for true and 0 for false.

 

[MarcusMaximus]

Solved. I used an Or statement in the query builder.

 

[gemma-the-husky]

out of interest, most sytems would probably use a "livequote" yes/no indicator to store the current status of quotes - if you did this you wouldnt need the iif statement.

 

[MarcusMaximus]

One way to handle this is to have the combo box have an * is the user selects All.

Combo box columns

Display   Bound Column Value
Live                 -1
Not Live             0
All                  *

then the SQL would something like:

Where [IsLive] Like forms!myform.cboMyComboBox

[IsLive] would be your calculated value that I assume is -1 for true and 0 for false.

I tried this and it wouldn't work when selecting all. I tried it with 1 for live 2 for not live and * for all. Its telling me that the expression is too complex when *|both is selected. It doesn't seem to like the *