Have Dialog Appear When Running Query

  • Thread starter Thread starter nullGumby
  • Start date Start date
N

nullGumby

My client has a few dozen queries, most of which prompt for a date
range. The current solution is to use:

SELECT ...
FROM ...
WHERE Date BETWEEN [Enter Start Date] AND [Enter End Date]

Where "[Enter Start Date]" and "[Enter End Date]" are query
parameters. This works, but it's a pain to type " 1 / 1 / 2007 " when
a DateTimePicker would be so much more efficient.

So I want to do something like:

SELECT ...
FROM ...
WHERE udfGetRangeRange()

Where "udfGetRangeRange()" is a function that would return a string
like " Date BETWEEN #1/1/2007# AND #12/31/2007# ".

My issue is getting the "udfGetRangeRange()" so show my form
"fdlgDateRange" modally. And I'm not even sure if this will work--has
anyone tried a similar solution? My other option is to make a wrapper
form for running all these queries, the user would prefer to just
double-click on the one they want.

Thanks.
 
The standard solution is to use a form to get the dates and to pick the
report. Then run the report from a button on the form.

Form has controls to get the Start date and End date. A control (a listbox
or combobox) to pick the report and a button (or two) to print the report
using the the information fromt he combobox to open the correct report.

I don't believe you can use a UDF to interupt the query and returns values.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
The standard solution is to use a form to get the dates and to pick the
report. Then run the report from a button on the form.

Form has controls to get the Start date and End date. A control (a listbox
or combobox) to pick the report and a button (or two) to print the report
using the the information fromt he combobox to open the correct report.

I don't believe you can use a UDF to interupt the query and returns values.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.


My client has a few dozen queries, most of which prompt for a date
range. The current solution is to use:
Click to expand...
SELECT ...
FROM ...
WHERE Date BETWEEN [Enter Start Date] AND [Enter End Date]
Click to expand...
Where "[Enter Start Date]" and "[Enter End Date]" are query
parameters. This works, but it's a pain to type " 1 / 1 / 2007 " when
a DateTimePicker would be so much more efficient.
Click to expand...
So I want to do something like:
Click to expand...
SELECT ...
FROM ...
WHERE udfGetRangeRange()
Click to expand...
Where "udfGetRangeRange()" is a function that would return a string
like " Date BETWEEN #1/1/2007# AND #12/31/2007# ".
Click to expand...
My issue is getting the "udfGetRangeRange()" so show my form
"fdlgDateRange" modally. And I'm not even sure if this will work--has
anyone tried a similar solution? My other option is to make a wrapper
form for running all these queries, the user would prefer to just
double-click on the one they want.
Click to expand...
Thanks.
Click to expand...
Click to expand...

John, thanks for the response. I tried the test query "qryYesOrNo":

SELECT *
FROM Table1
WHERE udfAskUserYesOrNo();

Public Function udfAskUserYesOrNo() As Boolean
udfAskUserYesOrNo = MsgBox("Yes or no?", vbQuestion + vbYesNo)
End Function

I double-click "qryYesOrNo" and the MsgBox appears; I click "Yes", all
the records return, "No", nothing returns. It would be nice if the
function I write could return some SQL that Access could interpret on
the fly--like " WHERE Date Between #7/1/2007# And #7/31/2007# ".

I'm beginning to think I am going to have to go with the form solution
you described.
 
Back
Top