MS SQL datetime field in access report

[Alan]

When trying to display a date field in an access report
not all of the dates are showing on the report. When the
stored proc (which is the record source for the report) is
executed in query analyzer all the dates are shown as
expected. When the access report is run, not all of the
dates that were returned in qa show up. Any thoughts or
ideas would be greatly appreciated. Also, tried different
format options on the date column in access but no luck.

 

[SA]

Alan:

When you say the dates are not showing up, do you mean that the sproc is not
returning all the records you would expect in the report or, that all the
row data displays, but you are not seeing the value of the data in the
control that is bound to the returned date field?

If the latter, then what SQL format are you using for the date field (i.e.
using a cast or convert statement in the sproc)?

 

[Guest]

sproc returns all the records as expected, but the text
box on the report does not show all of the records
returned. I cast the date field to small datetime and
still do not get all records when the report is run.
Thanks for the help.

 

[SA]

There's always a number of things to look at when dealing with dates in SQL
server vs. how you may have historically dealt with them in Access.

Generally when you store a date in Access (Jet) you store the short date say
12/6/2003 and if you want to span a between you use 12/6/2003 and 12/7/2003
as an example; time is not generally stored in the field. SQL server
stores the full date time data, so if as an example you're internal data
time field has dates that were stored for 12/7/2003 with a time signature
any time after midnight, and you are sending in a short date which has a
time signature of 00:00:00, then you'll only end up with in the previous
example records from the 12/6/2003, because all the 12/7/2003 records have a
time signature greater than what you are sending in as a high side of the
range in your paramter

You might want to look at the following article from SQL Server Magazine
from a few years ago which discusses some of the many issues with dates in
SQL Server that can mess you up.

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9723

HTH