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

    No outpot from query (MSACCESS 2003)

    I have played around with quotes on this, but still get nothing. Put it into a messagebox but cannot see what should or not be there. Regards

    mysql = "SELECT UCase(StrConv(Mid([Athlete],InStrRev([Athlete],"" "")+1),3)) AS Surname, StrConv([Athlete],3) AS [Full Name],"
    mysql = mysql & " AthleteNames.Country, AthleteNames.Athlete, AthleteNames.AthleteID FROM AthleteNames"
    mysql = mysql & " WHERE ((UCase(StrConv(Mid([Athlete], InStrRev([Athlete], "" "") + 1), 3)) "
    mysql = mysql & " Like '" & Me!UUU.Caption & "*'"

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

    Re: No outpot from query (MSACCESS 2003)

    You have a condition on UCase(...) so the string you're comparing to should be in upper case too, so try

    ...
    mysql = mysql & " Like '" & UCase(Me!UUU.Caption) & "*'"

    BTW there is no point in using StrConv(..., 3) if you're going to apply UCase to the result.

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

    Re: No outpot from query (MSACCESS 2003)

    Thanks Hans, but I still get no output. This is the code that was inside the listbox rowsource, maybe I have done something wrong in my conversion before pasting my last code.
    ?

    SELECT UCase(StrConv(Mid([Athlete],InStrRev([Athlete]," ")+1),3)) AS Surname, StrConv([Athlete],3) AS [Full Name], AthleteNames.Country, AthleteNames.Athlete, AthleteNames.AthleteID FROM AthleteNames WHERE (((UCase(StrConv(Mid([Athlete],InStrRev([Athlete]," ")+1),3))) Like [FORMS]![Mainform1].[Form].[UUU].[CAPTION] & "*")) ORDER BY AthleteNames.Athlete;

    Best regards

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

    Re: No outpot from query (MSACCESS 2003)

    It looks OK, if you're sure that the caption is always in upper case. As usual, I'd have to see the database if you want further assistance.

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

    Re: No outpot from query (MSACCESS 2003)

    Thanks Hans. It is strange as when the rowsource is hardcoded it works. The reason I am trying to make it external is because when the form opens its asks for a parameter value of UUU.Caption. I tried putting in a * in the labels caption but it still asks. Actually, while I am wring I realise the control L3 is on the mainform, and the label is on the subform. As the subform has not yet opened it may be the reason its unhappy. I will move the label onto the mainform, could be the answer. Anyway thanks for your appreciated help as ever. 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
  •