Filter on date in date field with default value Now()

[S. van Beek]

Dear reader,

By a Date field with Now() as default value the content of the field is date
plus time.

As I need a filter in a query on date only (excluding time) I invented the
following solution:

Field in query DatePart: Format([DateNow];"yyyy") & "-"&

Format([DateNow];"mm")
& "-"&

Format([DateNow];"dd")

Criteria in query "2004-11-07"

It works but it looks to me as complicated to subtract the date part from
content of the date/time field for filtering.

Is there an easier way to use a date only filter in a date/time field?

Tanks for any help.

Kind regards,

Simon

[Bruno Campanini]

"S. van Beek" <S.v,Be**@HCCnet.nl> wrote in message
news:41*********************@reader20.nntp.hccnet. nl...

By a Date field with Now() as default value the content of the field is
date
plus time.

If you change default value to Date() the contend is date only.

Ciao
Bruno

[Douglas J. Steele]

Are you saying that your table has times, and you want to filter on date
only? Use the DateValue function on your field:

Field in query WhatDate: DateValue([DateNow])
Criteria in query #2004-11-07#

BTW, I wouldn't use DatePart as a name: there's a DatePart function in VBA,
so you run the risk of problems.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"S. van Beek" <S.v,Be**@HCCnet.nl> wrote in message
news:41*********************@reader20.nntp.hccnet. nl...

Dear reader,

By a Date field with Now() as default value the content of the field is date plus time.

As I need a filter in a query on date only (excluding time) I invented the
following solution:

Field in query DatePart: Format([DateNow];"yyyy") & "-"&

Format([DateNow];"mm")
& "-"&

Format([DateNow];"dd")

Criteria in query "2004-11-07"

It works but it looks to me as complicated to subtract the date part from
content of the date/time field for filtering.

Is there an easier way to use a date only filter in a date/time field?

Tanks for any help.

Kind regards,

Simon

[Alfred]

Use Int(now()) is better than date() . Date causes problems.
"S. van Beek" <S.v,Be**@HCCnet.nl> wrote in message
news:41*********************@reader20.nntp.hccnet. nl...

Dear reader,

By a Date field with Now() as default value the content of the field is
date
plus time.

As I need a filter in a query on date only (excluding time) I invented the
following solution:

Field in query DatePart: Format([DateNow];"yyyy") & "-"&

Format([DateNow];"mm")
& "-"&

Format([DateNow];"dd")

Criteria in query "2004-11-07"

It works but it looks to me as complicated to subtract the date part from
content of the date/time field for filtering.

Is there an easier way to use a date only filter in a date/time field?

Tanks for any help.

Kind regards,

Simon

[John Winterbottom]

"S. van Beek" <S.v,Be**@HCCnet.nl> wrote in message
news:41*********************@reader20.nntp.hccnet. nl...

Dear reader,

By a Date field with Now() as default value the content of the field is
date
plus time.

As I need a filter in a query on date only (excluding time) I invented the
following solution:

Field in query DatePart: Format([DateNow];"yyyy") & "-"&

Format([DateNow];"mm")
& "-"&

Format([DateNow];"dd")

Don't use a calculated column in a where clause - it's better to modify your
criteria like so:

parameters [theDate] datetime;
select * from myTable
where
(
myDate >= [theDate]
and myDate < [theDate] +1
)