excluding 12:00:00 AM from query results

[xyzer]

I know 12:00:00 AM is 0, but when I put "not 0" in the criteria box it still gives me 12:00:00 AM in my results. The only way I have found to exclude them is to set up an IIF statement that pulls the date if it's not 0 (i.e.,not 12:00:00 AM) otherwise pull the date then filter on that field. Is there a way to simply exclude this straight up?

 

[Douglas J Steele]

Date fields in Access contain both date and time. 0 is actually 12:00:00 AM
on 30 Dec, 1899. 12:00:00 AM today (30 Jan, 2013) would actually be 41304,
since it's 41304 days since 30 Dec, 1899.

If a date field also contains time, then [TheDateField] <>
DateValue([TheDateField])

wrote in message

I know 12:00:00 AM is 0, but when I put "not 0" in the criteria box it still
gives me 12:00:00 AM in my results. The only way I have found to exclude
them is to set up an IIF statement that pulls the date if it's not 0 (i.e.,
not 12:00:00 AM) otherwise pull the date then filter on that field. Is there
a way to simply exclude this straight up?

 

[xyzer]

Interesting information, but I'm still not able to exclude the "12:00:00 AM" records. But just as you say, if I use a dateadd function to add 1 day to"12:00:00 AM" then the date miracously appears as 12/31/1899 along with the time. I guess I'll just use that field to exclude what I need. It just makes the query slower. Thanks.

 

[John W. Vinson]

I know 12:00:00 AM is 0, but when I put "not 0" in the criteria box it still gives me 12:00:00 AM in my results. The only way I have found to exclude them is to set up an IIF statement that pulls the date if it's not 0 (i.e., not 12:00:00 AM) otherwise pull the date then filter on that field. Is there a way to simply exclude this straight up?

What's actually in the table? Could you post the SQL of the query?

A criterion of

<> #12/30/1899#

or equivalently

<> 0

will exclude all records where the date/time value is zero; a criterion of

IS NOT NULL

will exclude NULL dates. IIF() is a wretched way to do this...
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bob Quintal]

(e-mail address removed) wrote in

Interesting information, but I'm still not able to exclude the
"12:00:00 AM" records. But just as you say, if I use a dateadd
function to add 1 day to "12:00:00 AM" then the date miracously
appears as 12/31/1899 along with the time. I guess I'll just use
that field to exclude what I need. It just makes the query slower.
Thanks.

Date math in Acce3ss is very interesting.
Access stores the date and time as a whole number, with the date
being the integer portion and the time being the fraction (noon is
..5, 6 pm is .75)

So to excludse the time of midnight, set the filter to test whether
the whole number equals the integer of that number.

<> int(myDateAndTime) in the criteria row of the query will remove
those records that occur at exactly midnight.

Date fields in Access contain both date and time. 0 is actually
12:00:00 AM

on 30 Dec, 1899. 12:00:00 AM today (30 Jan, 2013) would actually
be 41304 ,

since it's 41304 days since 30 Dec, 1899.



If a date field also contains time, then [TheDateField] <>

DateValue([TheDateField])



wrote in message




I know 12:00:00 AM is 0, but when I put "not 0" in the criteria
box it st ill

gives me 12:00:00 AM in my results. The only way I have found to
exclude

them is to set up an IIF statement that pulls the date if it's
not 0 (i.e .,

not 12:00:00 AM) otherwise pull the date then filter on that
field. Is th ere

a way to simply exclude this straight up?