Web Development

Ask something
Discussion Locked

Access Query

0 helpful
5 replies

by choppit

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

5 replies

by rrv

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

by avhohlov

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

This discussion is closed to new replies.