Results 1 to 4 of 4
  1. #1
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87

    Variable in SQL statement

    SELECT TOP 5 PERCENT DonorT.DonorFirstName, DonorT.DonorLastName, Rnd([DonorID]*Now()) AS X
    FROM DonorT


    ORDER BY Rnd([DonorID]*Now()) DESC;

    Is it possible to use a variable to set the Top return value in an SQL statement? Basically, I need to have that value be based on a value from a combo box in the form

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You would have to build the SQL in a string then update the query definition, so something like
    (Air code untested!)
    Code:
    Sub Test()
        Dim strSql As String
        Dim qDef As QueryDef
    
    
        strSql = "SELECT TOP " & Me.YouComboControlName & " PERCENT DonorT.DonorFirstName, DonorT.DonorLastName, Rnd([DonorID]*Now()) AS X "
        strSql = strSql & " FROM DonorT "
        strSql = strSql & " ORDER BY Rnd([DonorID]*Now()) DESC; "
        
        Set qDef = CurrentDb.QueryDefs("YourQueryName")
        
        qDef.SQL = strSql
        qDef.Close
        
        SET qDef = Nothing
        DoCmd.OpenQuery "YourQueryName"
           
    
    
    End Sub
    
    
    
    
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    this will ask for the Top N, then save it to the query. replace the const:
    kQRY = "qsTopVals"
    with your query. The query must already exist, it wont create it, only modify.
    and change the SQL to your sql.

    Code:
    
    
    Code:
    Public Sub SetTopNqry()
    Dim vVal
    Dim qdf As QueryDef
    Const kQRY = "qsTopVals"
    
    vVal = InputBox("get top#", " Enter top value")
    
    Set qdf = CurrentDb.QueryDefs(kQRY)
    qdf.SQL = "SELECT TOP " & vVal & " tClients.* FROM tClients;"
    qdf.Close
    
      'open the query or report here!
    DoCmd.OpenQuery kQRY
    Set qdf = Nothing
    End Sub
    

  4. #4
    Glenley is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    87
    Thank you Minty! That worked

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Use Variable IN Update Statement
    By chalupabatman in forum Programming
    Replies: 2
    Last Post: 09-26-2017, 08:19 AM
  2. Inserting a variable into a SQL statement
    By adacpt in forum Programming
    Replies: 3
    Last Post: 11-25-2012, 06:02 PM
  3. select statement with variable table name
    By dv89k in forum Queries
    Replies: 1
    Last Post: 05-26-2011, 10:54 PM
  4. Help Using Variable in DLookup Statement
    By bcmarshall in forum Access
    Replies: 9
    Last Post: 12-02-2010, 12:44 AM
  5. Replies: 5
    Last Post: 04-05-2010, 11:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums