SQL Query Help... help rebuilding a couple lines of code?

[Woodman650]

hey guys,
I'm no whiz with SQL, so I have no idea what to do with the code below.

Here is the original statement:

SELECT 0 as CastID,  "<all>" as Cast FROM tblzNull UNION ALL SELECT tblCast.CastID, tblCast.Cast FROM tblCast
ORDER BY Cast;

basically, it puts a default value of "<all>" in a combo box and allows all records to be searched. I've deleted the CastID column and tblCast table though... so now I just have a query called qryClientData and a column being pulled through it called Cast. the code now in the combobox is:

SELECT qryClientData.Cast
FROM qryClientData
WHERE (((qryClientData.Cast)<>"IsNull"))
ORDER BY qryClientData.Cast;

My question is... can anyone help me out as far as formatting this new code to achieve the same result as the original? (default value of "<all>" in the combobox). thank you

 

[mhartman1]

Hello Woodman

Try the below code. It may need some tweeking I did not have time to actually test it.

Select "All" as a, -2 as SortNumber FROM qryClientData
Union Select "---" as a, -1 as SortNumber FROM qryClientData
Union Select qryClientData.Cast FROM qryClientData
Where (((qryClientData.Cast)<>”IsNull”))
ORDER BY qryClientData.Cast;

Regards
Mark

 

[Woodman650]

the combo just empties completely. I'm really no good at SQL so I wouldn't know where to make changes to fix this thanks though Mark

 

[Remou]

How to add 'ALL' to List Box or Combo Box.
faq701-2330

 

[Woodman650]

right yes, I don't have a CastID field anymore. do I absolutely need one?

 

[Remou]

No, not now you have a unique value (Select Distinct) for each cast. Try:
[tt]Select "All" as Cast From tblzNull
Union
SELECT Distinct qryClientData.Cast
FROM qryClientData
WHERE qryClientData.Cast Not IsNull
ORDER BY qryClientData.Cast;[/tt]

You may need to fiddle with spaces to get 'All' sorted first, because you do not have a numeric ID.

 

[Golom]

Just to add a bit to Remou's idea

Select "All" as Cast From tblzNull
 
Union

SELECT Distinct qryClientData.Cast
FROM qryClientData
WHERE qryClientData.Cast [COLOR=red]IS NOT NULL[/color]
ORDER BY qryClientData.Cast;