If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using prompts in a switch statement.
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! |
#2
|
|||
|
|||
Using prompts in a switch statement.
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" wrote in message ... 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! |
#3
|
|||
|
|||
follow-up
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!Q2FY08Split0,Analyst!Q2FY08Split,IIf( Analyst!Q2FY09Split0,Analyst!Q2FY09Split,IIf(Anal yst!Q2FY10Split,[Enter pre-FY08 Q2 Split Amount (Hit enter if FY08 or later):]))) -Bryan |
#4
|
|||
|
|||
follow-up
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 "BryanK" wrote in message ... 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!Q2FY08Split0,Analyst!Q2FY08Split,IIf( Analyst!Q2FY09Split0,Analyst!Q2FY09Split,IIf(Anal yst!Q2FY10Split,[Enter pre-FY08 Q2 Split Amount (Hit enter if FY08 or later):]))) -Bryan |
Thread Tools | |
Display Modes | |
|
|