Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trying to use 'LIKE' (A2k SP2, DAO)

    Hi All,
    I've got the following:

    strSQL = "SELECT [ChosenField] "
    strSQL = strSQL & "FROM qryChosen "
    strSQL = strSQL & "WHERE PKID = " & Me!lngPKID
    strSQL = strSQL & " AND [ChoiceField] Like 'TM*'"

    Which I then use in the following:

    strInsertData = "INSERT INTO tblTempAssay(a bunch of fields) "
    strInsertData = strInsertData & "SELECT (the proper fields) "
    strInsertData = strInsertData & "FROM qryAssay "
    strInsertData = strInsertData & "WHERE [PKID] = " & Me![lngPKID]
    strInsertData = strInsertData & " And [DetailsPKID] = " & Me![lngDetailsPKID]
    strInsertData = strInsertData & " And [2Point1] = -1"
    ****strInsertData = strInsertData & " And [ChosenField] = '" & strSQL & "'"
    strInsertData = strInsertData & " And [Included] = -1"

    The problem is I keep getting syntax errors.
    I do need the TM* as the data being searched is TM1, TM2, etc., but only 1 is entered per a record. (i.e. TM1 in the 1st record, TM2 in the 2nd, etc.)
    strSQL, when pasted into a query, returns the proper value.
    strInsertData works just fine with the line, preceded with asterisks, commented out.
    I've used the same syntax as I have in strInsertData before, with (seemingly) no problems.

    So please can someone point out what I'm missing or how to go about it in a different fashion?
    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trying to use 'LIKE' (A2k SP2, DAO)

    Don't you need to enclose the SELECT statement in round quotes (SELECT ......)?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Trying to use 'LIKE' (A2k SP2, DAO)

    You are confusing the SQL string strSQL with its result. To use the result, enclose strSQL in brackets, not in single quotes:<pre>strInsertData = strInsertData & " And [ChosenField] = (" & strSQL & ")"</pre>

    Note: if the query represented by strSQL returns more than one record, you'll get an error message, because [ChosenField]=something only works if something is a single value.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trying to use 'LIKE' (A2k SP2, DAO)

    As you see by the SELECT I meant round brackets, not single quotes.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Trying to use 'LIKE' (A2k SP2, DAO)

    Hi Pat,

    My reply was to Gary's original post, not to your reply - we were composing our replies at the same time, but you posted yours before I did. We were thinking among the same lines.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trying to use 'LIKE' (A2k SP2, DAO)

    Hi Pat and Hans,
    Thank you. That's just what I needed.
    Hans, there will be only 1 entry of TM* per the other limiting criteria. There will be more than an error message if there is more than 1 record. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thank you both again.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trying to use 'LIKE' (A2k SP2, DAO)

    Hi Hans

    Round quotes, I must be going troppo <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Pat <img src=/S/smile.gif border=0 alt=smile 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
  •