Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Problem (MS Access 2003)

    I had put speech marks, quotes all over the place but cannot find a way to make the query work.

    The first part works without probem

    mysql = "SELECT DISTINCT StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3) "
    mysql = mysql & "AS NName, TXCLIPS.Comments, TXMASTERS.Barcode"
    mysql = mysql & " FROM TXMASTERS LEFT JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1"
    mysql = mysql & " WHERE (((TXCLIPS.ID1)=[FORMS]![Mainform1].[FORM].[ID1]))"

    but I cannot get a result adding Select Top below.

    mysql = "SELECT * FROM (SELECT TOP 7 "

    Solution greatly appreciated. Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Query Problem (MS Access 2003)

    It's not clear what you want to do.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (MS Access 2003)

    Hi Hans. I am trying to integrate the SQL Top statement into the main query, so a list fills with 7 records. I left out the end part of the code which deals with sort order. The 7 records have string conversion to become Proper case (ie Capital Letter starting)

    Hope that clears it up a bit more. Regards and thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Query Problem (MS Access 2003)

    I still don't understand. Does

    "SELECT Top 7 StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3) "
    ...

    do what you want? Or

    "SELECT DISTINCT StrConv(Concatenate(""SELECT Top 7 Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3) "
    ...

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (MS Access 2003)

    Thankyou Hans. That was just what I wanted, after several hours of trial and error. All the very best regards.

Posting Permissions

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