Grouping dates

[Guest]

I am trying to get a check box to action when a birthdate falls betwween two
dates given. I have only just started with expressions and i am obviously
missing something here. I have tried the following which keeps failing.
=IIF([Birthdate]>=#1/9/94# AND <=#31/8/95#,1,0). I can get it to work to
activate before a certain date but not looking between 2 dates.

 

[Guest]

Hi abebaby,

Try this:

IIf([Birthdate]>=#1/9/94# And [Birthdate]<=#31/8/95#,1,0)


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[John Vinson]

I am trying to get a check box to action when a birthdate falls betwween two
dates given. I have only just started with expressions and i am obviously
missing something here. I have tried the following which keeps failing.
=IIF([Birthdate]>=#1/9/94# AND <=#31/8/95#,1,0). I can get it to work to
activate before a certain date but not looking between 2 dates.

If you use literal dates, you must use either American mm/dd/yy (or
yyyy) format, or an unambiguous format such as 01-Sep-94 (in addition
to Tom's suggestion that you include the [Birthdate] field in both
comparisons).

John W. Vinson[MVP]

 

[Guest]

As well as what's been said already, you don't need to call the IIf function;
an expression which evaluates to True or False will do as the check box's
ControlSource property:

= [Birthdate]>=#09/01/1994# AND [Birthdate]<#09/01/1995#

This expression will be either True or False, so the check box will be
checked for the former, unchecked for the latter.

Testing for the end of the range with <#09/01/1995# rather then
<=#08/31/1995# makes it a little more bullet-proof as it caters for sloppy
data, where dates might have a non-zero time of day; these can easily creep
in without you being aware of it if the field is not constrained to reject
them – a common culprit is the inappropriate use of the Now() function.

BTW if you do use the IIF function you should get it to return a Boolean
TRUE or FALSE not 1 (or -1 which is actually the implementation of a Boolean
TRUE in Access) or 0:

= IIF([Birthdate]>=#09/01/1994# AND [Birthdate]<#09/01/1995#,TRUE, FALSE)

Reliance on the implementation is considered bad programming practice in
principle.

Ken Sheridan
Stafford, England

 

[Guest]

Thank you for your help, hopefully I should be able to finish my database
now. Cheers eveyone

As well as what's been said already, you don't need to call the IIf function;
an expression which evaluates to True or False will do as the check box's
ControlSource property:

= [Birthdate]>=#09/01/1994# AND [Birthdate]<#09/01/1995#

This expression will be either True or False, so the check box will be
checked for the former, unchecked for the latter.

Testing for the end of the range with <#09/01/1995# rather then
<=#08/31/1995# makes it a little more bullet-proof as it caters for sloppy
data, where dates might have a non-zero time of day; these can easily creep
in without you being aware of it if the field is not constrained to reject
them – a common culprit is the inappropriate use of the Now() function.

BTW if you do use the IIF function you should get it to return a Boolean
TRUE or FALSE not 1 (or -1 which is actually the implementation of a Boolean
TRUE in Access) or 0:

= IIF([Birthdate]>=#09/01/1994# AND [Birthdate]<#09/01/1995#,TRUE, FALSE)

Reliance on the implementation is considered bad programming practice in
principle.

Ken Sheridan
Stafford, England

I am trying to get a check box to action when a birthdate falls betwween two
dates given. I have only just started with expressions and i am obviously
missing something here. I have tried the following which keeps failing.
=IIF([Birthdate]>=#1/9/94# AND <=#31/8/95#,1,0). I can get it to work to
activate before a certain date but not looking between 2 dates.