Reference Form Value in Query Function

[toilettenseife]

Good Afternoon,

I am trying to use the following formula to populate a field in a query:

CHRT_HLPR: IIf(X<=12 And X>=Y,X-(Y-1),X+(12-(Y-1)))

Y is a field in a table and X is a user inputted value from a form.

I tried to use [Forms]![Form_Name]![Text_Box_Name]. However, Access says the function is too complex.

Any ideas?

Thanks!
Tim

 

[Joe4]

I don't believe you can use values entered into an Unbound Form in a Calculated Field in a Query (you can use it in the Criteria row, but not in a Calculated Field (Expression).
If you want to include it in a calculation, store it in a field in a table somewhere.

 

[toilettenseife]

Ok, I didn't want to have to do that but I guess that might be my only option.

Thanks!

 

[Joe4]

What exactly is it that you are trying to do/accomplish (what's the bigger picture here)?

 

[xenou]

I think you could get the Y value using DLookup() -- if you have enough criteria to complete the DLookup function with arguments that cause it to return a single value as the result. The X part might work using a [FORMS]![MYFORM]![MYTEXTBOX] syntax.

It's a little ugly but maybe just possible.