DateTime Query

[Guest]

Hello, i have the foloowing query in access;

SELECT dbo_AnalogHistory.DateTime, dbo_AnalogHistory.Tagname,
dbo_AnalogHistory.Value
FROM dbo_AnalogHistory
WHERE (dbo_AnalogHistory.TagName="FIR_301");

The problem is that the results i get back are for today, i want to query
for 12:00 am to 11:59:59 pm on the 11/25/2006?

but how do i put this in DateTime criteria?

ex.

[DateTime] [TagName] [Value]

criteria #11/26/2006# FIR_301 Is Not Null

but this does not work, any help would be welcome.

 

[Guest]

SELECT dbo_AnalogHistory.DateTime,
dbo_AnalogHistory.Tagname,
dbo_AnalogHistory.Value
FROM dbo_AnalogHistory
WHERE dbo_AnalogHistory.TagName="FIR_301"
AND dbo_AnalogHistory.DateTime BETWEEN #11/25/2006# and #11/25/2006# + .99999
AND dbo_AnalogHistory.Value Is Not Null ;

However if you do not have a FIR_301 or a Value on November 25, 2006 no
record will return. Being an AND statement, all three must be true.

One problem with #11/25/2006# is if there are any times in that field.
#11/25/2006# is just for Midnight of that morning. Therefore I put in the
Between statement and added almost a whole day to the last part. .99999 gets
you to a minute before midnight.

 

[fredg]

Hello, i have the foloowing query in access;

SELECT dbo_AnalogHistory.DateTime, dbo_AnalogHistory.Tagname,
dbo_AnalogHistory.Value
FROM dbo_AnalogHistory
WHERE (dbo_AnalogHistory.TagName="FIR_301");

The problem is that the results i get back are for today, i want to query
for 12:00 am to 11:59:59 pm on the 11/25/2006?

but how do i put this in DateTime criteria?

ex.

[DateTime] [TagName] [Value]

criteria #11/26/2006# FIR_301 Is Not Null

but this does not work, any help would be welcome.

Before you fiddle with your query I would suggest you first fix your
database.
You are using 2 keywords as field names.
DateTime and Value are reserved Access/VBA/Jet words and should not be
used as field names.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'


I suspect you really mean to enter the date when prompted, not hard
code the query specifically to 11/26/2006.
After you change your two field names, make sure you set the Parameter
and it's expected datatype in the Parameter dialog box:

click Query + Parameters
[What Date] Date/Time

Then as criteria for the query, try:
WHERE (TagName="FIR_301" AND [NewValueFieldName] Is Not Null AND
DatePart("d",[NewDateFieldName])=DatePart("d",[What Date]);