Solved Move to specific row in form's combo box

[mib1019]

I have a combo box on a form

SELECT [qryTTT Media].Company_ID, [qryTTT Media].CompanyName, [qryTTT Media].Market
FROM [qryTTT Media]
WHERE ((([qryTTT Media].[Bill_ID].[Value])=8))
ORDER BY [qryTTT Media].Market, [qryTTT Media].CompanyName;

The box displays CompanyName and [qryTTTMedia].Market. Some of the companies don't have an associated Market, so the sort order lists all of those first. Most of the time, I want a company with an associated Market (those are all Radio and TV Stations).

I'd like the box to zoom to the first item that has a value in the Market column. I want to keep the Market column as the first sort, but want to get to the first place in the list that actually has a Market in it. How do I accomplish that?

Any ideas?
MIB1019

 

[Isaac]

I have a feeling this is something that will be several ways you could do, but here is one idea:

1. Create a sql SELECT statement that selects the top 1 item where Market is not null (don't forget the Order By clause)
2. Open that as a recordset, so you can assign the recordset appropriate Field's value to a variable.
3. Assign the combobox's .Value property as the value of your new variable. (you'd have to make sure that was the bound column).

 

[isladogs]

More possible solutions
1. Use Nz(Market, "some value") where "some value" will be sorted last e.g. use ZZZZ
2, Replace your query by a union query made up and sorted in two parts: Market<>"" and Nz(Market,"")=""

 

[arnelgp]

ORDER BY Nz([qryTTT Media].Market,"zzzzz"), [qryTTT Media].CompanyName;

 

[mib1019]

ORDER BY Nz([qryTTT Media].Market,"zzzzz"), [qryTTT Media].CompanyName;

That worked perfectly. Brilliant!
I have so much to learn. Wish I could just hang out here.

Thanks for the advice, as always.
MIB1019