Adding "ALL" to Combo Box 4

[ReluctantDataGuy]

I've seen code solutions for populating Combo Boxes and including an "ALL" selection, but I wanted to use a Select statement. I think I have a pretty good cludge here but I'm wondering if anyone has a better solution - ie: that doesn't require a table as my solution does.

I made a table (zzzthat has two fields: Fld1 and Fld1
The table has one row: Fld1 = -1, Fld2 = "ALL"

Originally I had the following Select statement for my combo box:

SELECT zzzType.TypeId, zzzType.TypeCode FROM zzzType ORDER BY zzType.TypeCode;

I changed it to the following:

SELECT zzzComboAdd.Fld1 AS TypeId, zzzComboAdd.Fld2 AS TypeCode FROM zzzComboAdd UNION SELECT zzzType.TypeId, zzzType.TypeCode FROM zzzType ORDER BY TypeCode;

It works great, and it'll work for all of the combo boxes in my appication simply by changing the field names in the first table of the union query.

Problem is, I hate the fact that I have to use a table. Seems like there should be a Select statement that doesn't require the extra table.

Any ideas?

 

[TonyJollans]

Hi ReluctantDataGuy,

It's still a bit of a kludge (and along the same lines) but you should be able to do it with this - it still needs a valid table, but any one will do.

SELECT DISTINCT -1 AS TypeId, "ALL" AS TypeCode FROM zzzType UNION ALL SELECT zzzType.TypeId, zzzType.TypeCode FROM zzzType ORDER BY TypeCode;

Enjoy,
Tony

 

[ReluctantDataGuy]

Thank you Tony! That did the trick. I just added a space in front of " ALL" to make sure it sorts to the top even if there's a "lower" value than "ALL" in the list.

 

[ChrisJF]

Hi there, this looks promising, will this replace the default blank screen on a combo box when the form is opened?

I am having problems interpreting and applying the above code to my application.

I am trying to replace the Blank combo box with "ALL", without the need for ALL to exist in the underlying query/table.

can you please explain how to use this with a combo box, say with a ROW SOURCE property populated like this.."SELECT [aProcedure].[Procedure/ Grid Standard] FROM aProcedure; ]"

Thanks
ChrisJF

 

[TonyJollans]

Hi ChrisJF,

You should just be able to match the extra selection to your current one like ...

SELECT DISTINCT "ALL" AS [Procedure/ Grid Standard] FROM aProcedure
UNION ALL
SELECT [aProcedure].[Procedure/ Grid Standard] FROM aProcedure;"

Enjoy,
Tony

 

[ChrisJF]

Tony,

I have done as you said, but when I select the ALL in the Combo box, and run the query which takes that value, I get no result. I suppose I have to use an
IIF(...="all","*",....) type of solution.
Any comments?

 

[TonyJollans]

Hi ChrisJF,

Yes, you're right. It does depend on what you do with the value selected in the combo, but you can't just feed it into SQL directly as though it were a value in your table. It can only be, effectively, a flag for your code to act upon.

Enjoy,
Tony

 

[ChrisJF]

Tony,

Following on from above, I am using the following as criteria for the query taking an input from the combo box,

IIf([Forms]![AdminSearch]![frmAdmChapterNo].value="ALL",Like "*",[Forms]![AdminSearch]![frmAdmChapterNo])

where frmAdmChapterNo is the Combo box.

I have also set up ALL as the default value in the combo box properties.

Problem is the Like "*" does not seem to work. I get zero results returned when ALL is selected in the combo box.
I need to return the full data set when ALL is selected, but it may have some null values so the nz() function may need to be combined.
I have been using the following criteria for the selection which has been working correctly when the combo box default is null.

(like "*" & [Forms]![AdminSearch]![frmAdmChapterNo].value &"*&quot or nz([Forms]![AdminSearch]![frmAdmChapterNo],"&quot="&quot

Help!Please

Regards
ChrisJF

 

[TonyJollans]

Hi ChrisJF,

It's late and I'm about to go and watch a bit of poker on TV before I go to bed, so very quickly. I'll revisit in the morning to see how you've got on.

Your criteria at the moment, if I read you right, are Like "*combovalue*".

When ALL is selected do you not just want Like "**"? In other words, should you not just feed an empty dtring into the criteria.

Enjoy,
Tony

 

[ChrisJF]

Tony, I have tried that but for some reason it spits out a null result, however, if I select a chapter from the Combo box the resulting dynaset is correct. It just seems to struggle with interpreting a null result (ALL) from the IIF statement. Any suggestions?

ChrisJF

 

[TonyJollans]

Hi ChrisJF,

I have just tried out this slight variation on what you have and it works for me ..

Like Iif(([Forms]![AdminSearch]![frmAdmChapterNo]="All","",([Forms]![AdminSearch]![frmAdmChapterNo]) & "*"

I haven't tried combining it with anything else (like your nz function) but I don't see why it shouldn't work.

Enjoy,
Tony

 

[cranebill]

I have used the following.... modify it for your own needs

SELECT "*" AS RecID,"(All)" AS Stat From (Your Table) UNION SELECT (Your Field 1) AS RecID, (Your Field 2) AS Stat FROM (Your Table)

In this example my combo box has 2 fields one for status name for example "Open" and a status ID "1" only the status name column is shown on the combo box itself but if both columns were to show mine would be like this:

Column 1 | Column 2
* (ALL)
1 Open
2 Closed

Column 1 is bound for use in queries.

Hope this helps

Bill

 

[WhiteZiggy]

I was having the same problem. I have it i Think.

The problem is the "Like" . You dont need the IIF. Just change criteria in the query to read.

"Like [Forms]![frmQuery]![cboAccount]"

With the "Like" in the front. Then use the other example with the * as the holder.
Column 1 | Column 2
* (ALL)

Then you need no other changes..

WZ