Skip nulls in Paramarray

I am using the Excel Min function in Access with Paramarray to capture the parameters. It works OK if all of the parameters have non-null values but errors if any of them are null. How can I skip nulls?

I thought to concatenate any non-nulls in a string and pass that as the paramater but it doesn't work.

Any ideas?

Thanks

Mike Collard

have you tryed

if YourVal = ""

To filter information instead of using a parameter, us a custom dialog box.

What kind of information is it? (dates, text, numbers)


You should set up a query and link it to the custom dialog box. Lets say you are filtering dates in the query just enter "Is Not Null" in the "date" field. This will not display empty fields. If you want to display emply fields use "Is Null" (remove the " " )

In the help section of Access type parameter and you will find information on both parameters and customer dialog boxes.

What are you using the "Min" for? (just curious)

Regards,

The values on which the Minimum function is used are contained in unbound text boxes on a form. Without going into too much detail the user may enter a value in any or all of three text boxes. I then need a procedure to calculate the minimum value of any non-null entries in those boxes. The resultant value is then used in a further calculation.

I still think the Excel Min function is the way to go and I just need some way to exclude any non-null textbox entries in the parameter e.g. I use the Min function thus Min(P1, P2, P3) to return the minimum value of P1, P2 or P3 and it works if all three contain values. But if any of the boxes has a null entry then an error occurs. Somehow I need to recreate the function each time it is called so that only non-null references are included e.g. if P2 is null then the function would be Min(P1, P3).

Have tried substituting the textbox references with a string variable that is the concatenation of any non-null references but it returns 0 (zero).

Hope the above is clear.

Mike Collard

Have you tried linking the form to a query (instead of table) and put Is Not Null in the Criteria for the field.


If Iam on the wrong track, post a sample db


Regards,

Sorted!

I used the Nz function with the valueifnull set to a ridiculously high value.

Thanks for your interest.

Mike