Using prompts in a switch statement.

[BryanK]

Hello, I have setup a switch statement, with the final item, if true,
requesting the database user to input a value. I was curious if it is
possible to make the query run so that it only prompts for a value if the
final statment ([Analyst]![Fy06Split]+[Analyst]![FY07Split])>0) is true. Due
to the setup of the tables and the requests of the group whom this project is
for, it seems this is the only way to setup the field.

Q1 Split:
Switch([Analyst]![Q1FY08Split]>0,[Analyst]![Q1FY08Split],[Analyst]![Q1FY09Split]>0,[Analyst]![Q1FY09Split],[Analyst]![Q1FY10Split]>0,[Analyst]![Q1FY10Split],([Analyst]![Fy06Split]+[Analyst]![FY07Split])>0,[Enter
pre-FY08 Split Amount (Hit enter if Q1 FY08 or later):])

Thanks!

 

[Michel Walsh]

Not with Switch. Switch is a function and as all functions, requires that
all its arguments got fully evaluated before launching its function-work.

On the other hand, JET-iif is a statement, meaning it is more like a flow
control and indeed, only the required 'parts' get evaluated, as the flow of
code execution progress.

So, in short try something like:

iif( "Bonzai" = Switch( ..., ..., ..., ..., true, "Bonzai"),
MyVBAFunction( ) , Switch( ..., ..., ..., ..., true, "Bonzai"))


ie. only if Switch returns "Bonzai" will the function MyVBAFunction() will
be called. This function, inside a STANDARD VBA module, could then open a
form, prompting for the user to supply a parameter. If you just want some
fast proof of faisability, you can probably use the function InputBox() :


iif("Bonzai" = Switch( ..., ..., ..., ..., true, "Bonzai"),
InputBox("pre-Fy08 Split Amount" ) , Switch( ..., ..., ..., ..., true,
"Bonzai") )





(note: it can be shorter to define an alias to the result of Switch:

mySwitch: Switch( ..., ..., ..., ..., true, "Bonzai")


and then, in another column, use:

iif( "Bonzai"= mySwitch, InputBox( "label here"), mySwitch)



It reduces the required typing.)




Vanderghast, Access MVP

 

[BryanK]

I have also tried creating it as a nested iif, would this make it easier to
solve my problem (and only prompt, or open a form when it is necessary)?
Sorry for my simplistic response, I am still a bit of a novice when it comes
to Access...



IIf(Analyst!Q2FY08Split>0,Analyst!Q2FY08Split,IIf(Analyst!Q2FY09Split>0,Analyst!Q2FY09Split,IIf(Analyst!Q2FY10Split,[Enter
pre-FY08 Q2 Split Amount (Hit enter if FY08 or later):])))


-Bryan

 

[Michel Walsh]

Yes. You cannot just use the parameter name, though. Use something like
InputBox( "Enter pre-FY08 Q2 ... " ) instead of the last [Enter pre-FY08
Q2 ... ] , as last argument of your last iif.


Vanderghast, Access MVP