General discussion

Locked

Access Query

By Choppit ·
I have created an access query that gets its paramenters from controls on a form. This works fine unless any of the controls are left blank in which case no records are returned. How can I make the query return all record for a blank field (i.e. what value should be returned to the query parameter for that field, I've tried "" and *.

Even better, how can I make the query read its parameters from VBA variables?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access Query

by RRV In reply to Access Query

If your field is alphanumeric and if you can modify your query a little it is possible.
Instead of = use like operator in your query. In that case your client or user can put * in the text box and the query will return all records. Sample - #select* from Customer where C_Name like '*'#
if it is AS400 the * will be replaced with % symbol.

VBA can get variables or parameters using command object.

All the best
RV

Collapse -

by Choppit In reply to Access Query

Poster rated this answer.

Collapse -

Access Query

by avhohlov In reply to Access Query

Also you may generate query dinamically. Create form in which you will see result set and use DoCmd.ApplyFilter with generated condition (where clause). If parameter A is not null, apend it to condition, if A is null - don't append

Collapse -

by Choppit In reply to Access Query

Poster rated this answer.

Collapse -

by Choppit In reply to Access Query

This question was closed by the author

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums