query with datetime values

  • Thread starter Thread starter MobielPlezier.com --> Fun with your mobile
  • Start date Start date
M

MobielPlezier.com --> Fun with your mobile

Hi all
I have a little problem
i have a table with the following values12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/18/2005 20:20
12/18/2005 20:20
12/18/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/19/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:56
12/20/2005 21:00
12/20/2005 21:06
12/20/2005 21:12
12/21/2005 21:15
12/22/2005 21:15
12/22/2005 21:19

When i build a query to display only the values of #12/18/2005# i get no
records. Its because of the time.
Can anyone point me in the direction as how to have the date specified? To
have all the records for a specific date or a date range without the time
causing problems?
Example if i want all the records for 12/18/2005 or all records between
12/17/2005 and 12/21/2005

Thanks and regards
--
Vishal Sewkaransing
www.SuriStar.com / www.Mobielplezier.com
Messenger: (e-mail address removed)
Dr. J.F. Nassylaan # 35 Par'bo
office 597-420094 / 367555
mobile 597-8515544
Office hours 9.00 AM - 6.00 PM
 
Use criteria that goes from Date1 to Date2
Date field always contain a time. If you just put in a date the time is
midnight. Dates are stored in Access as a number (Type Double). Everything
before the decimal point is the day number and everything after is the time.

So you can do one of the Following:
-- Use DateValue to get just the date portion of your field
Field: DateOnly: DateValue([YourTablename}.[YourFieldName])
Criteria: #12/17/2005#

-- Use a Date range
Field: [YourfieldName]
Criteria: >= #12/17/2005# AND < #12/18/2005#

For multiple days
Criteria: >= #12/17/2005# AND < #12/22/2005#
 
Thanks for you reply john
It worked but after some seconds i get an error
Criteria mismatch and all the records give Name?

Any comments?
Thanks again

John Spencer said:
Use criteria that goes from Date1 to Date2
Date field always contain a time. If you just put in a date the time is
midnight. Dates are stored in Access as a number (Type Double).
Everything before the decimal point is the day number and everything after
is the time.

So you can do one of the Following:
-- Use DateValue to get just the date portion of your field
Field: DateOnly: DateValue([YourTablename}.[YourFieldName])
Criteria: #12/17/2005#

-- Use a Date range
Field: [YourfieldName]
Criteria: >= #12/17/2005# AND < #12/18/2005#

For multiple days
Criteria: >= #12/17/2005# AND < #12/22/2005#


MobielPlezier.com --> Fun with your mobile said:
Hi all
I have a little problem
i have a table with the following values12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/18/2005 20:20
12/18/2005 20:20
12/18/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/19/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:56
12/20/2005 21:00
12/20/2005 21:06
12/20/2005 21:12
12/21/2005 21:15
12/22/2005 21:15
12/22/2005 21:19

When i build a query to display only the values of #12/18/2005# i get no
records. Its because of the time.
Can anyone point me in the direction as how to have the date specified?
To
have all the records for a specific date or a date range without the time
causing problems?
Example if i want all the records for 12/18/2005 or all records between
12/17/2005 and 12/21/2005

Thanks and regards
--
Vishal Sewkaransing
www.SuriStar.com / www.Mobielplezier.com
Messenger: (e-mail address removed)
Dr. J.F. Nassylaan # 35 Par'bo
office 597-420094 / 367555
mobile 597-8515544
Office hours 9.00 AM - 6.00 PM
Click to expand...
Click to expand...
 
Its OK NOW
IT was MY FAULT
i had a record where there was no date data in.
THANKS
sewkar said:
Thanks for you reply john
It worked but after some seconds i get an error
Criteria mismatch and all the records give Name?

Any comments?
Thanks again

John Spencer said:
Use criteria that goes from Date1 to Date2
Date field always contain a time. If you just put in a date the time is
midnight. Dates are stored in Access as a number (Type Double).
Everything before the decimal point is the day number and everything
after is the time.

So you can do one of the Following:
-- Use DateValue to get just the date portion of your field
Field: DateOnly: DateValue([YourTablename}.[YourFieldName])
Criteria: #12/17/2005#

-- Use a Date range
Field: [YourfieldName]
Criteria: >= #12/17/2005# AND < #12/18/2005#

For multiple days
Criteria: >= #12/17/2005# AND < #12/22/2005#


MobielPlezier.com --> Fun with your mobile said:
Hi all
I have a little problem
i have a table with the following values12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/18/2005 20:20
12/18/2005 20:20
12/18/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/19/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:56
12/20/2005 21:00
12/20/2005 21:06
12/20/2005 21:12
12/21/2005 21:15
12/22/2005 21:15
12/22/2005 21:19

When i build a query to display only the values of #12/18/2005# i get no
records. Its because of the time.
Can anyone point me in the direction as how to have the date specified?
To
have all the records for a specific date or a date range without the
time
causing problems?
Example if i want all the records for 12/18/2005 or all records between
12/17/2005 and 12/21/2005

Thanks and regards
--
Vishal Sewkaransing
www.SuriStar.com / www.Mobielplezier.com
Messenger: (e-mail address removed)
Dr. J.F. Nassylaan # 35 Par'bo
office 597-420094 / 367555
mobile 597-8515544
Office hours 9.00 AM - 6.00 PM
Click to expand...
Click to expand...
Click to expand...
 
I assume that you are using the DateValue option.

You can use the following to avoid trouble
Field: DateOnly: IIF([YourTablename].[YourFieldName] is Null,Null,DateValue([YourTablename].[YourFieldName]))
Its OK NOW
IT was MY FAULT
i had a record where there was no date data in.
THANKS
sewkar said:
Thanks for you reply john
It worked but after some seconds i get an error
Criteria mismatch and all the records give Name?

Any comments?
Thanks again

John Spencer said:
Use criteria that goes from Date1 to Date2
Date field always contain a time. If you just put in a date the time is
midnight. Dates are stored in Access as a number (Type Double).
Everything before the decimal point is the day number and everything
after is the time.

So you can do one of the Following:
-- Use DateValue to get just the date portion of your field
Field: DateOnly: DateValue([YourTablename}.[YourFieldName])
Criteria: #12/17/2005#

-- Use a Date range
Field: [YourfieldName]
Criteria: >= #12/17/2005# AND < #12/18/2005#

For multiple days
Criteria: >= #12/17/2005# AND < #12/22/2005#


"MobielPlezier.com --> Fun with your mobile" <[email protected]>
wrote in message Hi all
I have a little problem
i have a table with the following values12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/18/2005 20:20
12/18/2005 20:20
12/18/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/19/2005 20:20
12/17/2005 20:20
12/17/2005 20:20
12/17/2005 20:56
12/20/2005 21:00
12/20/2005 21:06
12/20/2005 21:12
12/21/2005 21:15
12/22/2005 21:15
12/22/2005 21:19

When i build a query to display only the values of #12/18/2005# i get no
records. Its because of the time.
Can anyone point me in the direction as how to have the date specified?
To
have all the records for a specific date or a date range without the
time
causing problems?
Example if i want all the records for 12/18/2005 or all records between
12/17/2005 and 12/21/2005

Thanks and regards
--
Vishal Sewkaransing
www.SuriStar.com / www.Mobielplezier.com
Messenger: (e-mail address removed)
Dr. J.F. Nassylaan # 35 Par'bo
office 597-420094 / 367555
mobile 597-8515544
Office hours 9.00 AM - 6.00 PM
Click to expand...
Click to expand...
Click to expand...
 
Back
Top