Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    SQL statement for first letter (VB6, w2000, office xp)

    I have a list box that has a through z in it, lstAlpha. I've added a thru z in the lstAlpha through additem.

    When I click on a letter in lstAlpha, I want my flexgrid (flxClient) to populate with the client's name that have that first letter in their name from my tblClient.

    dim rs as new ADODB.recordset
    "SELECT ClientName FROM tblClient WHERE left(ClientName, 1) = lstAlpha.Text ORDER by ClientName"
    set flxClient.DataSource = rs
    rs.close

    I get an error about wrong parameters. I think i'm not putting the right quotes or single quotes in the SQL stmt. Can someone lead me down the correct path. Thanks a log

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL statement for first letter (VB6, w2000, office xp)

    What's your back end? SQL Server? If so, I don't think there's a Left function. Try Substring instead.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SQL statement for first letter (VB6, w2000, office xp)

    or maybe LIKE a*, LIKE z*, etc. (which I have used in a web page tapping an Access database).

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL statement for first letter (VB6, w2000, office xp)

    Regardless of what backend you're using, you've left out some important steps. You dimmed your recordset object, but that's all you've done. You have to set its connection property and then open it, either directly or by using an execute on a command or connection object. Once the recordset actually contains records, you can assign it to the datasource of the flexgrid.

    I don't know why you're bothering to use a flexgrid though with a straight SQL statement like that. Without a hierarchical recordset, a flexgrid just looks like a datasheet.
    Charlotte

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SQL statement for first letter (VB6, w2000, office xp)

    Hi,
    In addition to Charlotte's points, I think you need to change your SELECT statement to:
    "SELECT ClientName FROM tblClient WHERE left(ClientName, 1) = '" & lstAlpha.Text & "' ORDER by ClientName"
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL statement for first letter (VB6, w2000, office xp)

    thanks rory. that worked. it was my single and double quotes. Could you let me know the rationale behind the use of single quotes. thanks

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SQL statement for first letter (VB6, w2000, office xp)

    Hi,
    The single quotes are there because you're passing a string variable and I use them in preference to double quotes partly because that is, I believe, the ANSI/ISO standard (Oracle certainly doesn't seem to like double quotes) and partly because I get confused trying to put double quotes within quoted strings (I think it's """ to put " inside a string that's already in quotes?)
    In this case you also needed to take the controlname.text bit out of the SELECT statement so that it gets evaluated - otherwise you're including the words "controlname.text" in the statement rather than passing whatever the control's text actually is, if that makes sense?
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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