Between [first day:] and [last day:]

[Guest]

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.

 

[strive4peace]

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
*

 

[Guest]

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

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.

 

[strive4peace]

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

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.

 

[Jamie Collins]

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.

One thing missing from your notes is that the smallest time granule
supported by Access/Jet/VBA is one second. I think the better approach
is to keep the values strongly typed as DATETIME and ensure you only
work with values to this level of granularity.

As you point out, the storage uses double float allowing the supported
granularity to be violated e.g.

SELECT CDBL(#2005-01-01 12:00:00#) + CDBL(0.0000000001) AS
test_date_as_DOUBLE

It's a shame that the CDATE() casting function does not 'round' values
to the nearest second e.g.

SELECT CDBL(#2005-01-01 12:00:00#) + CDBL(0.0000000001) AS
test_date_as_DOUBLE,
CBOOL(test_date_as_DOUBLE = #2005-01-01 12:00:00#) AS
result_date_as_DOUBLE

returns FALSE

Therefore, to be able to compare dates you have to round them
'manually' e.g.

SELECT CDBL(#2005-01-01 12:00:00#) + CDBL(0.0000000001) AS
test_date_as_DOUBLE,
CBOOL(CDATE(test_date_as_DOUBLE) = #2005-01-01 12:00:00#) AS
result_date_as_DOUBLE,
DATEADD('S', DATEDIFF('S', #1990-01-01 00:00:00#,
test_date_as_DOUBLE), #1990-01-01 00:00:00#) AS
test_date_as_DATETIME,
CBOOL(test_date_as_DATETIME = #2005-01-01 12:00:00#) AS
result_date_as_DATETIME

To do this in every query becomes a pain. On the basis that bad SQL
DML is often caused by bad SQL DDL, it is better to put the rounding
logic into a Validation Rule to ensure that only DATETIME values to
one second accuracy. Obviously, you don't want the validation Rule to
bite too often so if subsecond values are expected, and considering
most Access projects operate in non- insert-intensive environments, a
more complete solution will do the required rounding in SQL statements
e.g. I'm thinking of a helper proc that takes the date as an argument
and does the rounding there.

for instance, if you are using an international date format, you can do
this:

me.Filter = "DateValue(DateFieldname)=" & Format(Date(),
"\#mm\/dd\/yyyy\#")

Are you saying that format is international? I cannot agree. I
recommend ISO 8601 representation e.g.

"\#yyyy\-mm\-dd hh\:mm\:ss\#"

Jamie.

--

 

[strive4peace]

Hi Jamie,

thanks for your comments. I agree that it would be nice to have
something that kept dates to one second (your helper proc is a good idea).

What I do for floating point comparison sometimes is use a tolerance.
For instance

iif num1-num2 < .0001 then

why do you prefer the ISO format over what the example I gave? they both
seem to work...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access

*

 

[Jamie Collins]

why do you prefer the ISO format over what the example I gave? they both
seem to work...

I picked up the point because you said, "if you are using an
international date format" then proceeded not to use one but instead
use a regional one i.e. #mm/dd/yyyy# the US format. If you hadn't
have mentioned 'international' then neither would have I

Considerations of nation states aside, it is valid and unambiguous for
Access/Jet and as geek I like the left-to-right diminishing size of
time granules of ISO 8601: a year is larger than a month which is
larger than a day etc. From a style point of view, I like to
disambiguate a --/--/yyyy date by using Roman numerals for the month
but that's no good from a software engineering point of view ;-)

What I do for floating point comparison sometimes is use a tolerance.
For instance

iif num1-num2 < .0001 then

Doesn't that mean that everyone writing SQL against the schema has to
know the level of tolerance to apply in all circumstances?

To be honest, I rarely use the approximate types (Double, Single). The
sectors I've worked in (finance, corporate compliance, drug
prescribing) require exact data therefore I predominantly use the
DECIMAL/NUMERIC types i.e. fixed point decimal. I find Access/Jet's
CURRENCY type has too many assumptions (rounding algorithm, value
range, etc) even for money data.

Jamie.

--

 

[strive4peace]

Hi Jamie,

thanks for the correction in my terminology of regional vs international

I am with you on not using single/double. I have problems in code,
however, with Decimal so until that is corrected, I resort to currency
(even with its banker's rounding, it is still more accurate than
floating point)

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access

*

 

[Jamie Collins]

I am with you on not using single/double. I have problems in code,
however, with Decimal so until that is corrected, I resort to currency

I'm not sure what you mean by "problems in code" but a significant
DECIMAL bug has now been fixed. See:

What's fixed (old issues solved)

http://allenbrowne.com/Access2007.html#Solved

"Several issues with existing versions of Access are solved in 2007:
"...Decimal data type: Access can now sort Decimal fields correctly."

Jamie.

--

 

[strive4peace]

thanks for the link, Jamie

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*