Hi Terry,
use the FORMAT property to show date and time.
Date is the whole number part, Time is a fraction. Midnight is 0
>"my constructed date and time string"
please tell us what you are using, thanks
~~~
Access stores date/times in a numeric format where the integer portion of the number represents the date and the decimal portion of the number represents time:
1/1/100 --> -657,434
1/2/100 --> -657,433
12/30/1899 --> 0
1/1/1950 --> 18,264
1/1/2005 --> 38,353
12/31/2007 --> 39,447
1/1/9999 --> 2,958,101
12/31/9999 --> 2,958,465
the time is a fraction of the day
12 noon is 0.5
6pm is 0.75
1/1/2005, 12 noon --> 38353.5
if you have a control with just a date and you want to make sure it converts to a whole number (or it is stored in text format), use
DateValue([control_or_fieldname])
or
cLng([control_or_fieldname])
likewise, if you have a time, you can force it to the fractional part by
TimeValue([control_or_fieldname])
since dates are whole numbers and times are the fractions, you can also do arithmetic operations on them
that is why you can subtract one date from another and get the number of days between the two.
Because dates can also have a time component, it is handy to use DateDiff and DateAdd, which let you specify the time increment (year, month, day, hour, etc) to calculate new dates or get a difference between dates.
The DateDiff function can be used to specify what time increment you want returned when you subtract dates
Likewise, there is a DateAdd function to add specific time increments to a date
***
Dates, therefore, are stored as floating point numbers. This makes them inaccurate for direct comparisons anyway -- the best way to ensure you have only the the Whole part of the number (the date), is to use the Integer portion of the number (the date) only -- this, in essence, is what DateValue does. In addition to showing the result in a date format, it strips off the decimals.
~~~~~~~~~~~~~~~~~
International Dates in Access - Allen Browne
http://allenbrowne.com/ser-36.html
If you are setting a filter on a form or using criteria in a query, it works to give Access the date in the American format:
me.Filter = "DateValue(DateFieldname)=" & Format(Date(), "\#mm\/dd\/yyyy\#")
you can also use the ISO 8601 representation for the format:
"\#yyyy\-mm\-dd hh\:mm\:ss\#"
~~~~~~~~~~~~~~~~~
ISO standard
http://en.wikipedia.org/wiki/ISO_8601
ISO 8601 representation e.g.
"\#yyyy\-mm\-dd hh\:mm\:ss\#"
Warm Regards,
Crystal
Learn Access on YouTube
http://www.YouTube.com/LearnAccessByCrystal
*
(: have an awesome day :)
*
________________________________
From: Terry Olsen
Hi
I use the CDate function to convert my constructed date and time string to a valid date and time field. Every time of the day works fine, except 12:00:00 AM. CDate (or something I'm doing) converts 12:00:00 AM (midnight) to 7/11/2012 with no time.
Thanks
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar