Alpha Video Training
Results 1 to 9 of 9

Thread: Queries

  1. #1
    John Gagnon
    Guest

    Default Queries

    Trying to put a query on a button with a varible. I created the query using the query toolbar icon and used the script recorder. Transferred it to my button and it works fine. Below is the query.

    tbl = table.current()
    query.description = ""
    query.order = ""
    query.filter = "SMATCH((VOLUNTER->CODES),("*bull*"))"
    query.options = "I"
    tbl.query_create()
    :Vol1.fetch_first()

    I am trying to sub ("*bull*") with a varible. If I use xbasic code toolbar button it uses "*bull*" ( all it does is remove the back slashes). When it run my form and use the button it errors and says incorrect data type. If the backslashes are left in it works.

    Any help would be appreciated.

    Thanks

  2. #2
    "Certified" Alphaholic
    Real Name
    William Hanigsberg
    Join Date
    Apr 2000
    Location
    Toronto, ON
    Posts
    4,018

    Default RE: Queries

    John,

    I really don't understand your example but one problem sticks right out. A query filter must be a logical expression, one which is true or false for each record, such as
    "Lastname='Jones' "
    and yours
    query.filter = "SMATCH((VOLUNTER->CODES),("*bull*"))"
    is not (unless there is a typo).

    "I am trying to sub ("*bull*") with a varible. If I use xbasic code toolbar button it uses "*bull*" ( all it does is remove the back slashes)."

    I find this passage opaque. Is "bull" a variable? Is the "*" character being used as a wildcard? What removes the backslashes from where?

    Bill

  3. #3
    John Gagnon
    Guest

    Default RE: Queries

    Bill,
    Thanks for your reply.

    My field name is CODES and in some of the records it will contain the phrase "em,bull,choir". I am searching the field for the expression bull.

    Using the script recorder it gave me the following result,

    :Vol1.activate()
    tbl = table.current()
    query.description = ""
    query.order = ""
    query.filter = "SMATCH((VOLUNTER->CODES),("*bull*"))"
    query.options = "I"
    tbl.query_create()

    When I put this code into my button and looked at the code in xbasic it had removed the two back slashes.

    My intention is to replace *bull* with a varible. The problem with replacing it with a varible is the usage of quotes.

    Thanks

  4. #4
    John Gagnon
    Guest

    Default RE: Queries

    Bill,
    I forgot to tell you that the "*" is being used as a wildcard.

    thanks

  5. #5
    "Certified" Alphaholic
    Real Name
    William Hanigsberg
    Join Date
    Apr 2000
    Location
    Toronto, ON
    Posts
    4,018

    Default RE: Queries

    Well John,

    I've just managed to show everyone that I forgot how smatch works. The expression you quote (from the genie) is logical after all which is why it works.

    So all you want to do is replace the literal "*bull*" with a variable (e.g. vString). There are two issues here
    -the quotes
    -get the variable to work correctly in this context

    as for the quotes
    "+vString+"
    is probably right and if it isn't Tom Cone will tell us.

    Getting the variable to work might require some experimentation. You do not want to search for the literal "*bull*" so you may need to use the eval() function:
    eval("+vString+")

    If we're lucky someone has already worked this out. If not you'll have to fiddle with it.

    Bill

  6. #6
    John Gagnon
    Guest

    Default RE: Queries

    Bill,
    This query filter works (see above e-mails.
    query.filter = "SMATCH((VOLUNTER->CODES),("*bull*"))"

    This query filter does not work
    query.filter = "SMATCH((VOLUNTER->CODES),("*bull*"))"

    The error that I get is
    Argument is incorrect data type.

    Thanks again!

  7. #7
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,957

    Default RE: Queries

    here is a snippet that I use acting on user data

    dim temp as c
    temp=upper(ui_get_text("Enter any portion of Serial Number",""))
    if temp=""
    end
    end if
    temp="*"+temp+"*"
    ha=table.open("hearing aids")
    query.filter="smatch(serial_number,var->temp)"

    I could also add temp="*"+temp+"bull"+"*"

    ps:did you get your smatch filter from the script recorder? Thats where I got mine about, 3 years ago.

  8. #8
    Mick Magnuson
    Guest

    Default RE: Queries

    Hi John
    If you are using smatch on a button, try

    query.filter = "SMATCH((VOLUNTER->CODES),'*bull*')"
    Note the apostrophies before and after BULL

    I use smatch all the time, just have to use apostrophies instead of quotation marks. Substitute them in scripts where in other operations would require quotation marks.

    for variables with smatch

    "SMATCH((VOLUNTER->CODES),'*'+ALLTRIM(VAR->VARNAME)+'*')"

    Mick

  9. #9
    John Gagnon
    Guest

    Default RE: Queries

    Bill, Martin, Mick,

    After trying what you had suggested this is the result,

    "SMATCH((VOLUNTER->CODES),('*'+var->qry+'*'))"

    This works. qry is the name of my varible. The problem seemed to be using "var->qry", I was not familiar with the "var->" concept.

    Thanks for all the help.

    Is there a step below newbie?

    Thanks

Similar Threads

  1. SQL Queries
    By Sonia Sidhwaney in forum Web Application Server v6
    Replies: 6
    Last Post: 07-15-2005, 06:44 AM
  2. queries
    By Mike Finnerty in forum Alpha Five Version 5
    Replies: 1
    Last Post: 07-18-2003, 02:07 PM
  3. Migrating Queries
    By William R Schone in forum Alpha Five Version 5
    Replies: 1
    Last Post: 07-03-2003, 05:50 AM
  4. Help with queries
    By Jamin Dunivan in forum Alpha Five Version 4
    Replies: 8
    Last Post: 04-24-2001, 06:26 PM
  5. Queries
    By Frank Cozzi in forum Alpha Five Version 4
    Replies: 1
    Last Post: 01-20-2001, 12:53 PM

Bookmarks

Posting Permissions

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