Solved Move to specific row in form's combo box (1 Viewer)

mib1019

Member
Local time
Today, 10:05
Joined
Jun 19, 2020
Messages
88
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

Lifelong Learner
Local time
Today, 09:05
Joined
Mar 14, 2017
Messages
8,738
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

MVP / VIP
Local time
Today, 16:05
Joined
Jan 14, 2017
Messages
18,186
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:05
Joined
May 7, 2009
Messages
19,169
Code:
ORDER BY Nz([qryTTT Media].Market,"zzzzz"), [qryTTT Media].CompanyName;
 

mib1019

Member
Local time
Today, 10:05
Joined
Jun 19, 2020
Messages
88
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
 

Users who are viewing this thread

Top Bottom