date function issue

[Anon]

I am trying to extract data from a database through an access query.
One of the fields in my query has a time-stamp date (i.e. the date
entered in the database shows dd/mm/yyyy plus the exact time)

When I run the query to extract data that has only today's date using
the date() function it returns only those few records that have
dd/mm/yyyy and not the ones that although have today's date have also a
time stamp.

I tried to filter the query by using "between date()-1 and date()" but
this returns more than just today's data. Any suggestions on how to
get around this? Is there any other function that I could use?

 

[Van T. Dinh]

Try:

.... WHERE ([TimeStampField] >= Date())
AND ([TimeStampField] < DateAdd("d", 1, Date()))

Perhaps, you should add an Index on the [TimeStampField] (in the Table
design) to speed up the query processing.

 

[Anon]

That didnt work but I tried to play with the dateadd function and
ultimately what worked was ">=DateAdd("d",-1,Date())" under criteria
which returns yesterday data.

Try:

... WHERE ([TimeStampField] >= Date())
AND ([TimeStampField] < DateAdd("d", 1, Date()))

Perhaps, you should add an Index on the [TimeStampField] (in the Table
design) to speed up the query processing.

--
HTH
Van T. Dinh
MVP (Access)

I am trying to extract data from a database through an access query.
One of the fields in my query has a time-stamp date (i.e. the date
entered in the database shows dd/mm/yyyy plus the exact time)

When I run the query to extract data that has only today's date using
the date() function it returns only those few records that have
dd/mm/yyyy and not the ones that although have today's date have also a
time stamp.

I tried to filter the query by using "between date()-1 and date()" but
this returns more than just today's data. Any suggestions on how to
get around this? Is there any other function that I could use?

 

[Van T. Dinh]

The criteria I posted was for today's date because you wrote:

"When I run the query to extract data that has only *today's date* ..."

???

 

[raskew via AccessMonster.com]

Hi -

Consider applying the DateValue() function. Example from the debug
(immediate) window:

x = now()
? x
12/9/06 3:37:42 PM
? datevalue(x)
12/9/06

HTH - Bob

 

[Douglas J. Steele]

If you're suggesting using the DateValue function on the table field, as in:

WHERE DateValue([TableName].[FieldName]) = Date()

it's far more efficient to use

WHERE [TableName].[FieldName] BETWEEN Date() and DateAdd("d", 1, Date())

 

[raskew via AccessMonster.com]

Help me out here. If you have a date in general date format, e.g. #12/10/06
7:17:40 PM#, stored in date/time data type (39061.8039351852),
WHERE [TableName].[FieldName] between date() and DateAdd("d", 1, date()) is
not going to pick it up. Hence the DateValue() function.

If you're suggesting using the DateValue function on the table field, as in:

WHERE DateValue([TableName].[FieldName]) = Date()

it's far more efficient to use

WHERE [TableName].[FieldName] BETWEEN Date() and DateAdd("d", 1, Date())

Hi -

[quoted text clipped - 18 lines]

 

[Van T. Dinh]

It should provided that your current date, i.e. the return of Date() is
10/Dec/2006 (39061)?

(sorry, it is an international newsgroup so I don't know your actual date).

Have you tested it?

 

[raskew via AccessMonster.com]

Hi -

Working in U.S. short-date/general date.

My point is that if we are specifying a date range between 09-Dec-06 (39060)
and 10-Dec-06 (39061) and you have a time attached to the latter date, it's
going to exceed 39061 and thus your date range is not going to capture it.

Bob

It should provided that your current date, i.e. the return of Date() is
10/Dec/2006 (39061)?

(sorry, it is an international newsgroup so I don't know your actual date).

Have you tested it?

Help me out here. If you have a date in general date format, e.g.
#12/10/06

[quoted text clipped - 4 lines]

Bob

 

[Van T. Dinh]

I think there may be some confusion here ... It is possible that either you
or I don't pick up all the posts in the thread ...

Are you asking a question or providing an answer?

If you look at my first post in this thread, I posted:

.... WHERE ([TimeStampField] >= Date())
AND ([TimeStampField] < DateAdd("d", 1, Date()))

so if today's date is 10/Dec/2006 (39061), my criteria selects all between
39061 and 39062 so 39061 + fractional part will be selected.

Apparently, the O.P. asked what seemed to be today's date but he actually
wanted yester's date!