Filtering to a form - TechHelp - Computer Learning Zone


	Back to TechHelp Comments List

Upload Images @Reply Bookmark Link Email

Filtering to a form

Maarten Brusselers

2 years ago

I have a query to show me in a form what quantities of what salad went where. In my form I have 3 quantity fields, P(lanting)qty, N(ursery)qty and G(rowth)qty. What I want to do is have only 1 quantity field in my form so that when the StageID is 1, the quantity field shows the Pqty, if it is 2 it shows the Nqty and if it is 3 it shows the Gqty. I have been trying it with IIf functions in my query but cant' get it to work. Any advice would be much appreciated

	Maarten Brusselers OP @Reply 2 years ago

	Maarten Brusselers OP @Reply 2 years ago

	Kevin Robertson @Reply 2 years ago
There are no joins in your Query. You say you have been trying IIF functions. You haven't shown us what you have tried.

	Maarten Brusselers OP @Reply 2 years ago
Thanks Kevin. I noticed that after I sent it but everything worked anyway. I now made the joins but this made no difference. It's evening here now. Will try the IIf functions again tomorrow and show you

	Maarten Brusselers OP @Reply 2 years ago
Hi Kevin, got it to work. As usual a spelling mistake. That happens to me lot. Thanks for the response

Maarten Brusselers OP @Reply

2 years ago

I got it to work but not really in the way it should, although the result is correct.
This is what I did: Quantity: IIf([StageID]=1,[Pquantity],IIf([StageID]=2,[NQuantity],[Gquantity]))
The 1, 2 and 3 are the different stages and by defining them in the IIF function they work. However, for proper database design I think they should take the value from the form and assign the proper quantity directly, without me inserting numerical values.
The form value is Forms]![LotToTableF]![StageID]. I have no idea how to do this.

	Maarten Brusselers OP @Reply 2 years ago

This thread is now CLOSED. If you wish to comment, start a NEW discussion in TechHelp.