optional paramters to a query

[inungh]

I use following method to send parameters to query for optional
parameters.

1. Like [Forms]![YourFormName]![TextBox] & "*"

This one has a bug if the parameter is one then the query will
retrieve all the records prefix 1, lie 1, 10, 12, 103...etc

2. [Forms]![YourFormName]![TextBox] Or [Forms]![YourFormName]!
[TextBox] Is Null

This one works, but it creates a very long SQL statement when I have 8
parameteres optional and I got query too complex mesages.


I just wonder are there any other solution to pass optional parameters
to query.


Your information is great appreciated,

 

[Michel Walsh]

Make an ad hoc query: build the string of your criteria, forgetting any NULL
(since you can test before appending the data to the criteria) :

Dim sql AS String
...
sql=" SELECT ... FROM .. WHERE TRUE "
if NOT(IsNull(param1)) then sql= "sql & " AND field1 = " & param1
if NOT(IsNull(param2)) then sql = sql & " AND field2 = " & param2

... even smarter ...
sql=sql & ( " AND field3 =" + param3 )


And then, use that string as record source (form or report) or as rowsource
(list of combo box/list box), or argument (opening recordset).


In theory, that way of doing things have problem of SQL code injection, so
you should validate the values of each of your parameters. As example, if
param1 is expected to be a number, but someone supply " myFunction( ) ", the
statement may become:

"SELECT ... FROM ... WHERE TRUE AND field1=myFunction( )"


and a public function, from a standard module, myFunction( ) is called,
doing whatever it want to do, eventually harming the computer. Sure,someone
has to have the possibility to write code, in the first place, but having
checked that param1 is a numerical value would add much more security to the
whole ad hoc SQL statement.


That being said, the produced SQL code will probably find an optimal plan of
execution that a general statement, full of OR, would have been totally
unable to get.


Vanderghast, Access MVP