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

    Red face Unanswered: vb6 and access sql parameter query

    please help with this parameter query with like


    Option Explicit
    'CHANGE THE PATH AS APPROPRIATE
    Private Const TD_PATH = "D:\TELEPHONE DIRECTORY PROJECT\TELEPHONE DIRECTORY DATA FILES\OLDVER\DB3.MDB"
    Private RS_EXPERIMENT As Recordset
    Private TD As Database, RS As Recordset, CQ As QueryDef, N As Integer
    Private Sub Form_Load()
    Set TD = DBEngine.Workspaces(0).OpenDatabase(TD_PATH)
    Set CQ = TD.CreateQueryDef("")
    Set RS_EXPERIMENT = TD.OpenRecordset("SELECT * FROM TELEPHONE_DIRECTORY")
    Text1.Text = Len(RS_EXPERIMENT!LAST_NAME) 'THIS IS JUST TO TEST SOMETHING ELSE AND NOT
    List1.Text = Len(RS_EXPERIMENT!LAST_NAME) 'PART OF THE PRESENT PROJECT. FOR THIS I WILL TROUBLE LATER

    ' THE FOLLOWING WORKS!!!
    ' BUT NEEDS TO BE REFINED
    CQ.SQL = "PARAMETERS something1 STRING; 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] " & _
    " ORDER BY LAST_NAME; " ' and go on till 255???

    ' THE FOLLOWING DOES NOT WORK ??????
    ' Query input must contain at least one table or query. (Error 3067)
    ' For N = 1 To N = 255 Step 1
    ' CQ.SQL = "PARAMETERS something1 STRING; SELECT * FROM TELEPHONE_DIRECTORY" & _
    ' " WHERE LEFT(LAST_NAME,N)LIKE [something1] " & _
    ' " ORDER BY LAST_NAME; "
    ' Next N

    ' THE FOLLOWING ALSO DOES NOT WORK !?!?!?!?!?
    ' IT POPULATES THE LIST BOX WITH ALL RECORDS
    ' CQ.SQL = "PARAMETERS something1 STRING; SELECT * FROM TELEPHONE_DIRECTORY" & _
    ' " WHERE LAST_NAME LIKE '[something1]*' " & _
    ' " ORDER BY EXTENSION_No; "

    End Sub

    Private Sub Parameter1_Change() ' Parameter1 is the text box
    CQ![something1] = Parameter1.Text
    If RS Is Nothing Then
    Set RS = CQ.OpenRecordset()
    Else
    RS.Requery CQ
    End If
    ResultLabel.Caption = ""
    ResultListBox.Clear
    ResultCombo.Clear
    If RS.RecordCount > 0 Then
    RS.MoveFirst
    Do Until RS.EOF
    ResultLabel.Caption = "LAST NAME: - " & RS!LAST_NAME & " " & "CHARACTER COUNT : - " & Len(RS!LAST_NAME)
    ResultCombo.Text = "LAST NAME CHR COUNT: - " & Len(RS!LAST_NAME)
    ResultListBox.AddItem RS!LAST_NAME & " " & RS!FIRST_NAME & " " & RS!RANK & " " & RS!FLAT_No & " " & RS!extension_no & " " & RS!UNIT
    RS.MoveNext
    Loop
    End If
    End Sub
    Last edited by ONIL; 02-04-04 at 11: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
  •