Combine nested IIF and SELECT TOP (1 Viewer)

i.am.sophie

Registered User.
Local time
Today, 11:38
Joined
May 29, 2015
Messages
14
[SOLVED] Combine nested IIF and SELECT TOP

Hi All,

I'm working on a database which shows the performance of our field colleagues.

I'm done with almost all functions, except for one.

I have a search form where the user will be able to search colleagues based on different criteria. One of this goes like this:
Show me the top..
5%
10%
15%
and an option button next to each.

I need a query something like this:

IIF [Top5]=True, SELECT TOP 5 PERCENT ColA, ColB, ColC
ELSE
IIF [Top10]=True, SELECT TOP 10 PERCENT ColA, ColB, ColC
ELSE
IIF [Top15]=True, SELECT TOP 15 PERCENT ColA, ColB, ColC
FROM Table1
ORDER BY Score DESC

Can someone please help?

Thanks
 
Last edited:

plog

Banishment Pending
Local time
Today, 06:38
Joined
May 11, 2011
Messages
11,613
This isn't going to be straight SQL, this will require VBA. You will have to dynamically create your SQL, then define a query object with your SQL and then open and delete said query object.

This is the tricky part of the code:


Code:
Private Sub click_RunTopQuery()
    ' creates SQL for selected query and runs it

    str_sql = "SELECT * FROM YourTableNameHere"
    ' sql statement to be created and run

    Set dbCurr = CurrentDb()
    Set qdfCurr = CurrentDb.CreateQueryDef("qryTemp", str_sql)
    ' creates temporary query based on SQL

    DoCmd.OpenQuery "qryTemp"
    dbCurr.QueryDefs.Delete "qryTemp"
    ' opens then deletes temporary query

End Sub

You will need to use your conditional statements to build your SQL statement.
 
Last edited:

i.am.sophie

Registered User.
Local time
Today, 11:38
Joined
May 29, 2015
Messages
14
Whoa.

Thanks for the quick reply.

I'll try to figure it out, but I have the feeling my Intro to SQL training might not be enough for this :(
 

Brianwarnock

Retired
Local time
Today, 11:38
Joined
Jun 2, 2003
Messages
12,701
I no longer have Access so this will probably be my only response.
If you have just 3 predefined % then 3 simple queries would seem the way to go rather than using VBA to build a SQL string obtaining the value from the form to put into that string.

Brian
 

Users who are viewing this thread

Top Bottom