Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    14

    Unanswered: case statement with an sql query statement

    a case statement in VB is ment for a string or numeric expression. if i place a sql parameter query statement it shows type mismatch. what do i do????

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: case statement with an sql query statement

    Originally posted by ONIL
    a case statement in VB is ment for a string or numeric expression. if i place a sql parameter query statement it shows type mismatch. what do i do????
    can you show the statement you are trying to execute? Or a better understanding of what you are after?

  3. #3
    Join Date
    Feb 2004
    Posts
    14

    further expalnation

    ok here's the issue
    i have the following: -
    1. a access table: telephone_directory (fields are first name, last name, extension_no, building_name)
    2. a form with menu find and sub menus "by extension number", "by first name", "by last name". ALSO another form named PF with a text box and a listbox

    3. a sql parameter query as: -
    cq.SQL = "PARAMETERS something1 INTEGER; SELECT * FROM TELEPHONE_DIRECTORY" & _
    " WHERE LEFT(EXTENSION_NO,1) LIKE [something1] " & _
    " OR LEFT(EXTENSION_NO,2) LIKE [something1] " & _
    " OR LEFT(EXTENSION_NO,3) LIKE [something1] " & _
    " OR LEFT(EXTENSION_NO,4) LIKE [something1] " & _
    " OR LEFT(EXTENSION_NO,5) LIKE [something1] " & _
    " OR LEFT(EXTENSION_NO,6) LIKE [something1] " & _
    " OR EXTENSION_NO LIKE [something1] " & _
    " ORDER BY EXTENSION_No; "
    4. a bas file with the parameter query wherein case 1 is for extension number case 2 is for last names wherein the sql query is : -

    cq.SQL = "PARAMETERS something1 text; SELECT * FROM TELEPHONE_DIRECTORY" & _
    " WHERE LEFT(LAST_NAME,1)LIKE [something1] " & _
    " OR LEFT(LAST_NAME,2)LIKE [something1] " & _
    " OR LEFT(LAST_NAME,3)LIKE [something1] " & _
    " OR LEFT(LAST_NAME,4)LIKE [something1] " & _
    " OR LEFT(LAST_NAME,5)LIKE [something1] " & _
    " OR LEFT(LAST_NAME,6)LIKE [something1] " & _
    " OR LAST_NAME LIKE [something1] " & _
    " ORDER BY LAST_NAME; "
    ..... and so on

    5. now how do i call for the "case" in the PF form so that the text box takes the input and listbox displays result for all types of find. I am succesful with different forms for each FIND but i want to use ONLY ONE form.

  4. #4
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Since it's an Access database I don't know if IIf() or Switch() would help at all.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

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