Date = Date() idiocy

[Paul Gross]

First, let me say, I feel like an idiot.

The simple background: I'm using Access to create a
SIMPLE database to track my newborn's feeding (bottle fed)
intake, diapers, etc. I mean this is SIMPLE. Each entry
is an Event with a date (medium). I'm trying to due a
calculated field total intake to have on a form for
running total. I would expect that it would be as simple
as:

Field: Intake: sum([Amount])

Criteria:
[Event] = "feeding" and Date=Date()

but it doesn't work. It is clearly the Date=Date() that
makes the query return with no records because if I
decompose it, to exclude date, I get the right
calculations.

I've simplified my query to just try to return records
with today's date to debug this and just can't get it to
work! It works with Date<Date() or Date>Date()+1 but not
the expression equivalent of TODAY.

Please, enlighten me on what I'm missing. I would hate to
return to Excel for what is clearly an Access problem.

Paul

 

[John Vinson]

Please, enlighten me on what I'm missing.

Probably the fact that Date is a reserved word, and a very bad choice
as a fieldname. If you try [Date] = Date() you might have better luck;
better would be to change the fieldname.

Another possibility is that you're filling the field using the Now()
function - this inserts the date AND THE TIME, accurate to
microseconds. #7/15/2003 17:02:31# is not equal to Date()! Change the
default to Date() to store just the date without the time portion, or
use a calculated field of

JustDate: DateValue([Date])

Give the baby a peek-a-boo from me!

 

[Douglas J. Steele]

How did you populate the field? If you used Now(), that includes time as
well as date, so it's extremely unlikely that any value in the table will
equal a Date() value.

You can remove the time component by using the DateValue function, or you
can look for dates between Date() and Date() + 1.

BTW, using Date as a table field name (or for any user-defined object, for
that matter) is not a very good idea: it's a reserved word, and can lead to
problems. If you're stuck with that design, at least use square brackets in
your query to indicate that it's a field name:

[Event] = "feeding" and [Date]=Date()

 

[PHG]

Thanks.

I had actually changed teh field name to eDate, and it
does have a default value set with Now() so my problem is
that I'm formating the presentation one way and letting it
fool me on what a match would be in a query. Thanks for
the insight.

Paul

-----Original Message-----
How did you populate the field? If you used Now(), that includes time as
well as date, so it's extremely unlikely that any value in the table will
equal a Date() value.

You can remove the time component by using the DateValue function, or you
can look for dates between Date() and Date() + 1.

BTW, using Date as a table field name (or for any user- defined object, for
that matter) is not a very good idea: it's a reserved word, and can lead to
problems. If you're stuck with that design, at least use square brackets in
your query to indicate that it's a field name:

[Event] = "feeding" and [Date]=Date()


--
Doug Steele, Microsoft Access MVP

First, let me say, I feel like an idiot.

The simple background: I'm using Access to create a
SIMPLE database to track my newborn's feeding (bottle fed)
intake, diapers, etc. I mean this is SIMPLE. Each entry
is an Event with a date (medium). I'm trying to due a
calculated field total intake to have on a form for
running total. I would expect that it would be as simple
as:

Field: Intake: sum([Amount])

Criteria:
[Event] = "feeding" and Date=Date()

but it doesn't work. It is clearly the Date=Date() that
makes the query return with no records because if I
decompose it, to exclude date, I get the right
calculations.

I've simplified my query to just try to return records
with today's date to debug this and just can't get it to
work! It works with Date<Date() or Date>Date()+1 but not
the expression equivalent of TODAY.

Please, enlighten me on what I'm missing. I would hate to
return to Excel for what is clearly an Access problem.

Paul

.