Thursday, March 29, 2012

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??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?|||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.|||Since it's an Access database I don't know if IIf() or Switch() would help at all.sql

No comments:

Post a Comment