calculated date field criteria...

[Guest]

some help please, I would like to add a criteria to the following calculated
date column

BillDate: Format(DateAdd("m",-1,[DueDate]),"mm/dd/yy")

in the criteria I am entering between 02/01/2006 and 02/28/2006 but getting
no results when there are records falling between these 2 dates I have also
tried between #02/01/2006# and #02/28/2006# and still no results
Am I missing something

 

[OfficeDev18 via AccessMonster.com]

Does it work with any dates?

Sam

some help please, I would like to add a criteria to the following calculated
date column

BillDate: Format(DateAdd("m",-1,[DueDate]),"mm/dd/yy")

in the criteria I am entering between 02/01/2006 and 02/28/2006 but getting
no results when there are records falling between these 2 dates I have also
tried between #02/01/2006# and #02/28/2006# and still no results
Am I missing something

 

[Duane Hookom]

Why are you applying Format(). If you want to query the calculation as a
date value, don't format it.

 

[John Vinson]

some help please, I would like to add a criteria to the following calculated
date column

BillDate: Format(DateAdd("m",-1,[DueDate]),"mm/dd/yy")

in the criteria I am entering between 02/01/2006 and 02/28/2006 but getting
no results when there are records falling between these 2 dates I have also
tried between #02/01/2006# and #02/28/2006# and still no results
Am I missing something

Yes. The Format() function converts your date/time value to a Text
String, and Text Strings sort and search alphabetically, not
numerically. The text string "02/15/06" is NOT between the text
strings "02/01/2006" and "02/28/2006", because at the first
difference, the character 2 is greater than the character 0.

Try using a date/time value of BillDate: just remove the Format and
use the DateAdd function call. This will be a true date value, and a
criterion of

BETWEEN #02/01/2006# AND #02/28/2006#

or, better,

= DateSerial([Enter year:], [Enter month:], 1) AND < DateSerial([Enter year:], [Enter month:] + 1, 1)

John W. Vinson[MVP]

 

[Guest]

Using the format change the field type from date to string, and probably this
is why you are not getting any resault

try this
Cvdate(Format(DateAdd("m",-1,[DueDate]),"mm/dd/yy")) Between ....

 

[Guest]

Yes it does work with dates

--
thanks as always for the help

Does it work with any dates?

Sam

some help please, I would like to add a criteria to the following calculated
date column

BillDate: Format(DateAdd("m",-1,[DueDate]),"mm/dd/yy")

in the criteria I am entering between 02/01/2006 and 02/28/2006 but getting
no results when there are records falling between these 2 dates I have also
tried between #02/01/2006# and #02/28/2006# and still no results
Am I missing something

 

[Guest]

Thank you Duane
--
thanks as always for the help

Why are you applying Format(). If you want to query the calculation as a
date value, don't format it.

--
Duane Hookom
MS Access MVP
--

some help please, I would like to add a criteria to the following
calculated
date column

BillDate: Format(DateAdd("m",-1,[DueDate]),"mm/dd/yy")

in the criteria I am entering between 02/01/2006 and 02/28/2006 but
getting
no results when there are records falling between these 2 dates I have
also
tried between #02/01/2006# and #02/28/2006# and still no results
Am I missing something

 

[Guest]

thanks John
--
thanks as always for the help

some help please, I would like to add a criteria to the following calculated
date column

BillDate: Format(DateAdd("m",-1,[DueDate]),"mm/dd/yy")

in the criteria I am entering between 02/01/2006 and 02/28/2006 but getting
no results when there are records falling between these 2 dates I have also
tried between #02/01/2006# and #02/28/2006# and still no results
Am I missing something

Yes. The Format() function converts your date/time value to a Text
String, and Text Strings sort and search alphabetically, not
numerically. The text string "02/15/06" is NOT between the text
strings "02/01/2006" and "02/28/2006", because at the first
difference, the character 2 is greater than the character 0.

Try using a date/time value of BillDate: just remove the Format and
use the DateAdd function call. This will be a true date value, and a
criterion of

BETWEEN #02/01/2006# AND #02/28/2006#

or, better,

= DateSerial([Enter year:], [Enter month:], 1) AND < DateSerial([Enter year:], [Enter month:] + 1, 1)

John W. Vinson[MVP]