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

    SQL Syntax (MS ACCESS 2003)

    Can someone put me out of my misery. I have tried so many combinations to the end of this line to add an "*", so it will fetch all records starting with a leading character. Many thenks

    MySql = MySql & " WHERE AthleteNames.Athlete Like Forms!Mainform1!Subform1.Form.NewName.Text"

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

    Re: SQL Syntax (MS ACCESS 2003)

    <code>MySql = MySql & " WHERE AthleteNames.Athlete Like Forms!Mainform1!Subform1!NewName & '*'"</code>

    You shouldn't use NewName.Text, just NewName.

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

    Re: SQL Syntax (MS ACCESS 2003)

    Many thanks Hans, however still have a problem.

    At first there was no activity when typing in text into the textbox.

    I then modified the line to include Form

    MySql = MySql & " WHERE AthleteNames.Athlete Like Forms!Mainform1!Subform1!Form.NewName & ' *' "

    When I type into the textbox it opens an enter parameter value dialogue - Forms!Mainform1!Subform1!Form.NewName

    Both my form names are correct, and the textbox control. I am using the query in the textbox onchange event??

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

    Re: SQL Syntax (MS ACCESS 2003)

    Are you using this in the On Change event of the NewName text box? If so, try this:
    <code>
    MySql = MySql & " WHERE AthleteNames.Athlete Like " & Chr(34) & Me!NewName.Text & "*" & Chr(34)
    </code>
    This includes the text currently entered in the NewName box in the SQL.

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

    Re: SQL Syntax (MS ACCESS 2003)

    Thankyou Hans, that worked! Is this because it's in the onchange event of the ccontrol? In the past I have normally put the contents of a textbox into a label caption and used the caption content in the query. Best regards

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

    Re: SQL Syntax (MS ACCESS 2003)

    In the On Change event of a text box, you must use the .Text property to retrieve the currently entered text, but this property is not available in SQL. So you must concatenate with the literal value.

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

    Re: SQL Syntax (MS ACCESS 2003)

    Many thanks Hans for the explanation, and getting it to work. Very much appreciated, regards

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

    Re: SQL Syntax (MS ACCESS 2003)

    Maybe this should be on a seperate thread. The sql I have been working on selects the first part of a name. I know if I put a "*" at the front and end I get a match to whats between the two *'s. Is there any way to build a split function in that would select the first part again of the name - bt from after a last space in a name.Basically working as a search for a surname. Names without a surname being ignored.

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

    Re: SQL Syntax (MS ACCESS 2003)

    Sorry, I'm confused. You mention "the first part of a name", then "from after a last space in a name". What exactly do you mean?

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

    Re: SQL Syntax (MS ACCESS 2003)

    Sorry for confusion.
    If I had a table with names in:

    Peter Taylor
    Roger Andrew Smith
    Hamilton

    And had a search textbox where I entered T, Ta etc it would find Peter Taylor
    If entering S, or Sm it would find Roger Andrew Smith
    It could or ignore typing in H or Ha to select Hamilton as that would be found with a normal searxh.

    Hope thats clearer. Thanks again

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

    Re: SQL Syntax (MS ACCESS 2003)

    Try
    <code>
    MySql = MySql & " WHERE Mid(AthleteNames.Athlete,InStrRev(AthleteNames.Ath lete,' ')+1) " & _
    "Like '" & Me!NewName.Text & "*'"
    </code>
    The InStrRev function is used to determine the position of the last space in the name; it returns 0 if there is no space.

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

    Re: SQL Syntax (MS ACCESS 2003)

    Thank you Hans, perfect!! Do you ever get to sleep? Best regards

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

    Re: SQL Syntax (MS ACCESS 2003)

    > Do you ever get to sleep?

    Yes, but only when you're not watching! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

Posting Permissions

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