Making a user form for a Parameter Query

[shriil]

I have a parameter query which asks the user to enter the [start date]
and [end date] on a DATE field, and then executes as per the criteria:
Between [start date] and [end date]. How can I design and link a form
which will have these parameters for the user to enter, after which the
query will execute?

Thanks for the help

 

[John Vinson]

I have a parameter query which asks the user to enter the [start date]
and [end date] on a DATE field, and then executes as per the criteria:
Between [start date] and [end date]. How can I design and link a form
which will have these parameters for the user to enter, after which the
query will execute?

Thanks for the help

Create an unbound form (let's call it frmCrit) with textboxes for your
criteria - e.g. txtStartDate and txtEndDate.

Use a criterion such as

BETWEEN [Forms]![frmCrit]![txtStartDate] AND
[Forms]![frmCrit]![txtEndDate]

It's convenient to put a command button on frmCrit to open a Report or
another Form based on the query. You generally need not and should not
display the query datasheet to users; open a form for onscreen
display, or a report for printing, and leave the query "under the
hood" where it belongs.

John W. Vinson[MVP]

 

[Michael Gramelspacher]

I have a parameter query which asks the user to enter the [start date]
and [end date] on a DATE field, and then executes as per the criteria:
Between [start date] and [end date]. How can I design and link a form
which will have these parameters for the user to enter, after which the
query will execute?

Thanks for the help

I use a form like this
http://www.psci.net/gramelsp/temp/percent.png

Here is code behind Run Query button. After the code runs the
query the query results also display in the form.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef

Set db = CurrentDb

If Len(Me.txtStartDate & "") = 0 Then
MsgBox "Start date must contain a date!"
Me.txtStartDate.SetFocus
Exit Sub
End If

If Len(Me.txtStopDate & "") = 0 Then
MsgBox "Stop date must contain a date!"
Me.txtStopDate.SetFocus
Exit Sub
End If

Set qd = db.QueryDefs("DaysDifferenceBirthBaptism")

qd![Enter church name] = Me.txtChurchName
qd![Enter start date] = Me.txtStartDate
qd![Enter stop date] = Me.txtStopDate

' alternate method
'qd.Parameters("Enter church name") = Me.txtChurchName
'qd.Parameters("Enter start date") = Me.txtStartDate
'qd.Parameters("Enter stop date") = Me.txtStopDate

Set rs = qd.OpenRecordset()

If Not rs.EOF Then
Me.txtChurch = rs![ChurchName]
Me.txtTotalCount = rs![Total Count]
Me.txtNoValue = rs![No Value]
Me.txt0_5days = rs![0-5 days]
Me.txt6_15days = rs![6-15 days]
Me.txt15_30days = rs![15-30 days]
Me.txt31_days = rs![31+days]
End If

rs.Close
Set rs = Nothing
Set qd = Nothing
Set db = Nothing

 

[???]

??? ??????? ???????

 

[shriil]

Thks a lot. That was a big help

S N Lahiri

I have a parameter query which asks the user to enter the [start date]
and [end date] on a DATE field, and then executes as per the criteria:
Between [start date] and [end date]. How can I design and link a form
which will have these parameters for the user to enter, after which the
query will execute?

Thanks for the help

Create an unbound form (let's call it frmCrit) with textboxes for your
criteria - e.g. txtStartDate and txtEndDate.

Use a criterion such as

BETWEEN [Forms]![frmCrit]![txtStartDate] AND
[Forms]![frmCrit]![txtEndDate]

It's convenient to put a command button on frmCrit to open a Report or
another Form based on the query. You generally need not and should not
display the query datasheet to users; open a form for onscreen
display, or a report for printing, and leave the query "under the
hood" where it belongs.

John W. Vinson[MVP]

 

[shriil]

You said about using the criterion " BETWEEN
[Forms]![frmCrit]![txtStartDate] AND

[Forms]![frmCrit]![txtEndDate] . but where ?? I mean how do I link it with the query?

The original query has a Date Field where I have put a criteria
"Between [Please enter Start Date] And [Please Enter End Date] for the
user to specify a range of date between which the query will run. My
idea is that whenever the query will be run, instead of the usual
Access window of "Enter Parameter Value" for inputting the Start Date
and End Date, I would like to have a form which will have these two
date fields , where the user will input the dates, and the same shall
be linked to the query for subsequent execution of the same.

Pls help

I have a parameter query which asks the user to enter the [start date]
and [end date] on a DATE field, and then executes as per the criteria:
Between [start date] and [end date]. How can I design and link a form
which will have these parameters for the user to enter, after which the
query will execute?

Thanks for the help

Create an unbound form (let's call it frmCrit) with textboxes for your
criteria - e.g. txtStartDate and txtEndDate.

Use a criterion such as

BETWEEN [Forms]![frmCrit]![txtStartDate] AND
[Forms]![frmCrit]![txtEndDate]

It's convenient to put a command button on frmCrit to open a Report or
another Form based on the query. You generally need not and should not
display the query datasheet to users; open a form for onscreen
display, or a report for printing, and leave the query "under the
hood" where it belongs.

John W. Vinson[MVP]

 

[Rick Brandt]

You said about using the criterion " BETWEEN
[Forms]![frmCrit]![txtStartDate] AND

[Forms]![frmCrit]![txtEndDate] . but where ?? I mean how do I link
it with the query?

The original query has a Date Field where I have put a criteria
"Between [Please enter Start Date] And [Please Enter End Date] for the
user to specify a range of date between which the query will run. My
idea is that whenever the query will be run, instead of the usual
Access window of "Enter Parameter Value" for inputting the Start Date
and End Date, I would like to have a form which will have these two
date fields , where the user will input the dates, and the same shall
be linked to the query for subsequent execution of the same.

Pls help

In your query find the text "Between [Please enter Start Date] And [Please Enter
End Date]" and replace it with "Between [Forms]![frmCrit]![txtStartDate] And
[Forms]![frmCrit]![txtEndDate]".

 

[John Vinson]

You said about using the criterion " BETWEEN
[Forms]![frmCrit]![txtStartDate] AND

[Forms]![frmCrit]![txtEndDate] . but where ?? I mean how do I link it with the query?

Now you have a criterion on your query design grid like

BETWEEN [Enter start date] AND [Enter end date].

Open the query in design view and replace this criterion with

BETWEEN [Forms]![frmCrit]![txtStartDate] AND
[Forms]![frmCrit]![txtEndDate]


John W. Vinson[MVP]