Missing records using a timestamped field to query.

[Michaelcip]

I'm trying to extract a range of information using a timestamp field through
a select query. I'm using Between[Date] and [Date]. It's not caputuring all
of the records, specifically the records w/ the 1st and the very last days of
the range. I tried doing something like this "Date([Timestamp])" but to no
avail. Any thoughts as to why?! Thanks, Michael

 

[John W. Vinson]

I'm trying to extract a range of information using a timestamp field through
a select query. I'm using Between[Date] and [Date]. It's not caputuring all
of the records, specifically the records w/ the 1st and the very last days of
the range. I tried doing something like this "Date([Timestamp])" but to no
avail. Any thoughts as to why?! Thanks, Michael

Please be more specific. Between [Date] AND [Date] is not a range.

Please post the SQL view of your query and perhaps a sample of the data you're
missing. My *guess* is that you're using a range of just dates, but that the
table data contains date and time; if so a criterion

= [Start date] AND < DateAdd("d", 1, [End date])

on the date field should do the job. If the user enters 2/12/2008 in the End
Date parameter, the DateAdd will ensure that all times during that day are
included in the search.

 

[John Spencer]

IF your timestamp field contains a date and a time then those field that
occur from 00:00:01 and on on the last date in your range will be excluded.
You will only see the records that have midnight on the last date.

I can think of no reason for your missing records on the earliest date in
the range.

Try this as the criteria.

= [Enter begin date] and < DateAdd("d",1, [Enter End Date])

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'm trying to extract a range of information using a timestamp field
through
a select query. I'm using Between[Date] and [Date]. It's not caputuring
all
of the records, specifically the records w/ the 1st and the very last days
of
the range. I tried doing something like this "Date([Timestamp])" but to
no
avail. Any thoughts as to why?! Thanks, Michael

 

[Michaelcip]

It's a SQL statement to display a bar chart:
SELECT tblQA.CODE, Count(*) AS [Count]
FROM (qryElectrical INNER JOIN tblQA ON qryElectrical.QAID = tblQA.QAID)
INNER JOIN tblCode ON qryElectrical.CODEID = tblCode.CODEID
WHERE (((qryElectrical.tblHeader.TimeStamp) Between
[Forms]![frmReports]![Start] And [Forms]![frmReports]![End]) AND
((tblCode.CODEID)=[Forms]![frmFailCode]![txtCode]))
GROUP BY tblQA.CODE
ORDER BY Count(*) DESC;

I'm trying to extract a range of information using a timestamp field through
a select query. I'm using Between[Date] and [Date]. It's not caputuring all
of the records, specifically the records w/ the 1st and the very last days of
the range. I tried doing something like this "Date([Timestamp])" but to no
avail. Any thoughts as to why?! Thanks, Michael

Please be more specific. Between [Date] AND [Date] is not a range.

Please post the SQL view of your query and perhaps a sample of the data you're
missing. My *guess* is that you're using a range of just dates, but that the
table data contains date and time; if so a criterion

= [Start date] AND < DateAdd("d", 1, [End date])

on the date field should do the job. If the user enters 2/12/2008 in the End
Date parameter, the DateAdd will ensure that all times during that day are
included in the search.

 

[John W. Vinson]

It's a SQL statement to display a bar chart:

If the field named TimeStamp (which I presume is selected in qryElectrical) is
a date/time field containing a time portion... and if you're missing the
records from the last day of the range... try

SELECT tblQA.CODE, Count(*) AS [Count]
FROM (qryElectrical INNER JOIN tblQA ON qryElectrical.QAID = tblQA.QAID)
INNER JOIN tblCode ON qryElectrical.CODEID = tblCode.CODEID
WHERE (((qryElectrical.TimeStamp) >=[Forms]![frmReports]![Start]
And (qryElectrical.TimeStamp) < DateAdd("d", 1, [Forms]![frmReports]![End]))
AND ((tblCode.CODEID)=[Forms]![frmFailCode]![txtCode]))
GROUP BY tblQA.CODE
ORDER BY Count(*) DESC;

 

[Michaelcip]

This did the trick! I appreciate your time in educating me!!

It's a SQL statement to display a bar chart:

If the field named TimeStamp (which I presume is selected in qryElectrical) is
a date/time field containing a time portion... and if you're missing the
records from the last day of the range... try

SELECT tblQA.CODE, Count(*) AS [Count]
FROM (qryElectrical INNER JOIN tblQA ON qryElectrical.QAID = tblQA.QAID)
INNER JOIN tblCode ON qryElectrical.CODEID = tblCode.CODEID
WHERE (((qryElectrical.TimeStamp) >=[Forms]![frmReports]![Start]
And (qryElectrical.TimeStamp) < DateAdd("d", 1, [Forms]![frmReports]![End]))
AND ((tblCode.CODEID)=[Forms]![frmFailCode]![txtCode]))
GROUP BY tblQA.CODE
ORDER BY Count(*) DESC;