Date/Time Data Type
---
Hi Alish,
when you enter [first day:] and [last day:], are you entering dates?
I suspect that the reason your end date is not "between" the supplied
dates is because there is a time component, making the number greater
than the actual date by a fraction.
here is some basic information on dates:
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 --> -657434
1/2/100 --> -657433
12/30/1899 --> 0
1/1/1950 --> 18264
1/1/2005 --> 38353
1/1/9999 --> 2958101
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
for instance, if you are using an international date format, you can do
this:
me.Filter = "DateValue(DateFieldname)=" & Format(Date(),
"\#mm\/dd\/yyyy\#")
~~~~~~~~~~~~~~~~~
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Hey Crystal,
Thanks for your help. I did not quite get you. Do you want me to change the
expression or do you want me to enter criteria as date format like first
date: 03/11/2007 last date: 03/17/2007?
Regards,
Alish
strive4peace said:
Hi Alish,
Make sure you are using JUST date in the field this is criteria for
(since Date also has a Time component)
field --> Date_: DateValue([datefieldname])
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Hi,
The expression 'Between [first day:] and [last day:]' is not giving the
actual last day's records. But when I re-write the expression it works fine.
Please help.
Thanks.
