Adding an IIF to a where condition?

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I am using a where condition, [INCIDENT]![Report_Number]=([Enter Report
Number]) and it will bring back the correct report for the number entered.
However it will bring back a new record if the number entered is not correct.

How can I add an IIF or other change to either prompt the user to re-enter
their number or just stop the search when the number entered does not exist?

Thank you for any assistance in this matter.
Frank
 
Frank,

It depends a bit when, where, and how, you are doing this. Can you give
us a bit of context? And what is supposed to happen as the result of
whatever you are trying to achieve.

I will say this, though... using a parameter prompt like that to obtain
query criteria is generally not a great approach.
 
I have a button on a startup page that allows the user to search for a
specific report that needs to be edited. The macro attached to this button
allows the user to Enter Parameter Value, (report number) and OpenForm.

I would like to stop the macro if the Parameter Value entered by the user is
not correct. Currently if the Parameter Value entered does not refer to any
report numbers a blank form is opened.

Thank you

Steve Schapel said:
Frank,

It depends a bit when, where, and how, you are doing this. Can you give
us a bit of context? And what is supposed to happen as the result of
whatever you are trying to achieve.

I will say this, though... using a parameter prompt like that to obtain
query criteria is generally not a great approach.

--
Steve Schapel, Microsoft Access MVP
I am using a where condition, [INCIDENT]![Report_Number]=([Enter Report
Number]) and it will bring back the correct report for the number entered.
However it will bring back a new record if the number entered is not correct.

How can I add an IIF or other change to either prompt the user to re-enter
their number or just stop the search when the number entered does not exist?

Thank you for any assistance in this matter.
Frank
Click to expand...
Click to expand...
 
Frank,

As far as I know, this is not possible directly.

One option would be to put a macro on the Load event of the second form
so that it would close again straight away if there are no records.

As I said before, the 'enter parameter value' idea is one which most
people give up on pretty quickly.

A better approach would be to put an unbound textbox on the "startup
page" form, where the user will enter the report number. Then, in the
query, in the place of the [Enter Report Number] parameter, you enter a
criterion that references the textbox, using syntax the equivalent of:
[Forms]![NameOfYourStartupForm]![NameOfTextbox]

Then, in the macro, in the Condition for the OpenForm action, is the
equivalent of this:
DCount("*","YourTable/Query","[Report Number]=" & [NameOfTextbox])>0
 
Back
Top