Using Choose Function in Query

[kannon]

Hi

I have an option group that lists a variety of categories which I want to query on.

I know that the option group only returns values.

In the Option Group, On Time = 1, Over Due =2 ,,,,

I am using a Choose function in my query. That is working fine. But ... I would like to have a last choice called "All" such that when selected all data is shown (i.e., On Time, Over Due, ....).

I am using the following:

Choose ([optiongrpStatus],"On Time", "Over Due", .... ,???) as my query criteria.

Have not figured what I can put in the last choice to get all data. Any ideas?

Tried "Is Not Null", tried "On Time or Over Due" - know these don't make sense -just wanted to try.

Thanks

 

[Roku]

What do you do with the output of the Choose function? Is it part of a WHERE clause? If so, try putting the entire WHERE clause within an IIF function which tests for the 'All' condition and includes the WHERE only on the False condition.
If this doesn't help, please provide more detail about the context of use.

 

[kannon]

The Choose function:

Choose ([optgrpStatus],"On Time", "Over Due") is part of my query. Using the Query Wizard I put that in the Criteria row.

Works fine for On Time and Over Due, just can't figure how to run the query with a criteria to get all entries. I tried "*" but all it looks for is * in the Status field.

Hope this explains it better.

Thank you

 

[Roku]

Please post the SQL syntax in full. From the Query design, go to SQL view, then copy/paste the text here.
Thanks.

 

[kannon]

Here's the code:

SELECT qryAFED.System, qryAFED.Status FROM qryAFED

WHERE (((qryAFED.[Status])=Choose([optgrpStatus],"On Time",Over Due", "All")))

ORDER BY qryAFED.System;

The optgrpStatus is an Option Group with three buttons - On Time, Over De, and All.

When I press On Time, the query runs and I get a list f systems that are On Time. Same for Over Due. Just like to get all of them by pressing "All"

Thanks

 

[Roku]

I'm struggling with this one, as I'm not entirely sure of the context.

From your description, I infer that you have a form with an OptionGroup which contains 3 option buttons. Your form contains two data fields (qryAFED.System, qryAFED.Status). The table data for 'qryAFED.Status' contains two values; "On Time" and "Overdue".
When you run the query, the WHERE clause is interpreted as 'WHERE qryAFED.[Status]="On Time"' when Option button 1 is pressed.

Clearly "All" is not a value in the table, so you need a wild-card substitution, which effectively suppresses the WHERE clause.

Is my interpretation correct?

What is the event which triggers the query to run?

 

[kannon]

You are correct.

The Choose Function is used in the query criteria, I do a requery on a list box using the query, triggered when the option group button is updated. Hope that makes sense. So ...

First - Option Group - select On Time, Over Due, or (trying) All.

When the option group selection is made, a list box with list of Projects is requeried based on the query criteria using the Choose Function - here the option group is one, Choose provides the criteria "On Time".

Works rather well and lot of good flexibility for other programs that can use an Option Group to trigger a query update. Only limitations is how to do All.

Thank you

 

[Roku]

Hi
I have tried various combinations of in-line methods to modify the query as you want, to no avail. The challenge is to exclude the WHERE clause for the 'All' case.
The best solution I have found (using your approach) is in the VBA AfterUpdate event code for the Option Group:

Private Sub optgrpStatus_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT System,Status FROM qryAFED"
If optgrpStatus < optgrpStatus.Controls.Count Then strSQL = strSQL & " WHERE Status=" & Chr(34) & Choose(optgrpStatus, "On Time", "Overdue") & Chr(34)
strSQL = strSQL & " ORDER BY System;"
Me.lsbFilter.RowSource = strSQL
End Sub

You might also consider using the Option Button Tag property to specify the literal values you want in the query. In the control property sheet under the 'Other' tab, put the values you want ('On Time', 'Overdue'). The modified AfterUpdate event now looks like this:

Private Sub optgrpStatus_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT System, Status FROM qryAFED"
If optgrpStatus < optgrpStatus.Controls.Count Then strSQL = strSQL & " WHERE Status=" & Chr(34) & optgrpStatus.Controls.Item(optgrpStatus - 1).Tag & Chr(34)
strSQL = strSQL & " ORDER BY System;"
Me.lsbFilter.RowSource = strSQL
End Sub

In both cases, the logic relies on the last item in the list meaning 'All', because the WHERE clause is suppressed when the option value equals the group count (i.e. the number of option buttons).
If you want a specific query to run when the ListBox first becomes visible, then hard-code that query string in its Row Source (it will be replaced later by the click event).
Note the leading space for WHERE and ORDER clauses - this ensures the syntax will parse irrespective of the presence or absence of the WHERE clause.
The advantage of the second approach (using Tag) is that you can add option buttons to the group without having to change the code.
As an aside, I would advise avoiding variable names like 'System' and 'Status', as you are likely to run into reserved words with this approach. I assume your variables are from a query, so a prefix would help, such as 'qStatus', 'qSystem'.