Hi all,

How can I do a query based on a form?
I have a queryform and I pass parameters to my query.
If I leave a textboxes blank in my queryform then query must return all records from my table.

How can I do this?

Thanks in advance
-vemaju

__________________
-vemaju

Vemanju,

How many parameters?

Kevin

dartcoach

Hi

I have about 8-12 parameters

Vemaju,

What happens when you run the query? Do you bring the results back to the screen or use the results for a report?

I'm thinking you can dynamically build the sql statement based on which parameters the user filled in.

Can you post the query sql? That way I can see what you have now.

Kevin

dartcoach

Hi Kevin

It should be something like this

SELECT Customers.CompanyName, Customers.ContactName, Customers.ContactTitle
FROM Customers
WHERE (((Customers.CompanyName)=[Forms]![Customers]![CustomerID]) AND ((Customers.ContactName)=[Forms]![Customers]![CompanyName]) AND ((Customers.ContactTitle)=[Forms]![Customers]![ContactTitle]));

-vemaju

Hi Guys,

A quick way to do this is to use the NZ function. e.g.,

SELECT Customers.CompanyName, Customers.ContactName, Customers.ContactTitle
FROM Customers
WHERE (((Customers.CompanyName) Like NZ([Forms]![Customers]![CustomerID],'*')) AND ((Customers.ContactName) Like NZ([Forms]![Customers]![CompanyName],'*')) AND ((Customers.ContactTitle) Like NZ([Forms]![Customers]![ContactTitle],'*')));

Then whenever you have a null text box the query uses the wild card instead.

Any good to you?

Jon

Cool, that's much better than dynamically creating the sql.

Kevin

dartcoach

Hi all,

Thank you for good and quick answer.
I have tried NZ-function, but I had word LIKE in wrong place.

-vemaju