Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Feb 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS Access SQL- Find any portion of string

    after a user enters a bit of data, i'm trying to see if the data already exists in the field. i have built the SQL on the fly for my queries and got it to work with check boxes and exact string matches, but am having trouble getting portions of strings to match.

    example: user inputs L Rear
    and say the entry field is called txt_entry

    if i already have a record with R Rear, I want the L Rear to query it out. I know that if the user enters Rear, i can use a combination of

    Like "*" & Forms!Sample.txt_entry & "*"

    to get a match. is there any way to have a match occur when any of the words in an entered field match the value of an existing record?

  • #2
    Banned
    Join Date
    Feb 2008
    Location
    Winnipeg, Canada
    Posts
    396
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Use a percent symbol as a wildcard

    For example:

    Code:
    SELECT * FROM table WHERE column LIKE '%StringGoesHere%'
    Obviously that will have to be concatenated for whatever programming language you are using, but I think the theory behind the query is enough to get you started.

  • #3
    New to the CF scene
    Join Date
    Feb 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    SELECT * FROM table WHERE column LIKE '%StringGoesHere%'


    that code partially works and was something i was able to achieve before. if i enter "Rear" and "L Rear" is already in the database, this will get flagged as a match. *Rear* matches with one of the words in "L Rear"

    the part i can't figure out is when i enter "R Rear" it will not show up as a match for the existing "L Rear"
    -that is using wildcards before and after rear doesn't make it match with a wildcard that grabs L Rear.

    Further, if i entered just "Rear" it would not show up as a match/conflict for the existing "L Rear"

    So, I am hoping to have any part of the entered string match any part of the existing string.

    thanks for the quick reply.

  • #4
    New to the CF scene
    Join Date
    Feb 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    bump. anyone?

  • #5
    New to the CF scene
    Join Date
    Sep 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think to do what you need you will have to split the user input and then do a longer sql...

    #Forms!Sample.txt_entry = "L Rear"
    Users_Words = split(Forms!Sample.txt_entry, " ")
    # User_Words is an array of 'L', 'Rear'

    sql_bit=""
    for each Word in User_Words
    sql_bit = sql_bit & "COLUMN LIKE "" " & Word & " "" OR "
    next

    #sql_bit is 'COLUMN LIKE "L" OR COLUMN LIKE "Rear" OR '
    sql_bit=left(sql_bit, len(sql_bit)-3)
    #sql_bit is 'COLUMN LIKE "L" OR COLUMN LIKE "Rear"'

    finished_sql="SELECT * FROM table WHERE " & sql_bit & " ORDER BY This_Column ASC;"


  •  

    Posting Permissions

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