What am I missing in this statement, >=[Begin Date] And <=[End Date]?

[Kelvin]

I've used this string ">=[Begin Date] And <=[End Date]" many time, but I may
not have used it on a field that has a default value of Now().

I have a field (in my table), DateOfSale with the following values.
Format: Short Date
Input Mask: 99/99/0000;0;_
Default Value: Now()

With all of the above it stores the date including the time.
5/7/2008 2:40:07 PM

If I take the time part off manually, in the table, leaving this 5/7/2008 my
string works fine.
If I enter tomorrow's date it works fine, even if the time is included in
the data

I've deleted the field off the form and added it back on, still stores the
date and TIME.

How can I get the date formatted so my string will recognize the date.

What am I missing here?

Thanks

Kelvin

 

[Rick Brandt]

I've used this string ">=[Begin Date] And <=[End Date]" many time,
but I may not have used it on a field that has a default value of
Now().
I have a field (in my table), DateOfSale with the following values.
Format: Short Date
Input Mask: 99/99/0000;0;_
Default Value: Now()

With all of the above it stores the date including the time.
5/7/2008 2:40:07 PM

If I take the time part off manually, in the table, leaving this
5/7/2008 my string works fine.
If I enter tomorrow's date it works fine, even if the time is
included in the data

I've deleted the field off the form and added it back on, still
stores the date and TIME.

How can I get the date formatted so my string will recognize the date.

What am I missing here?

If EndDate has no time portion then it actually has a time of midnight
(beginning instant of the day). Therefore all date values with that same
date but a *later* time will not satisfy the <= operator that you are using.

Easiest solution is to add an extra day and use < operator.

=[Begin Date] And < DateAdd("d", 1, [End Date])

 

[Beetle]

Change your default value to Date() instead of Now()

 

[fredg]

I've used this string ">=[Begin Date] And <=[End Date]" many time, but I may
not have used it on a field that has a default value of Now().

I have a field (in my table), DateOfSale with the following values.
Format: Short Date
Input Mask: 99/99/0000;0;_
Default Value: Now()

With all of the above it stores the date including the time.
5/7/2008 2:40:07 PM

If I take the time part off manually, in the table, leaving this 5/7/2008 my
string works fine.
If I enter tomorrow's date it works fine, even if the time is included in
the data

I've deleted the field off the form and added it back on, still stores the
date and TIME.

How can I get the date formatted so my string will recognize the date.

What am I missing here?

Thanks

Kelvin

The Format of a Date field is irrelevant to the way Access stores the
date value.
A date, in Access, is the count of days elapsed since 12/30/1899.
Today is stored as 39575.0 (which is midnight this morning)
Using Now(), 5/7/2008 1:05 PM is stored as
39575.5452777778

If you were returning records <= today you would not get any for today
as 39575.5452777778 is greater than 39575.0

Anyway, if the field includes a time value, you must add 1 day to the
EndDate parameter.

=[Begin Date] And <=DateAdd("d",1,[End Date])

You could also use
Between [BeginDate] and DateAdd("d",1,[EndDate])

If you don't wish to include the Time value in the date field, why are
you using Now() as the default value? Now() includes the time.
Use Date() instead.
Now you can remove the Time value from all existing records by running
an update query:

Update YourTable Set YourTable.DateField = DateValue([DateField]);

Then your old criteria would work.

 

[KARL DEWEY]

You can add a calculated field just for appling criteria like this --
Criteria_Field: DateValue([DateField])
Then apply criteria --

=[Begin Date] And <=[End Date]

OR
Between [Begin Date] And [End Date]
--
KARL DEWEY
Build a little - Test a little

I've used this string ">=[Begin Date] And <=[End Date]" many time, but I may
not have used it on a field that has a default value of Now().

I have a field (in my table), DateOfSale with the following values.
Format: Short Date
Input Mask: 99/99/0000;0;_
Default Value: Now()

With all of the above it stores the date including the time.
5/7/2008 2:40:07 PM

If I take the time part off manually, in the table, leaving this 5/7/2008 my
string works fine.
If I enter tomorrow's date it works fine, even if the time is included in
the data

I've deleted the field off the form and added it back on, still stores the
date and TIME.

How can I get the date formatted so my string will recognize the date.

What am I missing here?

Thanks

Kelvin

 

[Kelvin]

Thanks Fred!

Thanks for the explination!
I changed it to Date() and it works great!

Thanks for your time!

I've used this string ">=[Begin Date] And <=[End Date]" many time, but I
may
not have used it on a field that has a default value of Now().

I have a field (in my table), DateOfSale with the following values.
Format: Short Date
Input Mask: 99/99/0000;0;_
Default Value: Now()

With all of the above it stores the date including the time.
5/7/2008 2:40:07 PM

If I take the time part off manually, in the table, leaving this 5/7/2008
my
string works fine.
If I enter tomorrow's date it works fine, even if the time is included in
the data

I've deleted the field off the form and added it back on, still stores
the
date and TIME.

How can I get the date formatted so my string will recognize the date.

What am I missing here?

Thanks

Kelvin

The Format of a Date field is irrelevant to the way Access stores the
date value.
A date, in Access, is the count of days elapsed since 12/30/1899.
Today is stored as 39575.0 (which is midnight this morning)
Using Now(), 5/7/2008 1:05 PM is stored as
39575.5452777778

If you were returning records <= today you would not get any for today
as 39575.5452777778 is greater than 39575.0

Anyway, if the field includes a time value, you must add 1 day to the
EndDate parameter.

=[Begin Date] And <=DateAdd("d",1,[End Date])

You could also use
Between [BeginDate] and DateAdd("d",1,[EndDate])

If you don't wish to include the Time value in the date field, why are
you using Now() as the default value? Now() includes the time.
Use Date() instead.
Now you can remove the Time value from all existing records by running
an update query:

Update YourTable Set YourTable.DateField = DateValue([DateField]);

Then your old criteria would work.

 

[Kelvin]

Thanks

You can add a calculated field just for appling criteria like this --
Criteria_Field: DateValue([DateField])
Then apply criteria --

=[Begin Date] And <=[End Date]

OR
Between [Begin Date] And [End Date]
--
KARL DEWEY
Build a little - Test a little

I've used this string ">=[Begin Date] And <=[End Date]" many time, but I
may
not have used it on a field that has a default value of Now().

I have a field (in my table), DateOfSale with the following values.
Format: Short Date
Input Mask: 99/99/0000;0;_
Default Value: Now()

With all of the above it stores the date including the time.
5/7/2008 2:40:07 PM

If I take the time part off manually, in the table, leaving this 5/7/2008
my
string works fine.
If I enter tomorrow's date it works fine, even if the time is included in
the data

I've deleted the field off the form and added it back on, still stores
the
date and TIME.

How can I get the date formatted so my string will recognize the date.

What am I missing here?

Thanks

Kelvin

 

[Kelvin]

works great!

Thanks

Change your default value to Date() instead of Now()
--
_________

Sean Bailey

I've used this string ">=[Begin Date] And <=[End Date]" many time, but I
may
not have used it on a field that has a default value of Now().

I have a field (in my table), DateOfSale with the following values.
Format: Short Date
Input Mask: 99/99/0000;0;_
Default Value: Now()

With all of the above it stores the date including the time.
5/7/2008 2:40:07 PM

If I take the time part off manually, in the table, leaving this 5/7/2008
my
string works fine.
If I enter tomorrow's date it works fine, even if the time is included in
the data

I've deleted the field off the form and added it back on, still stores
the
date and TIME.

How can I get the date formatted so my string will recognize the date.

What am I missing here?

Thanks

Kelvin

 

[Kelvin]

Thanks it works great!

I've used this string ">=[Begin Date] And <=[End Date]" many time,
but I may not have used it on a field that has a default value of
Now().
I have a field (in my table), DateOfSale with the following values.
Format: Short Date
Input Mask: 99/99/0000;0;_
Default Value: Now()

With all of the above it stores the date including the time.
5/7/2008 2:40:07 PM

If I take the time part off manually, in the table, leaving this
5/7/2008 my string works fine.
If I enter tomorrow's date it works fine, even if the time is
included in the data

I've deleted the field off the form and added it back on, still
stores the date and TIME.

How can I get the date formatted so my string will recognize the date.

What am I missing here?

If EndDate has no time portion then it actually has a time of midnight
(beginning instant of the day). Therefore all date values with that same
date but a *later* time will not satisfy the <= operator that you are
using.

Easiest solution is to add an extra day and use < operator.

=[Begin Date] And < DateAdd("d", 1, [End Date])