Another Access 2003 / VBA request

viceTue 28/10/08 17:05

Another Access programming query for you. I'm learning as I go and a complete newby.

I have a database table of various records. I'm trying to create a user-friendly query, so that Mrs Miggins down the road can find records using various criteria which she specifies.

For example, lets say my database records the shopping habits of various people.

I have records of:

Name of person going shopping
Which Supermarket they go to
Which County the Supermarket is in
Which Town the Supermarket is in
What type of fruit they decided to buy.

I've created a search form, and linked this to the various underlying tables - so, for example I have a combo box listing Tesco, Morrisons, Asda etc, which is linked to the main table. My member of staff can select Supermarket as a search criteria.

In the 'Criteria' section of the accompanying query, therefore, I've used =[Forms]![Search Form]![Supermarket] to enable the query to look at what my member of staff has selected.

It works when I have one criteria, or when all of the search boxes are filled in. But it comes back with no results if some of the seach boxes aren't filled in.

So, for example, if we wanted to list people who visited Asda in Birmingham in the West Midlands and who bought Apples I could do. But if I wanted to leave everything else blank and just see people in Birmingham, I couldn't.

Is there a AND / OR thing I could stick in the Criteria box which means that the query will return proper results if I only enter a couple of search items?

I don't understand this post myself, and I'm the one who's writing it...

duncanTue 28/10/08 17:57

You know, at one time I earned money by designing and developing Access/VBA applications...

I have since forgotten everything I knew tho! However I always used to use SQL to do DB searches, and passed the values in from the VBA forms (SQL being a much more powerful method to search for data).

In theory what you want is possible - but I honestly can't remember how to do it!

(God my brain is useless these days - unless it's doing 'Do Chip' it just doesn't work :S)

viceWed 29/10/08 11:04

Thanks Duncan.

Maybe one option is to create macros to do the following

Run Query for Field One, then
Run Seperate Query for Field Two
etc, then
Merge results of queries
Check for Duplication,
Remove duplication Display results.

duncanWed 29/10/08 11:16

quoting > "Run Query for Field One, then
Run Seperate Query for Field Two
etc, then
Merge results of queries
Check for Duplication,
Remove duplication Display results."

That is an option, but it's overly complicated really.

If it was me designing the form/search, I'd have something like:

GET * FROM table WHERE SHOP = Form.shop AND LOCATION = Form.location

The Form.shop would return the value selected from the Shop dropdown box, and probably a '*' for "All" (that will return everything in the DB if my SQL is still working)

Like I said tho, its been a while (and I never really liked the in-built Forms/Search things that Access gave you for the tables - but that was just me probably).

No doubt someone who has done this recently will tell you a better way!

viceWed 29/10/08 11:28

Thanks Duncan, I'll try the *. It's only returning results when I input something into a specific combo box.

*mutters and shakes fist at machine*