Consulting

Results 1 to 8 of 8

Thread: ADO SQL Query question

  1. #1
    VBAX Regular
    Joined
    Sep 2012
    Posts
    63
    Location

    ADO SQL Query question

    I have a very simple database that when someone does a search using my custom for it saves the following details to the database:
    Username
    Clientnumber
    Date-time

    On saving to the database the record gets a unique reference number.

    The next time the access the list I want then to access the top 20 unique clientnumbers in the date/time order that they searched on them.

    I've got it so that they pull back the top 20 unique record but struggling with getting them in Date order. My code is as follows:

    Private Sub GetPrev()
    Dim objMyConn As Object
    Dim objMyCmd As Object
    Dim objMyRecordset As Object
    Dim Username As String
    Username = Environ("UserName")
    ' Set the Variable with relevent code / data
    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    Set objMyRecordset = New ADODB.Recordset
    objMyConn.ConnectionString = "Driver=SQL Server;Server=SQL\SQL;Database=SQL;Trusted_Connection=Yes;"
    objMyConn.Open
    Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = "Select top 20 cs_cltnum from CSResults group by cs_cltnum order by max(cs_DT) where cs_user like '" & Username & "'"
        objMyCmd.CommandType = adCmdText
        objMyCmd.Execute
    Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open objMyCmd
    objMyRecordset.MoveFirst
    objMyRecordset.Sort "cs_DT Desc"
    With Me.cmbcltnum
            .Clear
            Do
                .AddItem objMyRecordset!cs_cltnum
                objMyRecordset.MoveNext
            Loop Until objMyRecordset.EOF
    End With
    objMyRecordset.Close
    objMyConn.Close
    Set objMyConn = Nothing
    Set objMyCmd = Nothing
    Set objMyRecordset = Nothing
    End Sub
    I know that the SQL statment is messed up at the moment as I've been trying lots of different statements from when I was looking on google. any thoughts please?
    Last edited by Aussiebear; 04-19-2023 at 03:28 PM. Reason: Adjusted the code tags

  2. #2
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Quick comment... I like to use ALL CAPS for my sql statements when using key words..
    "SELECT top 20 cs_cltnum FROM CSResults GROUP BY cs_cltnum ORDER BY max(cs_DT) WHERE cs_user LIKE '" & Username & "'"

    I'm not a SQL guru, but I feel like you can use something like
    "ORDER BY cs_DT DESC"

    I'm not sure what the "max(cs_DT)" is, but if you're getting everything else properly, and it's just the ORDER BY that is failing, maybe it's the type of connection that doesn't recognize that way of order results
    _______________________________________________
    Please don't cross-post without providing links to your cross-posts. We answer questions for free. Please don't waste the time of the people helping you.
    For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

    - Frosty

  3. #3
    VBAX Regular
    Joined
    Sep 2012
    Posts
    63
    Location
    I finally got this sorted out yesterday afternoon taking an alternative route!

    objMyCmd.CommandText = "Select top 20 cs_cltnum, Max(cs_key) from CSResults where cs_user like '" & Username & "' Group by cs_cltnum Order by max(cs_key) Desc"
    Instead of using the date field I used the unique key field to order the client numbers which seemed to work.

    I used the Max SQL function to order the 'key' in the Select element then grouped on the client number to ensure I got unique set of numbers then did the final order by to force them to a descending order. If I did not put it on it would just go Assending order.

    Thanks for the suggestion on using the capitals I'll look in to that and see how I get on with it.
    Last edited by Aussiebear; 04-19-2023 at 03:29 PM. Reason: Adjusted the code tags

  4. #4
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Well, one more quick comment then -- I think the reason it started working is because you can't ORDER BY on something which isn't part of your result... your updated sql statement has new stuff in the SELECT area (the stuff between SELECT and FROM)... which you then can use in the ORDER BY area -- that wasn't the case in your original query.

  5. #5
    VBAX Regular
    Joined
    Sep 2012
    Posts
    63
    Location
    Thanks Frosty, fully understand (now) and as per my last line of my original post I said that the SQL was messed up due to the amount of attempts to get it right!

    As I learned as I went along I found that as I was trying different things it got messed up so hence the posting to get a different view... and to clarify things which you help helped with and got me back on track.

    Thanks

    Colin

  6. #6
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Creating SQL statements has never been something I've found easy to do. I was recently given a tip to create the querry in Access and then Home>View>SQL view. That gives you the statement you need to use:

    Here is an example of a statement that I could have probably never devised on my own ;-)

    SELECT [fldFirstName] & IIf(IsNull([fldMiddleInitial]),""," " & [fldMiddleInitial]) & " " & [fldLastName] AS fldName, tblPeople.*
    FROM tblPeople
    WHERE (((tblPeople.fldTitle)="Attorney"))
    ORDER BY tblPeople.fldLastName, tblPeople.fldFirstName;
    Last edited by Aussiebear; 04-19-2023 at 03:29 PM. Reason: Added code tags
    Greg

    Visit my website: http://gregmaxey.com

  7. #7
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    I definitely used Access SQL View to help my understanding of how SQL queries are built, generally speaking. But you also get a lot of extra confusing stuff which doesn't help the understanding (i.e., all those extra parantheticals in the WHERE section).

    In addition, I think you'll run into problems if you tried to use that actual string in VBA code, because of the differences between SQL wanting single quotes around parameters and Access SQL View building parameters with double-quotes (and brackets around field names, etc -- there are some quirks to the SQL view in Access which aren't really "pure" SQL.

    I'd agree it can help in the learning, but you might be sent down a garden path at times -- very analogous to learning VBA via recorded macros.

  8. #8
    VBAX Regular
    Joined
    Sep 2012
    Posts
    63
    Location
    Thanks for the tips, I've used excel's "Microsoft Query" to assist with building some of my SQL statement before but SQL statements is something I'm trying to teach myself to try and be a bit more effiecent with my data retrieval / sorting / totaling etc.

    Thanks for your help and suggestions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •