Advanced dropdown list

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

neez@iinet.net.au - 24 Apr 2007 09:52 GMT

Hi,

I'm trying to create a dropdown box, that is half a field list and
half Table/Query.

I'm working on a database for a conglomerate of labs. Four of these
are major labs, the other 22 are branch labs.

At the moment, I just have a dropdown box that has a query and pulls
in all of the labs, and this works fine. The problem is, that the four
major labs, are in the list but not at the top.

Ideally I would like to move these 4 labs to the top of the list, have
a space, and then show the rest of the 22. I have created an SQL
statement to remove the 4 labs. Does anyone know how to manually put
in the 4 labs I want at the top, and then the 22 branch labs ?

I could just do a value list, and type all the labs in the order I
want, but the branch labs drop off/on, so I want something dynamic.
The 4 major labs will never leave the list, so I'm happy to manually
put them in at the top of the list.

Alternatively, can you make a custom "Order By" in SQL ?

Thanks

Reply to this Message

Hunter57 - 24 Apr 2007 13:23 GMT

On Apr 24, 4:52 am, n...@iinet.net.au wrote:

> Hi,
>
[quoted text clipped - 21 lines]
>
> Thanks

I was intrigued by your question so I did a little experimenting and
found that the following SQL did the job for me.

SELECT LastName, FirstName, Suffix
FROM tblMembers
ORDER BY [LastName]="Quinn", [LastName]="Boyette",
tblMembers.LastName;

The [LastName]="Quinn" and [LastName]="Boyette" show up in the grid
like this: Expr1: [LastName]="Quinn" and in another column: Expr2:
[LastName]="Boyette". Also, the the Show box in these columns must be
not checked because if they are it adds two collumns full of numbers.

Thanks to you I now have learned something new.
Hope this helps.

Hunter57
http://churchmanagementsoftware.googlepages.com

Reply to this Message

neez@iinet.net.au - 26 Apr 2007 07:17 GMT

> I was intrigued by your question so I did a little experimenting and
> found that the following SQL did the job for me.
[quoted text clipped - 11 lines]
> Thanks to you I now have learned something new.
> Hope this helps.

Awesome, thanks Hunter,

That worked a treat.
I just changed my existing query to have the order by with the 4 labs
I wanted first, and then the rest.

Basically went from this

SELECT Panel.lab
FROM Panel
GROUP BY Panel.lab
ORDER BY [Panel].[lab];

to this

SELECT Panel.lab
FROM Panel
GROUP BY Panel.lab
ORDER BY [Panel].[lab]="FTH",[Panel].[lab]="PCP",[Panel].[lab]="RPH",
[Panel].[lab]="WCP",[Panel].[lab];

Reply to this Message

Hunter57 - 26 Apr 2007 13:54 GMT

I'm glad it worked for you. Thanks for answering back. That lets others
know that the method works and they can use it for themselves.

Hunter57
http://churchmanagementsoftware.googlepages.com/home

> > I was intrigued by your question so I did a little experimenting and
> > found that the following SQL did the job for me.
[quoted text clipped - 32 lines]
> ORDER BY [Panel].[lab]="FTH",[Panel].[lab]="PCP",[Panel].[lab]="RPH",
> [Panel].[lab]="WCP",[Panel].[lab];

Reply to this Message

Mark J - 24 Apr 2007 13:30 GMT

you coud rank these labs in your table , then you would be able to sort them
by rank in your dropdown.

> Hi,
>
[quoted text clipped - 21 lines]
>
> Thanks

Reply to this Message