Combobox Search Form with Calculated Field

[bigal.nz]

Hi,

I have a main form with a subform for a search functionality using combobox lookup.

The combo boxes on the main form control which is displayed on the subform (datasheet).

One of the colums on the datasheet is set to be a calculated field based on an expression that looks like:

=Date Between StartDate AND DateAdd('yyyy', -6, DateStart)

This evaluates to 0 or -1 in the datasheet of the subform - but this is not helpful for the user. I would prefer to display True or False.

How do I fix this please?

 

[sneuberg]

Maybe

=CBool(Date Between StartDate AND DateAdd('yyyy', -6, DateStart))

or if not then maybe

 =CStr(CBool(Date Between StartDate AND DateAdd('yyyy', -6, DateStart)))

 

[sneuberg]

One more to try

=IIF(Date Between StartDate AND DateAdd('yyyy', -6, DateStart),"True","False")

 

[bigal.nz]

Thanks Steve,

I thought the IIF might be a winner but I only got False (before I was getting 0 and -1)

I am happy to use VBA but not sure what event to use.

Any ideas?

Al

 

[sneuberg]

While my suggestions in post #2 don't work there's no reason why the IIF version shouldn't. I have it working in the attached database with Date changed to Date() , i.e.,

=IIf(Date() Between [StartDate] And DateAdd('yyyy',-6,[DateStart]),"True","False")

If I left Date without the parenthesis is was assumed to be a field. Do you have a field named Date or do you mean today's date.

Could you explain what you want this expression to do? As I understand it current displays True if today's date is between StartDate and 6 years before DateStart. That doesn't make sense to me especially having fields named StartDate and DateStart. What do these mean?

 

[bigal.nz]

Yes, I got that to go now too.

By the way I confused you because I obfuscated my code in a way I shouldnt have. Its testing to see if at the time of date start the person was under 6.

Date() is actually a DOB field

Thanks again

Al

 

[sneuberg]

Now I'm going to being wondering all day what people in New Zealand do or don't do before the age of six.

 

[Minty]

Play Cricket or Rugby ?