sql where start/end 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.
 
D

Dennis

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

redFred

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...
 
K

Klatuu

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
 
D

Dennis

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...
 
R

redFred

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...
 
D

Douglas J. Steele

<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...
 
R

redFred

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Range Criteria Allowing Blank Entries 0
start date and end date in unbound form 1
My query for start and end dates worked for a minute...then stoppe 1
Can't Ge A Report To Accept Paraemters 3
Want a Parameter for date range driving a cross tab query 2
Date Range on Report 6
#Name? prints on a report when date shows up on preview 6
Reference controls on a dialogue form as the parameters 1

Top