Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    43

    Red face Unanswered: Multi-criteria search form

    I just finished my first multi-criteria search form. Unfortunately I cant get it to work right.
    The fields (Expert/Executive/Communications) of the underlying table (Contacts) Im using as basis for my search are checkboxes. I assume this is why I run into a data type mismatch error on the following line of code

    Me.lblStats.Caption = DCount("*", "Contacts", SQLWhere) & " / " & DCount("*", "Contacts")

    Could someone have a look and let me know what Im doing wrong?
    Many thanks in advance !!

    P.S Copy of Database attached

    Chiron

    Private Sub RefreshQuery()
    Dim SQL As String
    Dim SQLWhere As String

    SQL = "SELECT OrganisationID, ContactID, FirstName, LastName, EmailName, Title, Board, Communications, Executive, Expert FROM Contacts Where Contacts!ContactID <> 0 "

    If Not Me.chkExp Then
    SQL = SQL & "And Contacts!Expert = '" & Me.txtSrchExp & "' "
    End If
    If Not Me.chkexec Then
    SQL = SQL & "And Contacts!Executive = '" & Me.txtSrchExec & "' "
    End If
    If Not Me.ChkTech Then
    SQL = SQL & "And Contacts!Technical = '" & Me.txtSrchTech & "' "
    End If
    If Not Me.chkCom Then
    SQL = SQL & "And Contacts!Communications = '" & Me.TxtSrchCom & "' "
    End If
    If Not Me.chkBoard Then
    SQL = SQL & "And Contacts!Board = '" & Me.TxtSrchBoard & "' "
    End If

    SQLWhere = Trim(Right(SQL, Len(SQL) - InStr(SQL, "Where ") - Len("Where ") + 1))

    SQL = SQL & ";"

    Me.lblStats.Caption = DCount("*", "Contacts", SQLWhere) & " / " & DCount("*", "Contacts")
    Me.lstResults.RowSource = SQL
    Me.lstResults.Requery

    End Sub
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Post your base in Access 97 format.....
    Saludos
    Norberto

  3. #3
    Join Date
    Nov 2003
    Posts
    43
    Ok here it is in 97 format ! Thx for having a look Norberto
    Attached Files Attached Files

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Originally posted by Chiron
    Ok here it is in 97 format ! Thx for having a look Norberto
    The problem is the field Contacts!Board = in the sentence SQLWhere =
    return and incorrect value, must be True or False. Run the code and see step by step that sentence.
    Saludos
    Norberto

  5. #5
    Join Date
    Nov 2003
    Posts
    43
    Thx for having a look, Norberto!!

    I did a quickwatch on the SQLWhere and it returned that:

    Watch : : SQLWhere : "Contacts!ContactID <> 0 And Contacts!Expert = ' '" : String : Form_frmSearchMember.RefreshQuery

    so I changed
    If Not Me.chkExp Then
    SQL = SQL & "And Contacts!Expert = '" & Me.txtSrchExp & "' "
    End If

    by If Not Me.chkExp Then
    SQL = SQL & "And Contacts!Expert like '*" & Me.txtSrchExp & "*' "
    End If

    but then whatever I type in as selection criteria I thought it would be yes or no since Contacts!Expert is a check box it tells me the value is out of context.

    Don't know what to do next ?

  6. #6
    Join Date
    Nov 2002
    Posts
    49
    Chiron,

    Instead of
    SQL = SQL & "And Contacts!Expert = '" & me.txtSrchExp & "' "

    try
    SQL = SQL & "And Contacts!Expert = & Me.txtSrchExp

    What you have is trying to set a check box = to a text value. That will give you a type mismatch error. In Access, True/False, Yes/No controls are represented by 0 and -1's (numbers, not text). If this does not help, look at the txtSrchExp control and ensure that it is a check box. If not, you may have to create an if block to determine how the control interacts with the SQL statement:

    if txtSrchExp =<value> then
    SQL=SQL & "True"
    else
    SQL=SQL & "False"
    end if

    I have used this method when dealing with a combo box with T/F values.

    Hope this helps a little.

  7. #7
    Join Date
    Nov 2003
    Posts
    43

    Wink

    Thanks Saundone !! I tried removing the single quotes as you suggested but I'm running into a 'Syntax error (missing operator) in query expression "Contacts!ContactID <> 0 And Contacts!Expert = & Me.txtSrchExp"

    I will try your if block solution and see

    Thanks

    Chiron

  8. #8
    Join Date
    Nov 2002
    Posts
    49
    Chiron,

    If your code reads exactly as you posted it:

    Contacts!ContactID <> 0 And Contacts!Expert = & Me.txtSrchExp

    If this is how it reads, you have to add a " after the =:

    Contacts!ContactID <> 0 And Contacts!Expert = " & Me.txtSrchExp

    This way, your SQL statement will be written as follows:

    Contacts!ContactID <> 0 And Contacts!Expert = < The value in Me.txtSrchExp>

    If you still have trouble, place a break point on the line where you execute the SQL statement. In the immediate window, print the SQL statement, then onpe a query in design view. Do not add tables, and instead change to SQL view. Paste the SQL statement into the query and debug it that way. When you have it corrected, what ever you had to do to allow it to run, make those changes in your code. I create most of my querys this way. I build using a query design, place my criteria, then seperate the SQL statement in code (using literal values in controls or variables as needed).

    Hope this helps.

  9. #9
    Join Date
    Nov 2002
    Location
    Melbourne
    Posts
    92
    here a sample of a multicriteria search sample that you might mind useful. I know i did.
    Attached Files Attached Files

Posting Permissions

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