Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005

    Unanswered: Using "Like" as a Variable in an SQL Statment

    In the example below I set a variable for m_dealer and m_opt

    In my SQL statement below I am finding all the dealers that are "Like" m_dealer, and as coded it works fine.

    m_dealer = "990201"
    m_opt = "Like"

    SQL = "SELECT * INTO tmp_r " & _
    "FROM D_RULES " & _
    "WHERE D_RULES.DEALER Like """ & m_dealer & """ "

    But what I want to do is pass the variable m_opt as a variable in place of the work "Like" in the SQL statment.

    I have tried replacing the Where in the SQL stament with the Where clause example below but it does not work

    "WHERE D_RULES.DEALER """ m_opt """ & m_dealer & """

    Can this be accomplished???????

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    but I'm not sure I understand what you are trying to achieve
    you cite
    "WHERE D_RULES.DEALER """ m_opt """ & m_dealer & """
    if m_opt = 950 & m_dealer=1
    then your where clause is WHERE D_RULES.DEALER="950""1"

    if m_opt = ABC & m_dealer=Z
    then your where clause is WHERE D_RULES.DEALER="ABC""Z"

    either option doesn't make (SQL) sense

    what are you trying to do with this clause?
    i would expect something similar to
    "WHERE D_RULES.DEALER """ m_opt "" OR D_RULES.DEALER=" & m_dealer & """

    The title of your thread suggests you want to use the like clause, but there is no use of the keyword like in your SQL. Also it is usual if you are using the like clause to see a wild card symbol * if you are using JET SQL, % if you are using ANSI SQL

    So I'm guessing that what you want is something similar to:-
    "WHERE D_RULES.DEALER like ""*" & m_opt & "*"" OR D_RULES.DEALER like""*" & m_dealer & "*""

    ie where D_RULES.DEALER contains m_opt or D_RULES.DEALER contains m_dealer

    Last edited by healdem; 12-23-05 at 04:08.

  3. #3
    Join Date
    Nov 2002
    Quote Originally Posted by paradoxtoaccess
    "WHERE D_RULES.DEALER """ m_opt """ & m_dealer & """
    If I understand correctly what you are trying to do:
    "WHERE D_RULES.DEALER " & m_opt & " """ & m_dealer & """"

    With m_dealer = "990201" and m_opt = "Like" that would produce
    WHERE D_RULES.DEALER Like "990201"
    Last edited by ivon; 12-23-05 at 03:38.

Posting Permissions

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