Query date without times.

[Peter Mcc]

I need to query fields matching the date the query is ran by the user. I
tried [date()] in the filed's criteria but since my dates are stored with
time i get no results. I need to keep the time in the field but also would
like to query by current date.

i tried

Between Date() And Date() + 1

still nothing. I'm sure i'm missing something easy, but i'm still new to
Access.

any help is appreciated

 

[Al Campagna]

Peter
Try
Between Date() And (Date()+1)
The times should make no difference. If the "Date portion" is between
Date and (Date+1) those dates will be returned.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[akphidelt]

Create a separate column that has an expression

ChangeDate: Format([YourDateColumn],"Short Date")

Then in the criteria of this expression put in the Date()

 

[George Nicholson]

You could apply a criteria of Date() to a (separate) calculated field like:

DatePortionOnly: DateValue([MyDateTimeField])

 

[Peter Mcc]

Oops i'm sorry. I miss read what you replyed. Yes this helps. thanks again.

Create a separate column that has an expression

ChangeDate: Format([YourDateColumn],"Short Date")

Then in the criteria of this expression put in the Date()

I need to query fields matching the date the query is ran by the user. I
tried [date()] in the filed's criteria but since my dates are stored with
time i get no results. I need to keep the time in the field but also would
like to query by current date.

i tried

Between Date() And Date() + 1

still nothing. I'm sure i'm missing something easy, but i'm still new to
Access.

any help is appreciated

 

[Peter Mcc]

Thanks for the reply,

However, from what i have seen it seems there is a query expression that
will accomplish what i want. I just don't know enogh about Access to write
it. I need a query expression that will either disreagard the time when
querying 'date()' or include all dates-times for the current day. I want to
avoid adding any additonal fileds to the table structure. But your soultion
seems like it would work otherwise.

Create a separate column that has an expression

ChangeDate: Format([YourDateColumn],"Short Date")

Then in the criteria of this expression put in the Date()

I need to query fields matching the date the query is ran by the user. I
tried [date()] in the filed's criteria but since my dates are stored with
time i get no results. I need to keep the time in the field but also would
like to query by current date.

i tried

Between Date() And Date() + 1

still nothing. I'm sure i'm missing something easy, but i'm still new to
Access.

any help is appreciated

 

[Larry Linson]

Thanks for the reply,

However, from what i have seen it seems there is a query expression that
will accomplish what i want. I just don't know enogh about Access to write
it. I need a query expression that will either disreagard the time when
querying 'date()' or include all dates-times for the current day. I want
to
avoid adding any additonal fileds to the table structure. But your
soultion
seems like it would work otherwise.

It seems your data is stored in a Date/Time field, with the time of day
included. Most likely, that means the Now() function was used to obtain the
value. If you wish to store only the Date, not the time, obtain the value
with the Date() function. On the other hand, to process the information you
now have stored, you can use a WHERE CONDITION very similar to that posted
by Al... >=Date() And <Date()+1.

BETWEEN is "inclusive" so will retrieve a date and time of midnight of
Date()+1.

Larry Linson
Microsoft Office Access MVP

 

[Al Campagna]

Peter,
Have you read my response?
The criteria... Between Date() And (Date + 1)... addresses only the
"date" portion of your Date/Time values, which is what you need. You don't
have to care about... or address... the Time portion at all.
I suspect you may not have returned records previously due to a faulty
criteria, rather than the time values causing any problem.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thanks for the reply,

However, from what i have seen it seems there is a query expression that
will accomplish what i want. I just don't know enogh about Access to write
it. I need a query expression that will either disreagard the time when
querying 'date()' or include all dates-times for the current day. I want
to
avoid adding any additonal fileds to the table structure. But your
soultion
seems like it would work otherwise.

Create a separate column that has an expression

ChangeDate: Format([YourDateColumn],"Short Date")

Then in the criteria of this expression put in the Date()

I need to query fields matching the date the query is ran by the user.
I
tried [date()] in the filed's criteria but since my dates are stored
with
time i get no results. I need to keep the time in the field but also
would
like to query by current date.

i tried

Between Date() And Date() + 1

still nothing. I'm sure i'm missing something easy, but i'm still new
to
Access.

any help is appreciated

 

[Peter Mcc]

Oh, Date() And (Date() + 1)

Works great thanks,

Peter
Try
Between Date() And (Date()+1)
The times should make no difference. If the "Date portion" is between
Date and (Date+1) those dates will be returned.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I need to query fields matching the date the query is ran by the user. I
tried [date()] in the filed's criteria but since my dates are stored with
time i get no results. I need to keep the time in the field but also would
like to query by current date.

i tried

Between Date() And Date() + 1

still nothing. I'm sure i'm missing something easy, but i'm still new to
Access.

any help is appreciated