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

    VB Query change (MSACCESS 2003)

    I am struggling to change this line. I am trying to change the query from being a like to an equal match.

    mysql = mysql & " WHERE TXCLIPS.Comments & ' ' Like '*" & Me!LNAME14.Caption & "*' "

    Many thanks, I have tried.

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

    Re: VB Query change (MSACCESS 2003)

    Try
    <code>
    mysql = mysql & " WHERE TXCLIPS.Comments=" & Chr(34) & Me!LNAME14.Caption & Chr(34)
    </code>
    Chr(34) is the double quote character ".

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

    Re: VB Query change (MSACCESS 2003)

    Thanks Hans, that worked.
    I don't know if this one is too complicated. The first code works okay, but does not show records where there are no names in. The second one I changed the joins so it might include records with no names, I did it in the grid and it seemed to work but putting in VB it gives no output. Will understand if it's too dificult, thanks.

    'mysql = "SELECT StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3) AS Name,"
    'mysql = mysql & " TXCLIPS.Shot, TXCLIPS.StarRating AS Rating, TXCLIPS.Comments, TXCLIPS.ID2"
    'mysql = mysql & " FROM TXCLIPS INNER JOIN (AthleteNames INNER JOIN JUNCTION ON"
    'mysql = mysql & " AthleteNames.AthleteID = JUNCTION.AthleteID) ON TXCLIPS.ID2 = JUNCTION.ID2"
    'mysql = mysql & " WHERE TXCLIPS.Comments & ' ' Like '*" & Me!LNAME14.Caption & "*' "
    'mysql = mysql & " ORDER BY TXCLIPS.SHOT"
    'Me.L88.RowSource = mysql


    mysql = "SELECT StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3) AS Name"
    mysql = mysql & " TXCLIPS.Shot, TXCLIPS.StarRating, TXCLIPS.Comments, TXCLIPS.ID2"
    mysql = mysql & " FROM TXMASTERS INNER JOIN (TXCLIPS LEFT JOIN (AthleteNames RIGHT JOIN JUNCTION ON AthleteNames.AthleteID"
    mysql = mysql & " = JUNCTION.AthleteID) ON TXCLIPS.ID2 = JUNCTION.ID2) ON TXMASTERS.ID1 = TXCLIPS.ID1"
    mysql = mysql & " WHERE TXCLIPS.Comments & ' ' Like '*" & Me!LNAME14.Caption & "*' "
    mysql = mysql & " ORDER BY TXCLIPS.SHOT"

    THIS CODE WORKS IN THE QUERY GRID, BUT DOES NOT INCLUDE THE LNAME14 LINE

    SELECT StrConv(Concatenate("SELECT Athlete FROM qselJunctionNames WHERE ID2=" & [TXCLIPS].[ID2]),3) AS Athlete, TXCLIPS.Shot, TXCLIPS.StarRating, TXCLIPS.Comments, TXCLIPS.ID2
    FROM TXMASTERS INNER JOIN (TXCLIPS LEFT JOIN (AthleteNames RIGHT JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID) ON TXCLIPS.ID2 = JUNCTION.ID2) ON TXMASTERS.ID1 = TXCLIPS.ID1;

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

    Re: VB Query change (MSACCESS 2003)

    Sorry, that's too complicated for me indeed without seeing the database.

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

    Re: VB Query change (MSACCESS 2003)

    Quite understand, but thanks for the welcomed help. 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
  •