Query date without times.

P

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
 
A

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()
 
G

George Nicholson

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

DatePortionOnly: DateValue([MyDateTimeField])
 
P

Peter Mcc

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

akphidelt said:
Create a separate column that has an expression

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

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

Peter Mcc said:
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
Click to expand...
Click to expand...
 
P

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.


akphidelt said:
Create a separate column that has an expression

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

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

Peter Mcc said:
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
Click to expand...
Click to expand...
 
L

Larry Linson

Peter Mcc said:
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.
Click to expand...

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
 
A

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."

Peter Mcc said:
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.


akphidelt said:
Create a separate column that has an expression

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

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

Peter Mcc said:
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
Click to expand...
Click to expand...
Click to expand...
 
P

Peter Mcc

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

Works great thanks,

Al Campagna said:
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."

Peter Mcc said:
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
Click to expand...
Click to expand...
 
Top