I just finished my first multi-criteria search form. Unfortunately I can’t get it to work right.
The fields (Expert/Executive/Communications) of the underlying table (Contacts) I’m 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
Could someone have a look and let me know what I’m doing wrong?
Many thanks in advance !!
P.S Copy of Database attached
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 & "' "
If Not Me.chkexec Then
SQL = SQL & "And Contacts!Executive = '" & Me.txtSrchExec & "' "
If Not Me.ChkTech Then
SQL = SQL & "And Contacts!Technical = '" & Me.txtSrchTech & "' "
If Not Me.chkCom Then
SQL = SQL & "And Contacts!Communications = '" & Me.TxtSrchCom & "' "
If Not Me.chkBoard Then
SQL = SQL & "And Contacts!Board = '" & Me.TxtSrchBoard & "' "
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"
SQL=SQL & "False"
I have used this method when dealing with a combo box with T/F values.
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"
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).