Parameter Query - Using a table in a drop down menu in a form

[timgentle]

G'day from Australia,

I am currently putting together a database to manage the cattle we run out here on the station (6000 head).

I want to be able generate standard reports which are based on certain criterias I select from a series of drop down menus in a form.

I am very close, as the reports work perfectly when I choose a criteria from the ALL the drop lists, but on many occassions I won't want to be so specific! eg. List all sales on cattle.

Currently I have a form which has a series of drop-down lists that are dynamically reference other tables. "eg. "Which breed do you want to report on ?" = The drop down list displays all the breeds from the table "tb_breed" ie. hereford, angus, crossbred etc

PROBLEM - Most times though I want to be able to select "ALL" breeds rather than being so specific.

QUESTION(1) - Can I have "all" appear in the list. So in the report it will display all the cattle, no just a specific breed.

The SQL in the query is currently :

Where (tb_StockManagement.breed)=[forms]![fm_formgenerator]![breed]!

QUESTION(2) - Do I need to use an "If" statement?

Your support would be greatly appreciated.

Cheers

Tim

 

[KevCon]

g'day Tim.

in the criteria section of your query for that field,
you obviously have it set to that combo box.
well, below that Criteria line is the "Or" line, type the following:

[forms]![fm_formgenerator]![breed]! Is Null

that should return all records if the combo box is blank

 

[timgentle]

Thanks Kev for your assistance...

I tried out your tip, but unfortunately the report didn't display all "breeds of cattle" when I didn't choose from the combo box in the form.

Is this becuase the way I have designed the form won't work unless something I selected an item from the list?

It would be great to only select from the combo box when you want to be specific, and when you don't select from the combo box it display all records that are consistant with the other combo boxes.

Waiting for your reply...

Cheers

Tim

 

[ZmrAbdulla]

I think =[forms]![fm_formgenerator]![breed]! is creating the problem
Bang operator! at the last is not necessary there.

[COLOR=blue]=[Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null[/color]

is the exact way to return all the records if ther criteria left null.
Hope this helps




Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[timgentle]

You are a LEGEND!!! It worked perfectly, my boss will be happy now. Thanks again... I'll touch base again if I get stuck!

Thanks to Kev and Zameer for their support.

If you want to see what we do out here, you can visit...

http://www.todmorden.com.au

(will be live in 2 days)
or

http://www.timgentle.com

(heaps of stories/images)

Cheers

Tim