Use Wild Card in Combo Box

[rubintheyid]

Hi,

My current issue is - I have created a combo box which obtains its list from a query. This works well and my query runs when one of the list items is selected.

I have added an "ALL" to my list but i would like this to act as a wild card when selected.

Can you advise how I make it a select all when "ALL" is selected.

Details are:

form name = Investigate
Combo box = CBO_Franchise

Criteria in query is: [forms]![Investigation]![Cbo_Franchise]

Row source in combo box = SELECT Tbl_ALL_INTEC_DATA.FRN_ID FROM Tbl_ALL_INTEC_DATA UNION select "ALL" FROM Tbl_ALL_INTEC_DATA GROUP BY Tbl_ALL_INTEC_DATA.FRN_ID HAVING ((Not (Tbl_ALL_INTEC_DATA.FRN_ID) Is Null))
ORDER BY Tbl_ALL_INTEC_DATA.FRN_ID;

This results in combo list of:
ALL
EB
TW
NT

Query runs with any of EB,TW or NT selected but not with ALL. I would like all to show all results ie a wildcard.

Many thanks for any help

 

[Uncle Gizmo]

You may find this Video and Example Helpful

 

[rubintheyid]

Thanks Uncle Gizmo,

Demo very useful but I still can not get this to work. I now have the message:"The number of columns in the 2 selected tables queries of a unionquery don't match"

Can you help?

 

[vbaInet]

Read up on Union query. They columns in either parts of the Union must have the same number of columns and must all be of the same data type.

 

[boblarson]

Read up on Union query. They columns in either parts of the Union must have the same number of columns...

Correct

... and must all be of the same data type.

Incorrect

They do not have to have the same datatypes. There must be the same number of columns, but you can mix datatypes, but the resulting output will be coerced into the appropriate datatype that will encapsulate all of the mixed types.

So, if you have numbers and text then text will be the result (but the numbers will be there - only as text).

 

[vbaInet]

Thanks boblarson. I should have said "similar"

 

[boblarson]

Thanks boblarson. I should have said "similar"

Well, even that would not be correct. You can have a Union Query with an integer, long integer, date, text, etc. ALL in the SAME field. It would, of course coerce all of the other datatypes to return a text value for integer, long integer, date, etc.

So, even the word 'similar' would really not have been correct.

The ONLY requirement for a UNION QUERY is that the queries being joined have the same NUMBER of fields - nothing else.

 

[vbaInet]

Hmm... maybe Access has a way of interpreting the UNION that makes for smooth coercion. I supposed it has something to do with the lack of datatypes. You've made me open my SQL Bible by Alex Kriegel (I bet you know it) lol, it does actually say "they must be of compatible data types". Even the very well known w3schools says the same.

 

[boblarson]

Yes, that is true. Access does handle it differently than SQL Server. So, for SQL Server you can't mix and match like you can in Access.

 

[vbaInet]

Oh, I didn't know that. Thanks for the correction

So would a UNION fail if using a pass-through query to sql server when the data types are dissimilar?

 

[boblarson]

Oh, I didn't know that. Thanks for the correction

So would a UNION fail if using a pass-through query to sql server when the data types are dissimilar?

A Pass-through query is formed exactly like a SQL T-SQL query so yes it would fail in that instance.

But you can coerce them manually so it will work.

Like this:

SELECT Cast(dbo.MyTable1.IntField As VarChar) As SomeName
FROM MyTable1
UNION
SELECT dbo.MyTable2.TextField
FROM MyTable2

 

[vbaInet]

Thanks for clarifying.