Wrong date used to pull off report

[Bob]

Hi I use the following code to pull off a daily report. Entering i.e.
txtStartDate 14/12/05 txtEndDate 14/12/05 it returns no data found when
files have been closed on that date.

yet if you enter txtStartDate 14/12/05 txtEndDate 15/12/05 it returns
the closed files for 14/12/05 the same code works on other daily reports
that I use by entering the same dates
i.e. 14/12/05 & 14/12/05 where am I going wrong? I now have 2 reports doing
the same thing.

If I use Between [Start Date]And[Finish Date] in the query I get
the same results

Dim strWhere As String

strWhere = "[FileClosedDate] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"

stDocName1 = "rptFileClosed"
DoCmd.OpenReport stDocName1, acPreview, , strWhere
DoCmd.RunCommand acCmdZoom100


Any idea's or help would be very much appreciated.

Bob

 

[Jeff Boyce]

Bob

I suspect that if you look at the underlying field you are checking, you'll
find that it is declared a Date/Time field, AND you'll find that there are
times included in the data.

When you use the same date as both From and To, but don't indicate any time,
Access uses midnight (00:00:00) as the time portion. Your "date range" test
is asking for records between midnight on the 14th, ... and midnight on the
14th!

If you convert the date/time value to date-only (for use in the report's
underlying query), using something like:
Date([YourDateTimeField])
this is a value you should be able to test with a Between ... And ...
criterion.

 

[Douglas J. Steele]

Slight typo, Jeff. That should be DateValue([FileClosedDate), not
Date([FileClosedDate])

However, it would probably be more efficient to use:

strWhere = "[FileClosedDate] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & _
"# AND #" & Format(DateAdd("d", 1, Me.txtEndDate), "dd-mmm-yy") & "#"

rather than using the DateValue function on each row of the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bob

I suspect that if you look at the underlying field you are checking,
you'll
find that it is declared a Date/Time field, AND you'll find that there are
times included in the data.

When you use the same date as both From and To, but don't indicate any
time,
Access uses midnight (00:00:00) as the time portion. Your "date range"
test
is asking for records between midnight on the 14th, ... and midnight on
the
14th!

If you convert the date/time value to date-only (for use in the report's
underlying query), using something like:
Date([YourDateTimeField])
this is a value you should be able to test with a Between ... And ...
criterion.

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi I use the following code to pull off a daily report. Entering i.e.
txtStartDate 14/12/05 txtEndDate 14/12/05 it returns no data found when
files have been closed on that date.

yet if you enter txtStartDate 14/12/05 txtEndDate 15/12/05 it returns
the closed files for 14/12/05 the same code works on other daily reports
that I use by entering the same dates
i.e. 14/12/05 & 14/12/05 where am I going wrong? I now have 2 reports doing
the same thing.

If I use Between [Start Date]And[Finish Date] in the query I get
the same results

Dim strWhere As String

strWhere = "[FileClosedDate] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"

stDocName1 = "rptFileClosed"
DoCmd.OpenReport stDocName1, acPreview, , strWhere
DoCmd.RunCommand acCmdZoom100


Any idea's or help would be very much appreciated.

Bob

 

[Bob]

Thanks all for your help, both options work but I have gone for the strWhere
option.

Thanks again for all the help its very much appreciated.

Bob

Slight typo, Jeff. That should be DateValue([FileClosedDate), not
Date([FileClosedDate])

However, it would probably be more efficient to use:

strWhere = "[FileClosedDate] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & _
"# AND #" & Format(DateAdd("d", 1, Me.txtEndDate), "dd-mmm-yy") & "#"

rather than using the DateValue function on each row of the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bob

I suspect that if you look at the underlying field you are checking,
you'll
find that it is declared a Date/Time field, AND you'll find that there
are
times included in the data.

When you use the same date as both From and To, but don't indicate any
time,
Access uses midnight (00:00:00) as the time portion. Your "date range"
test
is asking for records between midnight on the 14th, ... and midnight on
the
14th!

If you convert the date/time value to date-only (for use in the report's
underlying query), using something like:
Date([YourDateTimeField])
this is a value you should be able to test with a Between ... And ...
criterion.

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi I use the following code to pull off a daily report. Entering i.e.
txtStartDate 14/12/05 txtEndDate 14/12/05 it returns no data found when
files have been closed on that date.

yet if you enter txtStartDate 14/12/05 txtEndDate 15/12/05 it
returns
the closed files for 14/12/05 the same code works on other daily reports
that I use by entering the same dates
i.e. 14/12/05 & 14/12/05 where am I going wrong? I now have 2 reports doing
the same thing.

If I use Between [Start Date]And[Finish Date] in the query I
get
the same results

Dim strWhere As String

strWhere = "[FileClosedDate] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"

stDocName1 = "rptFileClosed"
DoCmd.OpenReport stDocName1, acPreview, , strWhere
DoCmd.RunCommand acCmdZoom100


Any idea's or help would be very much appreciated.

Bob

 

[Jeff Boyce]

Oh sure, take advantage of having 3 hours more of caffiene!

(thanks, Doug)

Jeff Boyce
<Office/Access MVP>

Slight typo, Jeff. That should be DateValue([FileClosedDate), not
Date([FileClosedDate])

However, it would probably be more efficient to use:

strWhere = "[FileClosedDate] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & _
"# AND #" & Format(DateAdd("d", 1, Me.txtEndDate), "dd-mmm-yy") & "#"

rather than using the DateValue function on each row of the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bob

I suspect that if you look at the underlying field you are checking,
you'll
find that it is declared a Date/Time field, AND you'll find that there
are
times included in the data.

When you use the same date as both From and To, but don't indicate any
time,
Access uses midnight (00:00:00) as the time portion. Your "date range"
test
is asking for records between midnight on the 14th, ... and midnight on
the
14th!

If you convert the date/time value to date-only (for use in the report's
underlying query), using something like:
Date([YourDateTimeField])
this is a value you should be able to test with a Between ... And ...
criterion.

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi I use the following code to pull off a daily report. Entering i.e.
txtStartDate 14/12/05 txtEndDate 14/12/05 it returns no data found when
files have been closed on that date.

yet if you enter txtStartDate 14/12/05 txtEndDate 15/12/05 it
returns
the closed files for 14/12/05 the same code works on other daily reports
that I use by entering the same dates
i.e. 14/12/05 & 14/12/05 where am I going wrong? I now have 2 reports doing
the same thing.

If I use Between [Start Date]And[Finish Date] in the query I
get
the same results

Dim strWhere As String

strWhere = "[FileClosedDate] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"

stDocName1 = "rptFileClosed"
DoCmd.OpenReport stDocName1, acPreview, , strWhere
DoCmd.RunCommand acCmdZoom100


Any idea's or help would be very much appreciated.

Bob