Help with If statement

[Guest]

I don't understand Duane. What control could I use, a combo box and a list
box would be too much data for those particular controls. I'll be going off
of invoice dates and I'll be going back several years. That means I'll have
365 dates x those several years. That's why I'm thinking an input box would
be the best way to go. Anyone have any suggestions on how I could make this
work making it easier for the end-user?

Those new to this question:

My question - I have a command button that when clicked, it brings up an
input box. The
user has to put in a date (mm/dd/yyyy). I need an If statement that searches
the column, if the date typed in is in the column, of course it will bring up
the data. But if the date is not in the column, I need another input box to
come up that reads, 'Date not found, try again.' If the user doesn't want to
try again, then a command button to close the input box. (I'm not sure, but
that command button may be a default button)

Here is what I have to pull the correct date:

SELECT *
FROM tblInvLog
WHERE InvDt=[What is the invoice date? (mm/dd/yyyy)];

But again, I need an If statement that tells the user the date was not
found. Right now, if the date is not found, it still brings up the table, but
with no data.

Duane's response - Do your users a favor and use a control on a form to
enter the date. You can
then check to make sure the date is in the report's record source prior to
attempting to open. Otherwise use the On No Data event of the report to
display a message and close the report.

 

[John Vinson]

I don't understand Duane. What control could I use, a combo box and a list
box would be too much data for those particular controls. I'll be going off
of invoice dates and I'll be going back several years. That means I'll have
365 dates x those several years. That's why I'm thinking an input box would
be the best way to go. Anyone have any suggestions on how I could make this
work making it easier for the end-user?

You do NOT need to pop up an Input Box.

Instead, use just a blank textbox on the form (with an input mask if
you insist). The user can type 3/15/2001 into that textbox JUST as
easily as into an Input Box popup.

Those new to this question:

My question - I have a command button that when clicked, it brings up an
input box. The
user has to put in a date (mm/dd/yyyy). I need an If statement that searches
the column, if the date typed in is in the column, of course it will bring up
the data. But if the date is not in the column, I need another input box to
come up that reads, 'Date not found, try again.' If the user doesn't want to
try again, then a command button to close the input box. (I'm not sure, but
that command button may be a default button)

If there is a list of valid dates, you can also use a Combo Box (with
up to 65536 rows, hopefully you don't have THAT many dates) and let
the user select a valid date - rather than making them guess and
slapping their hand if they happen to guess wrong.

John W. Vinson[MVP]

 

[Guest]

Thanks John,

But the thing I don't understand, if I use a text box with an input mask,
how will the rest of the text boxes be filled with the correct invoices for
that date? I know with a combo box I can link it, how can I do that with a
text box?

 

[Guest]

John, I forgot about the subform. Thanks!

You do NOT need to pop up an Input Box.

Instead, use just a blank textbox on the form (with an input mask if
you insist). The user can type 3/15/2001 into that textbox JUST as
easily as into an Input Box popup.


If there is a list of valid dates, you can also use a Combo Box (with
up to 65536 rows, hopefully you don't have THAT many dates) and let
the user select a valid date - rather than making them guess and
slapping their hand if they happen to guess wrong.

John W. Vinson[MVP]

 

[John Vinson]

But the thing I don't understand, if I use a text box with an input mask,
how will the rest of the text boxes be filled with the correct invoices for
that date? I know with a combo box I can link it, how can I do that with a
text box?

You can make the control (combo or textbox) the Master Link Field of a
Subform, or you can use its AfterUpdate event to set the Form's (or
Subform's) Filter property.

John W. Vinson[MVP]