Parameter Form

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I am using an unbound form (frmSpecify) to specify report
(rptTraining) criteria. Among its controls are text boxes
for Start Date (txtStartDate) and End Date (txtEndDate).
A parameter query (qrySpecify) is the source for
rptTraining. It is based on tblTraining, which includes a
field for [SessionDate]. The parameter for [SessionDate]
is: Between [Forms]![frmSpecify]![txtStartDate] And
[Forms]![frmSpecify]![txtEndDate].
There are other parameters such as EmployeeName, and the
option of leaving parameters blank. The default value for
txtEndDate on frmSpecify is: =Date(). The format for the
text box is Short Date. I would like the default value
for txtStartDate to be January 1 of the current year, but
I am not having any success with formatting the date (e.g.
=Format(Date(),"yy"). The idea was to return two digits
for the current year, and concatenate it with "1/1/".
Another thing I would like to do in a similar situation
elsewhere is to leave txtStartDate blank in order to
return all records. I realize I would have to specify
that in the parameter query. For Employee I can do
something like:

[Forms]![frmSpecify]![txtName] Or [Forms]!
[frmSpecifyRecord]![txtName] Is Null

(no line break) but I cannot figure out how to do that
with Between...And. I will never need to specify that
[txtEndDate] Is Null, since it defaults to the current
date.
 
Hi,
This will give you Jan1 of the current year:
DateSerial(DatePart("yyyy",Date),1,1)

HTH
Dan Artuso, MVP
 
Thanks for your speedy reply. I discovered that if I
created a hidden text box (txtDate) on the form, set its
control source to =Date(), and substituted [txtDate] for
Date in the code example (which I put into Control Source
for txtStartDate), it worked. While experimenting I found
that if instead of putting ="1/1/"&Format(Date(),"yy")
into Control Source (instead of Default Value) for
txtStartDate, that also worked. I probably could have
referenced txtEndDate instead of the hidden text box in
the code. That might be the cleanest of all. Anyhow,
there seems to be several viable approaches.
Any ideas on how to make the parameter query work with
txtStartDate blank? The report's default Start Date in
that case would be the earliest date for which there is a
record. That part is already set up. I just can't
remember how to leave Start Date blank.
-----Original Message-----
Hi,
This will give you Jan1 of the current year:
DateSerial(DatePart("yyyy",Date),1,1)

HTH
Dan Artuso, MVP

"Bruce" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
I am using an unbound form (frmSpecify) to specify report
(rptTraining) criteria. Among its controls are text boxes
for Start Date (txtStartDate) and End Date (txtEndDate).
A parameter query (qrySpecify) is the source for
rptTraining. It is based on tblTraining, which includes a
field for [SessionDate]. The parameter for [SessionDate]
is: Between [Forms]![frmSpecify]![txtStartDate] And
[Forms]![frmSpecify]![txtEndDate].
There are other parameters such as EmployeeName, and the
option of leaving parameters blank. The default value for
txtEndDate on frmSpecify is: =Date(). The format for the
text box is Short Date. I would like the default value
for txtStartDate to be January 1 of the current year, but
I am not having any success with formatting the date (e.g.
=Format(Date(),"yy"). The idea was to return two digits
for the current year, and concatenate it with "1/1/".
Another thing I would like to do in a similar situation
elsewhere is to leave txtStartDate blank in order to
return all records. I realize I would have to specify
that in the parameter query. For Employee I can do
something like:

[Forms]![frmSpecify]![txtName] Or [Forms]!
[frmSpecifyRecord]![txtName] Is Null

(no line break) but I cannot figure out how to do that
with Between...And. I will never need to specify that
[txtEndDate] Is Null, since it defaults to the current
date.
Click to expand...


.
Click to expand...
 
Hi,
The easiest way is probably to make the default start date on your form the earliest date in the table.
You could also use something like this. I just made the date the year 1900.
You could use DMin() to actually use the earliest date in the table, but the below will work.

Between IIf([Forms]![frmTestDates]![txtStart] Is Null,#01/01/1900#,[Forms]![frmTestDates]![txtStart]) And
[Forms]![frmTestDates]![txtEnd]

Substitute your names.

HTH
Dan Artuso, MVP

Bruce said:
Thanks for your speedy reply. I discovered that if I
created a hidden text box (txtDate) on the form, set its
control source to =Date(), and substituted [txtDate] for
Date in the code example (which I put into Control Source
for txtStartDate), it worked. While experimenting I found
that if instead of putting ="1/1/"&Format(Date(),"yy")
into Control Source (instead of Default Value) for
txtStartDate, that also worked. I probably could have
referenced txtEndDate instead of the hidden text box in
the code. That might be the cleanest of all. Anyhow,
there seems to be several viable approaches.
Any ideas on how to make the parameter query work with
txtStartDate blank? The report's default Start Date in
that case would be the earliest date for which there is a
record. That part is already set up. I just can't
remember how to leave Start Date blank.
-----Original Message-----
Hi,
This will give you Jan1 of the current year:
DateSerial(DatePart("yyyy",Date),1,1)

HTH
Dan Artuso, MVP

"Bruce" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
I am using an unbound form (frmSpecify) to specify report
(rptTraining) criteria. Among its controls are text boxes
for Start Date (txtStartDate) and End Date (txtEndDate).
A parameter query (qrySpecify) is the source for
rptTraining. It is based on tblTraining, which includes a
field for [SessionDate]. The parameter for [SessionDate]
is: Between [Forms]![frmSpecify]![txtStartDate] And
[Forms]![frmSpecify]![txtEndDate].
There are other parameters such as EmployeeName, and the
option of leaving parameters blank. The default value for
txtEndDate on frmSpecify is: =Date(). The format for the
text box is Short Date. I would like the default value
for txtStartDate to be January 1 of the current year, but
I am not having any success with formatting the date (e.g.
=Format(Date(),"yy"). The idea was to return two digits
for the current year, and concatenate it with "1/1/".
Another thing I would like to do in a similar situation
elsewhere is to leave txtStartDate blank in order to
return all records. I realize I would have to specify
that in the parameter query. For Employee I can do
something like:

[Forms]![frmSpecify]![txtName] Or [Forms]!
[frmSpecifyRecord]![txtName] Is Null

(no line break) but I cannot figure out how to do that
with Between...And. I will never need to specify that
[txtEndDate] Is Null, since it defaults to the current
date.
Click to expand...


.
Click to expand...
Click to expand...
 
Bruce,

I see what you're getting at with the default date being
the earliest record being the easiest, but the most useful
will probably be January 1 of the current year. I can
probably set up Dmin() in a hidden text box and reference
that if needed to find the earliest date. The more I
think about it the more I realize it will rarely be
necessary to determine the earliest date, so I won't spend
much time on that now. We can always use 1/1/80 or
something, and re-run the report after learning the
earliest date. Thanks again for your help.
-----Original Message-----
Hi,
The easiest way is probably to make the default start
Click to expand...
date on your form the earliest date in the table.
You could also use something like this. I just made the date the year 1900.
You could use DMin() to actually use the earliest date in
Click to expand...
the table, but the below will work.
Between IIf([Forms]![frmTestDates]![txtStart] Is
Click to expand...
Null,#01/01/1900#,[Forms]![frmTestDates]![txtStart]) And
[Forms]![frmTestDates]![txtEnd]

Substitute your names.

HTH
Dan Artuso, MVP

"Bruce" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
Thanks for your speedy reply. I discovered that if I
created a hidden text box (txtDate) on the form, set its
control source to =Date(), and substituted [txtDate] for
Date in the code example (which I put into Control Source
for txtStartDate), it worked. While experimenting I found
that if instead of putting ="1/1/"&Format(Date(),"yy")
into Control Source (instead of Default Value) for
txtStartDate, that also worked. I probably could have
referenced txtEndDate instead of the hidden text box in
the code. That might be the cleanest of all. Anyhow,
there seems to be several viable approaches.
Any ideas on how to make the parameter query work with
txtStartDate blank? The report's default Start Date in
that case would be the earliest date for which there is a
record. That part is already set up. I just can't
remember how to leave Start Date blank.
-----Original Message-----
Hi,
This will give you Jan1 of the current year:
DateSerial(DatePart("yyyy",Date),1,1)

HTH
Dan Artuso, MVP

"Bruce" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
I am using an unbound form (frmSpecify) to specify report
(rptTraining) criteria. Among its controls are text boxes
for Start Date (txtStartDate) and End Date (txtEndDate).
A parameter query (qrySpecify) is the source for
rptTraining. It is based on tblTraining, which includes a
field for [SessionDate]. The parameter for [SessionDate]
is: Between [Forms]![frmSpecify]![txtStartDate] And
[Forms]![frmSpecify]![txtEndDate].
There are other parameters such as EmployeeName, and the
option of leaving parameters blank. The default
Click to expand...
value
for
txtEndDate on frmSpecify is: =Date(). The format for the
text box is Short Date. I would like the default value
for txtStartDate to be January 1 of the current year, but
I am not having any success with formatting the date (e.g.
=Format(Date(),"yy"). The idea was to return two digits
for the current year, and concatenate it with "1/1/".
Another thing I would like to do in a similar situation
elsewhere is to leave txtStartDate blank in order to
return all records. I realize I would have to specify
that in the parameter query. For Employee I can do
something like:

[Forms]![frmSpecify]![txtName] Or [Forms]!
[frmSpecifyRecord]![txtName] Is Null

(no line break) but I cannot figure out how to do that
with Between...And. I will never need to specify that
[txtEndDate] Is Null, since it defaults to the current
date.


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top