Wrong date used to pull off report

  • Thread starter Thread starter Bob
  • Start date Start date
B

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



Jeff Boyce said:
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>

Bob said:
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
Click to expand...
Click to expand...
 
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
Douglas J. Steele said:
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!)



Jeff Boyce said:
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>

Bob said:
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
Click to expand...
Click to expand...
Click to expand...
 
Oh sure, take advantage of having 3 hours more of caffiene!

(thanks, Doug)

Jeff Boyce
<Office/Access MVP>

Douglas J. Steele said:
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!)



Jeff Boyce said:
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>

Bob said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top