field expression problem when using parameter

[leahf via AccessMonster.com]

I have a query with a field expression that checks that a begin_date is
greater/equal to 31/12/2006 and the end date is less than 01/01/2008. The
criteria is TRUE. It works perfectly.

What I want to do now is to take the year from a form. The expression that I
used:

Expr1: "([class]![class_begin_date]>=#31/12/" & [Forms]!
[frm_rptNtivStatistics]![ComboYear]-1 & "# And [class]![class_end_date]
<#01/01/" & [Forms]![frm_rptNtivStatistics]![ComboYear]+1 & "#)"

In other words, if the user chooses the year 2007, then I should get the
expression that I need. When I ran the query itself (not in the report), I
got in the expression field exactly what I thought that I wanted:

([class]![class_begin_date]>=#31/12/2006# And [class]![class_end_date]
<#01/01/2008#)


However, the output is all the records without limiting by dates.

What am I missing here?

Leah

 

[leahf via AccessMonster.com]

I got around the problem. However, if someone can tell me what is wrong with
the expression that I had, I would really appreciate it.


Leah

I have a query with a field expression that checks that a begin_date is
greater/equal to 31/12/2006 and the end date is less than 01/01/2008. The
criteria is TRUE. It works perfectly.

What I want to do now is to take the year from a form. The expression that I
used:

Expr1: "([class]![class_begin_date]>=#31/12/" & [Forms]!
[frm_rptNtivStatistics]![ComboYear]-1 & "# And [class]![class_end_date]
<#01/01/" & [Forms]![frm_rptNtivStatistics]![ComboYear]+1 & "#)"

In other words, if the user chooses the year 2007, then I should get the
expression that I need. When I ran the query itself (not in the report), I
got in the expression field exactly what I thought that I wanted:

([class]![class_begin_date]>=#31/12/2006# And [class]![class_end_date]
<#01/01/2008#)

However, the output is all the records without limiting by dates.

What am I missing here?

Leah

 

[John Spencer]

In your case I would have used the DateSerial function as criteria.

Class_Begin_Date >=
DateSerial(Forms![frm_rptNtivStatistics]![ComboYear]-1,12,31)
AND Class_End_Date
<DateSerial(Forms![frm_rptNtivStatistics]![ComboYear]+1,1,1)

Your expression would probably be more correctly written as
Expr1: [class]![class_begin_date]>= "#31/12/ " &
[Forms]![frm_rptNtivStatistics]![ComboYear]-1 & "# " And
[class]![class_end_date]
< "#01/01/" & [Forms]![frm_rptNtivStatistics]![ComboYear]+1 & "#"

By the way Access expects date literals to be in the US format of MM/DD/YYYY
or YYYY/MM/DD. Your dates happen to be unambiguous, but 1/11/2007 is
treated as January 11, 2007 not as November 1, 2007. See Allen Browne's
discussion at http://allenbrowne.com/ser-36.html
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I got around the problem. However, if someone can tell me what is wrong
with
the expression that I had, I would really appreciate it.


Leah

I have a query with a field expression that checks that a begin_date is
greater/equal to 31/12/2006 and the end date is less than 01/01/2008. The
criteria is TRUE. It works perfectly.

What I want to do now is to take the year from a form. The expression that
I
used:

Expr1: "([class]![class_begin_date]>=#31/12/" & [Forms]!
[frm_rptNtivStatistics]![ComboYear]-1 & "# And [class]![class_end_date]
<#01/01/" & [Forms]![frm_rptNtivStatistics]![ComboYear]+1 & "#)"

In other words, if the user chooses the year 2007, then I should get the
expression that I need. When I ran the query itself (not in the report),
I
got in the expression field exactly what I thought that I wanted:

([class]![class_begin_date]>=#31/12/2006# And [class]![class_end_date]
<#01/01/2008#)

However, the output is all the records without limiting by dates.

What am I missing here?

Leah

 

[leahf via AccessMonster.com]

Thank you very, very much.

As for the date format, I am in Israel and we follow the European system of
dates. And, yes, It does cause problems in updating files (we must remember
to reverse the month and day). However, once it's correct in the system it
is fine.

Thanks again.
Leah

In your case I would have used the DateSerial function as criteria.

Class_Begin_Date >=
DateSerial(Forms![frm_rptNtivStatistics]![ComboYear]-1,12,31)
AND Class_End_Date
<DateSerial(Forms![frm_rptNtivStatistics]![ComboYear]+1,1,1)

Your expression would probably be more correctly written as
Expr1: [class]![class_begin_date]>= "#31/12/ " &
[Forms]![frm_rptNtivStatistics]![ComboYear]-1 & "# " And
[class]![class_end_date]
< "#01/01/" & [Forms]![frm_rptNtivStatistics]![ComboYear]+1 & "#"

By the way Access expects date literals to be in the US format of MM/DD/YYYY
or YYYY/MM/DD. Your dates happen to be unambiguous, but 1/11/2007 is
treated as January 11, 2007 not as November 1, 2007. See Allen Browne's
discussion at http://allenbrowne.com/ser-36.html

I got around the problem. However, if someone can tell me what is wrong
with

[quoted text clipped - 27 lines]