Results 1 to 5 of 5
  1. #1
    Join Date
    May 2013
    Posts
    3

    Unanswered: Sql query where 2 or more reuirements are to be fulfil

    Good day,
    I am working on searching a particular recordlist from a database.
    To select from a database (declared to be db) where a requirement is fulfill (say !school = txtschool), the syntact is like this:

    set rs=db.openrecordset("select *from table1 where !school=" & txtschool)


    I am having problem on how to query a if two or more requirement is needed
    e.g

    I query it like this:

    Set re = db.OpenRecordset("select *from table1 where !dept=" & cbodept.Text And "select *from table1 where !level=" & cbolevel.Text)

    It continuously giving me error

    Run-time error'3061':
    Too few parameters.Expected 2.

    In other similar project, the error is -

    Run-time error'3061':
    Too few parameters.Expected 1.


    Please help me out.
    Last edited by Burrosheeed; 05-30-13 at 02:45.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well the reason its complaining is that your SQL is garbage
    Code:
    Set re = db.OpenRecordset("select *from table1 where !dept=" & cbodept.Text And "select *from table1 where !level=" & cbolevel.Text)
    what I'd do is something like
    Code:
    dim strWhereClause as string
    strWhereClause  = " where 1=1"   'use a dodge to make certain we always have a valid where clause
    'test we have a department number selected in the combo box
    if not isnull(cbodept) and len(cdodept)>0 then ' and if so add it to the where clause
      strWhereClause= strWhereClause & " And dept = cbodept"
    endif
    
    'test we have some information in the textbox control
    if not isnull(txtschool) and len(txtschool)>0 then 'and if so add that to the where clause
      strWhereClause= strWhereClause & " AND school = '" & txtschool & "'"
    endif
    Set re = db.OpenRecordset("select * from table1" & strwhereclause)
    NOTE stylisticall I alwasy start a fragemnt of SQL with a space eg " AND" or " WHERE", SQL usies spaces to separate words. its probably the error your code is complaining about

    it shoudl read "* from" not "*from"
    the above is air code, not tested so you may need to debug it to make it work
    its also assume that dept is numeric and school is text/string
    text values MUST be enclosed in quotes
    eg: where school = 'myschoolname'
    this is so the SQL engine knows where the text values starts and stops, numbers don't need quoting and dates need to be delimted by #
    eg: where myDateColumn = #2013/06/04#
    use the ISO date form as that is a standard in SQL and means your code is portable
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2013
    Posts
    3
    Thanks @ healdem . But, I adopted dsame format u used, it ran but yet all d whole record of d referred database is selected and not d specified records.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please post the VB including the SQL that you used.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    May 2013
    Posts
    3
    Thanks,
    There is a ready collated database of fields ; Name, School, Dept, Level etc.
    I'm now working on creating a search form to the software. To search, Name, School, Dept and/or level can be specified. E.g Searching for all CSC department 400 level students list from the general database to be saved in the search table of the database.

    Problem Identification;
    After specifying the needs, how can this be queried ?

    Here is my faulty code, Help me out:

    Dim db As Database
    Dim rs As Recordset
    Dim re As Recordset
    Dim k As Integer
    Dim Record(1 To 11) As String

    k = 0

    Set db = OpenDatabase(App.Path & "\mssnfutadb.mdb")
    Set rs = db.OpenRecordset("select *from tblrecord")

    With rs
    If (.RecordCount = 0) Then
    GoTo 10
    Else
    Do Until .EOF
    .Delete
    .MoveNext
    Loop
    End If
    End With


    10
    If Form3.cboschool.Text = "All" Then
    If cbodept.Text = "All" Then
    If cbolevel.Text = "All" Then
    Set re = db.OpenRecordset("select *from tblgeneral")
    GoTo 20
    Else
    'Set re = db.OpenRecordset("SELECT * FROM tblgeneral WHERE Level =" & cbolevel.Text)

    End If

    Else
    If cbolevel.Text = "All" Then
    Set re = db.OpenRecordset("select * from tblgeneral where dept=" & cbodept.Text)
    Else
    Set re = db.OpenRecordset("select * from tblgeneral where dept=" & cbodept.Text And "select *from tblgeneral where level=" & cbolevel.Text)
    End If
    End If
    Else
    If cbodept.Text = "All" And cbolevel.Text = "All" Then
    Set re = db.OpenRecordset("select * from tblgeneral where school=" & Form3.cboschool.Text)
    ElseIf cbodept.Text = "All" Then
    Set re = db.OpenRecordset("select * from tblgeneral where school=" & Form3.cboschool.Text And "select from tblgeneral where level=" & cbolevel.Text)
    ElseIf cbolevel.Text = "All" Then
    Set re = db.OpenRecordset("select * from tblgeneral where school=" & Form3.cboschool.Text And "select from tblgeneral where dept=" & cbodept.Text)
    Else
    Set re = db.OpenRecordset("select * from tblgeneral where school=" & Form3.cboschool.Text And "select from tblgeneral where level=" & cbolevel.Text And "select *from tblgeneral where dept=" & cbodept.Text)
    End If
    End If


    20
    With re

    If (.RecordCount = 0) Then
    MsgBox "No record found in the database!", vbInformation + vbOKOnly, "No record"
    Exit Sub

    Else
    Do Until .EOF
    Record(1) = !Name
    Record(2) = !phoneno
    Record(3) = !School
    Record(4) = !dept
    Record(5) = !Level
    Record(6) = !futaadd
    Record(7) = !email
    Record(8) = !paddr
    Record(9) = !nekname
    Record(10) = !nekphone
    Record(11) = !nekaddr

    With rs
    k = k + 1
    .AddNew
    !SN = k
    !Name = Record(1)
    !phoneno = Record(2)
    !School = Record(3)
    !dept = Record(4)
    !Level = Record(5)
    !futaadd = Record(6)
    !email = Record(7)
    !paddr = Record(8)
    !nekname = Record(9)
    !nekphone = Record(10)
    !nekaddr = Record(11)
    .Update

    End With

    .MoveNext
    Loop
    End If

    End With

Posting Permissions

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