Search code examples
sqlms-accessuser-input

Placing an undeclared variable in SQL query code generates an input box?

I recently posted this answer as a way to get user input when creating a MS Access query or report. The general behaviour is that if an undeclared variable is placed in SQL query code or a report (e.g. [UndeclaredVariable]), a small input/dialog box appears for the user to input the variables value.

I have been unable to find any mention of this functionality in documentation or elsewhere. All discussion is about using InputBox() in the standard way.

This functionality is unexpected/unusual for several reasons:

  • (In my limited knowledge) Using undeclared variables in MS Access generally causes an error
  • The input/dialog box is different than the one created when InputBox() is used
  • The functionality seems to transcend standard behavior (e.g. when an two undeclared variable are used in this way as the "ifTrue" and "ifFalse" components of an IIf() statement, BOTH dialog boxes are created sequentially!)

Does anyone know what this functionality is called or why it works in these ways?

Solution

  • To summarize the above comments:

    • the behavior is called a "parameter query" and is like normal parameterized queries (see here)

    • The behavior with IIf() is because Access requires a parameter to be given whether or not the value is used (in this case for both [ifTrue] and [ifFalse])

    • There seems to be no way to conditionally parameterize a query or report