sql where start/end date

  • Thread starter Thread starter redFred
  • Start date Start date
R

redFred

I have unbound controls for a date range ([txtStartDate] and [txtEndDate]) on
a form. (They are filled as the form opens.)

The form has a button to open a report, "rpt3QB".

When the report opens, I wish to view based on the above inclusive dates and
to populate date range controls (also [txtStartDate] and [txtEndDate]) on the
report.

This seems to be way over my pay grade. I'd love some help to understand
and resolve.
 
You could pass the dates into separate functions and then use the results of
those functions in the query that drives the report.
 
Thanks for responding. I have no clue how to do that. Treat me like the
Access idiot I am please.

Thanks,
--
redFred


Dennis said:
You could pass the dates into separate functions and then use the results of
those functions in the query that drives the report.

redFred said:
I have unbound controls for a date range ([txtStartDate] and [txtEndDate]) on
a form. (They are filled as the form opens.)

The form has a button to open a report, "rpt3QB".

When the report opens, I wish to view based on the above inclusive dates and
to populate date range controls (also [txtStartDate] and [txtEndDate]) on the
report.

This seems to be way over my pay grade. I'd love some help to understand
and resolve.
Click to expand...
Click to expand...
 
The OpenReport method has a Where argument that can be used to filter a
report. It is constructed just like an SQL WHERE clause without the word
WHERE

Dim strWhere As String

strWhere = "[SomeDateField] BETWEEN #" & Me.txtStartDate & "# AND #" &
Me.txtEndDate & "#"
Docmd.OpenReport "rpt3QB", acViewPreview, , strWhere

To show the date range value on the form, use the control source of the
control on the report to show the value of the control on the form:
=Forms!MyFormName!txtStartDate
 
Klatuu's method would work even better.

Klatuu said:
The OpenReport method has a Where argument that can be used to filter a
report. It is constructed just like an SQL WHERE clause without the word
WHERE

Dim strWhere As String

strWhere = "[SomeDateField] BETWEEN #" & Me.txtStartDate & "# AND #" &
Me.txtEndDate & "#"
Docmd.OpenReport "rpt3QB", acViewPreview, , strWhere

To show the date range value on the form, use the control source of the
control on the report to show the value of the control on the form:
=Forms!MyFormName!txtStartDate



redFred said:
I have unbound controls for a date range ([txtStartDate] and [txtEndDate])
on
a form. (They are filled as the form opens.)

The form has a button to open a report, "rpt3QB".

When the report opens, I wish to view based on the above inclusive dates
and
to populate date range controls (also [txtStartDate] and [txtEndDate]) on
the
report.

This seems to be way over my pay grade. I'd love some help to understand
and resolve.
Click to expand...
Click to expand...
 
Of course! When you put it like that ...

Thanks so much. Again!

--
redFred


Klatuu said:
The OpenReport method has a Where argument that can be used to filter a
report. It is constructed just like an SQL WHERE clause without the word
WHERE

Dim strWhere As String

strWhere = "[SomeDateField] BETWEEN #" & Me.txtStartDate & "# AND #" &
Me.txtEndDate & "#"
Docmd.OpenReport "rpt3QB", acViewPreview, , strWhere

To show the date range value on the form, use the control source of the
control on the report to show the value of the control on the form:
=Forms!MyFormName!txtStartDate



redFred said:
I have unbound controls for a date range ([txtStartDate] and [txtEndDate])
on
a form. (They are filled as the form opens.)

The form has a button to open a report, "rpt3QB".

When the report opens, I wish to view based on the above inclusive dates
and
to populate date range controls (also [txtStartDate] and [txtEndDate]) on
the
report.

This seems to be way over my pay grade. I'd love some help to understand
and resolve.
Click to expand...
Click to expand...
 
<picky>

Just a reminder that if the user has his/her format set to dd/mm/yyyy, that
won't work.

Since you have no control over the user's settings, it's safest to use:

strWhere = "[SomeDateField] BETWEEN " & _
Format(CDate(Me.txtStartDate), "\#yyyy\-mm\-dd\#") & " AND " & _
Format(CDate(Me.txtEndDate), "\#yyyy\-mm\-dd\#")

</picky>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
The OpenReport method has a Where argument that can be used to filter a
report. It is constructed just like an SQL WHERE clause without the word
WHERE

Dim strWhere As String

strWhere = "[SomeDateField] BETWEEN #" & Me.txtStartDate & "# AND #" &
Me.txtEndDate & "#"
Docmd.OpenReport "rpt3QB", acViewPreview, , strWhere

To show the date range value on the form, use the control source of the
control on the report to show the value of the control on the form:
=Forms!MyFormName!txtStartDate



redFred said:
I have unbound controls for a date range ([txtStartDate] and [txtEndDate])
on
a form. (They are filled as the form opens.)

The form has a button to open a report, "rpt3QB".

When the report opens, I wish to view based on the above inclusive dates
and
to populate date range controls (also [txtStartDate] and [txtEndDate]) on
the
report.

This seems to be way over my pay grade. I'd love some help to understand
and resolve.
Click to expand...
Click to expand...
 
Pick is good. Really good. Thanks for the heads-up, Douglas.

--
redFred

Douglas J. Steele said:
<picky>

Just a reminder that if the user has his/her format set to dd/mm/yyyy, that
won't work.

Since you have no control over the user's settings, it's safest to use:

strWhere = "[SomeDateField] BETWEEN " & _
Format(CDate(Me.txtStartDate), "\#yyyy\-mm\-dd\#") & " AND " & _
Format(CDate(Me.txtEndDate), "\#yyyy\-mm\-dd\#")

</picky>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
The OpenReport method has a Where argument that can be used to filter a
report. It is constructed just like an SQL WHERE clause without the word
WHERE

Dim strWhere As String

strWhere = "[SomeDateField] BETWEEN #" & Me.txtStartDate & "# AND #" &
Me.txtEndDate & "#"
Docmd.OpenReport "rpt3QB", acViewPreview, , strWhere

To show the date range value on the form, use the control source of the
control on the report to show the value of the control on the form:
=Forms!MyFormName!txtStartDate



redFred said:
I have unbound controls for a date range ([txtStartDate] and [txtEndDate])
on
a form. (They are filled as the form opens.)

The form has a button to open a report, "rpt3QB".

When the report opens, I wish to view based on the above inclusive dates
and
to populate date range controls (also [txtStartDate] and [txtEndDate]) on
the
report.

This seems to be way over my pay grade. I'd love some help to understand
and resolve.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top