hi, when executing this string, it does not work because contain error syntax . Where is the error?

stLinkCriteria = "SELECT * FROM numsist WHERE ((funz1 = [Forms]![ricercafunzioni]![fun1]) And (funz2 = [Forms]![ricercafunzioni]![fun2]) And (funz3 = [Forms]![ricercafunzioni]![fun3]) And (funz4 = [Forms]![ricercafunzioni]![fun4]) And (funz5 = [Forms]![ricercafunzioni]![fun5]));"

thanks

You're close on what you need already - you just need to modify how
the string is handled, plus it also depends on the type of data
field(s) being used for each value.

Here's how it would look if funz1, funz2, etc were text fields:

stLinkCriteria = "SELECT * FROM numsist WHERE ((funz1 = '" & [Forms]![ricercafunzioni]![fun1] & "') And (funz2 = '" & [Forms]![ricercafunzioni]![fun2] & "') And (funz3 = '" & [Forms]![ricercafunzioni]![fun3] & "') And (funz4 = '" & [Forms]![ricercafunzioni]![fun4] & "') And (funz5 = '" & [Forms]![ricercafunzioni]![fun5] & "'));"

In this example, the values for fun1 through fun5 from your form will be dynamically substituted into your query string. If on the form fun1 = a, funb = b, etc, here's what your link criteria will look like when it gets passed to the form/report you are trying to open:

stLinkCriteria = "SELECT * FROM numsist WHERE ((funz1 = 'a' And (funz2 = 'b') And (funz3 = 'c') And (funz4 = 'd') And (funz5 = 'e'));"

Just remember that if the field is numeric do not include
any delimiter, if the field is a date, use the pound sign (#),
and if the field is a string/text like in this example, use
the single quote.

Setting up the numeric field string would like like this:

stLinkCriteria = "SELECT * FROM numsist WHERE ((funz1 = " & [Forms]![ricercafunzioni]![fun1] & ") And (funz2 = " & [Forms]![ricercafunzioni]![fun2] & ") ...

And setting up the date field string would like this this:

stLinkCriteria = "SELECT * FROM numsist WHERE ((funz1 = #" & [Forms]![ricercafunzioni]![fun1] & "#) And (funz2 = #" & [Forms]![ricercafunzioni]![fun2] & "#) ...

And, of course, you can have any combination of the three depending
on your field types.

Hope that helps!


Warren
:D

My fields in this table are checkbox (yes/no or true/false)

F

I am assuming that the checkboxes on your form are bound to Yes/No fields within an underlying table. The reason that I mention this is that Yes/No or True/False fields will store as a 0 or -1 within a table - if these were not bound to field(s) within a table, you could have a possible logic error during the link because an unbound or non-initialized checkbox would default with a value of NULL until a user clicks on it or it has been defaulted to False via code. You're code won't blow up, but you most likely will not get the results you're looking for.

Here is what you would need to do for your link string:

stLinkCriteria = "SELECT * FROM numsist WHERE ((funz1 = " & [Forms]![ricercafunzioni]![fun1] & ") And (funz2 = " & [Forms]![ricercafunzioni]![fun2] & ") And (funz3 = " & [Forms]![ricercafunzioni]![fun3] & ") And (funz4 = " & [Forms]![ricercafunzioni]![fun4] & ") And (funz5 = " & [Forms]![ricercafunzioni]![fun5] & "))"

In this case, it's just as if you had used numeric fields because of the actual data type that is stored for Yes/No fields in Access.

The reason that I am pointing this checkbox issue out is that this is a very common error is Access apps because Yes/No fields can only contain a value of True (-1) or False (0) in MS-Access/Jet. The default for this column data type in Access is False, but this can vary on other database platforms. MS SQL Server 2000 has a similar column data type named "bit" which is actually capable of taking on values of True, False, and NULL. Also, for any other data type (number, text, date, etc), you should always check for NULL values in your form. For example, say you have a field named StartDate on your form which is bound to a field of the same name within your table. Normally, one may specify the following link criteria string:

stLinkCriteria = "SELECT * FROM numsist WHERE StartDate = #" & [Forms]![ricecafunzioni]![StartDate] & "#"

As long as there is a non-null value in the StartDate field on your form, things should be okay for your link. When checking for a NULL value in a column within a table, however, you cannot specify a
value being equal to NULL, like "StartDate = NULL". If you want
to check for a NULL value, it needs to be changed to the following:

stLinkCriteria = "SELECT * FROM numsist WHERE StartDate Is Null"

By combining the two parts of this example, here's how you would code it in your form:

If IsNull([Forms]![ricecafunzioni]![StartDate]) Then
   stLinkCriteria = "SELECT * FROM numsist WHERE StartDate Is Null"
Else
   stLinkCriteria = "SELECT * FROM numsist WHERE StartDate = #" & [Forms]![ricecafunzioni]![StartDate] & "#""
End If

It is possible, however, to "default" NULL checkboxes to False as a "safety net" before trying to use them within a link criteria string by doing something like the following:

If IsNull([Forms]![ricecafunzioni]![fun1]) Then
   [Forms]![ricecafunzioni]![fun1] = False
End If

And you can do the same for the other 5 controls.

As a little FYI in case you didn't know about it already, if you're calling the OpenForm from within the ricecafunzioni form, you can substitute the "Me" keyword for the "[Forms]![ricecafunzioni]" specification like this:

If IsNull(Me.fun1) Then
   Me.fun1 = False
End If

The "Me" keyword is used to refer to the current form or report you're in as long as you're using the code-behind-forms or reports in your situation.

I hope this info and the additional stuff can be helpful - being able to keep NULL checking in mind can save you oodles of hours of grief in the future when setting up these link criteria strings.

Warren