467,135 Members | 1,197 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,135 developers. It's quick & easy.

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

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
Nov 13 '05 #1
  • viewed: 4907
Share:
4 Replies
"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
Nov 13 '05 #2
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

Nov 13 '05 #3
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

Nov 13 '05 #4
"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
)

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Jerome | last post: by
3 posts views Thread by Nathan Bloomfield | last post: by
3 posts views Thread by Finn Stampe Mikkelsen | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.