Hi, I have one search mask I give in one access table . The code is in a click event of open a result mask; the control are checkbox fun1,fun2,fun3...funx and I would want to only select some of they for a research; for example:

fun1=yes
fun3=yes
fun5=yes

the table=numsist
the search mask=ricercafunzioni
the result mask=numsistema
the fields of table=funz1,funz2,funz3,....funzx

this is my code in a click event:

Private Sub cercarec_Click()
Dim Name As String
Dim Criteria As String
Criteria = "select * from numsist where funz1 like
        nz([forms]![ricercafunzioni]![fun1];" * ") AND funz2 like
        nz([forms]![ricercafunzioni]![fun2];" * ") AND funz3 like
        nz([forms]![ricercafunzioni]![fun3];" * ") AND funz4 like
        nz([forms]![ricercafunzioni]![fun4];" * ") AND funz5 like
        nz([forms]![ricercafunzioni]![fun5];" * ") AND funz6 like
        nz([forms]![ricercafunzioni]![fun6];" * ") AND funz7 like
        nz([forms]![ricercafunzioni]![fun7];" * ") AND funz8 like
        nz([forms]![ricercafunzioni]![fun8];" * ") AND funz9 like
        nz([forms]![ricercafunzioni]![fun9];" * ") AND funz10 like
        nz([forms]![ricercafunzioni]![fun10];" * ")"
        Name = "numsistema"
        DoCmd.OpenForm Name, , , Criteria
End Sub

this code does not work and it gives back the following error:

error 13 - type not correspondent

which it is the error?

thanks

Frank

try reform the criteria text as follows

Criteria = "select * from numsist where funz1 like '" & nz([forms]![ricercafunzioni]![fun1]) & "*' AND" & .....

Ahmed Ali
Software Developer

The problem is with the quotation marks. They're confusing Access because it doesn't know what is the string Criteria vs. what is inside the NZ function. Substitute the all quotation marks ( " ) in your NZ functions with single quotes ( ' ). That is:

Criteria = "select * from numsist where funz1 like
        nz([forms]![ricercafunzioni]![fun1];' * ') AND funz2 like
        nz([forms]![ricercafunzioni]![fun2];' * ') AND funz3 like
        nz([forms]![ricercafunzioni]![fun3];' * ') AND funz4 like
        nz([forms]![ricercafunzioni]![fun4];' * ') AND funz5 like
        nz([forms]![ricercafunzioni]![fun5];' * ') AND funz6 like
        nz([forms]![ricercafunzioni]![fun6];' * ') AND funz7 like
        nz([forms]![ricercafunzioni]![fun7];' * ') AND funz8 like
        nz([forms]![ricercafunzioni]![fun8];' * ') AND funz9 like
        nz([forms]![ricercafunzioni]![fun9];' * ') AND funz10 like
        nz([forms]![ricercafunzioni]![fun10];' * ')"

In other words, the quotes are only used to identify the string.

Criteria = " some expression ", or
Criteria = " some expression = ' some string ' "

Notice that it's ' some string ' and not " some string " because those extra " marks will confuse Access. It will think

Criteria = " some expression = " and lop off the rest of it, misinterpret it, and give you an error.

Examples of statements

Criteria = "Select * MyTable WHERE MyTextField = 'Jones'"
Criteria = "Select * MyTable WHERE MyTextField = '" & Me.txtName & "'"
Criteria = "Select * MyTable WHERE MyTextField Like '*Jones*'"
Criteria = "Select * MyTable WHERE MyNumField > 0"
Criteria = "Select * MyTable WHERE MyNumField > " & Me.txtNumber



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division