Using a form to set a query field

[ablim]

Any help or advice on what to search on would be appreciated please

I have a table with the stocklevels of all my shops Fields (PLU, Stocklevel_1 to StockLevel_6..)

Currently I have a form with 3 category levels which set the criteria in a query so I can gereate a list of 50 random PLU's as long as the query already has which Stocklevel to look at (ps the stocklevels table is linked to the Stock details stable via the PLU field)

Rather than creating multiple queries can I include another combo box or a option group with a list of branches which will set which field to get info from? Any suggestions on how this is done?

Thanks

 

[FoFa]

You could using the IIF functionality

 

[ablim]

Looking at the help access gives i can't see how that would help me

They give:
This example uses the IIf function to evaluate the TestMe parameter of the CheckIt procedure and returns the word "Large" if the amount is greater than 1000; otherwise, it returns the word "Small".

Function CheckIt (TestMe As Integer)
CheckIt = IIf(TestMe > 1000, "Large", "Small")
End Function

But thanks for replying so quick!!!!!

 

[FoFa]

If I understand your orginal question properly.
I hate the simplistic examples they always give.
IIF(Condition,TRUE,FALSE)
SO in your example you want one of a column (stocklevel 1 thru 6) if some condition is meet so
IIF(Cond1,[Stocklevel1],IIF(Cond2,[Stocklevel2],IIF(Cond3,[StockLevel3],etc)))
So this shows the TRUE condition being used to return a specific column, and the ELSE condition used to perform another check (nested IIF's). If this is used in a single column of a query, it would return the proper column within ONE column of your query.
Plain as mud?

 

[ablim]

I think I get it.

So in plain english I'm asking it to do is check whats in the option group/combo box to see if it is "Shop1" (ie stocklevel_1) & use the StockLevel_1 column if that condition is true, if not it checks the next statement.

I'll give it a try thanks
*************************************************************
Not sure I have done it right, i'm just producing a query data sheet at the moment but its giving a column expr1 instead of the Stocklevel_x that I am off.

I have this:
Expr1: IIf([Forms]![Frm_randselection]![Branchbox]="Head Office",[tblProduct_Master_StockLevel].[StockLevel_1],IIf([Forms]![Frm_randselection]![Branchbox]="Oldham",[tblProduct_Master_StockLevel].[StockLevel_2],IIf([Forms]![Frm_randselection]![Branchbox]="Middleton",[tblProduct_Master_StockLevel].[StockLevel_3],IIf([Forms]![Frm_randselection]![Branchbox]="Burnley",[tblProduct_Master_StockLevel].[StockLevel_4],IIf([Forms]![Frm_randselection]![Branchbox]="Blackburn",[tblProduct_Master_StockLevel].[StockLevel_5],IIf([Forms]![Frm_randselection]![Branchbox]="Bury",[tblProduct_Master_StockLevel].[StockLevel_6]))))))
in the field row of a column in the query builder. Is that the wrong place to put it?

 

[FoFa]

No, that should be the right place.
Try simple first:
Expr1: IIf([Forms]![Frm_randselection]![Branchbox]="Head Office",[tblProduct_Master_StockLevel].[StockLevel_1],"-ELSE-")
Or something like that, when one works then add the next part. Keep going like that because debugging is much easier.

 

[ablim]

I have had it working but only when I left out the [Forms]![Frm_randselection]! bit and manually typed the Head Office when the prompt came up.

is it ok to leave the Else statement out for now?? I'm going to put some error handling in there later for if they dont select a branch
***********************************************
Just as test i set the default value to Head Office and it gave me results too, its like its ignoring the value of the combo box

 

[ablim]

FoFa thanks for you help buddy, I got it working when I set up the option group

Thanks