Making a field invisible

[Doug]

Hi,

I'm trying to make a date field invisible (or fore color
white) on a report if it doesn't fall between a begin and
an end date, but visible if it does fall between the
range. So, 1 record it might be visible and the next it
might not be on the same report So far I have


If Not Me![6-Month].Value > Forms!frmFilterCriteria!
BeginDate And Not [6-Month].Value < Forms!
frmFilterCriteria!EndDate) Then
Me![6-Month].Visible = False
End If

I can't get between to work properly.

My report is filtered on another date field, so I can't
just limit the recordsource by the 6-month field. But
the user doesn't want it included if it doesn't also fall
within the date range.

Thanks in advance for your help.

Doug

 

[Marshall Barton]

I'm trying to make a date field invisible (or fore color
white) on a report if it doesn't fall between a begin and
an end date, but visible if it does fall between the
range. So, 1 record it might be visible and the next it
might not be on the same report So far I have


If Not Me![6-Month].Value > Forms!frmFilterCriteria!
BeginDate And Not [6-Month].Value < Forms!
frmFilterCriteria!EndDate) Then
Me![6-Month].Visible = False
End If

I can't get between to work properly.

My report is filtered on another date field, so I can't
just limit the recordsource by the 6-month field. But
the user doesn't want it included if it doesn't also fall
within the date range.

I think we should work on the report's record source query
and figure out how to filter out the undesired records.

Please post back with the current query (Copy/Paste SQL
view) along with an explanation of how the between condition
interacts with the existing criteria.

 

[Doug]

Thanks for the reply and help. I don't think affecting
the recordsource will help in this situation because
there are 9 other date fields in the report that the user
only wants to see if they also fall within the date range
of 1 field, the Next Comp Date. But the recordsource is
based upon the Next Comp date and not the other date
fields. The user would rather see nothing for the 9 date
fields than a date that doesn't fall within the date
range of the Next Comp date, but the record would still
be in the report if the Next Comp Date field was within
the date range. I figured it would be easier if I could
just make the data visible or not visible, because the 9
date fields shouldn't affect the report's overall
recordsource.

Thanks again,

Doug

-----Original Message-----

I'm trying to make a date field invisible (or fore color
white) on a report if it doesn't fall between a begin and
an end date, but visible if it does fall between the
range. So, 1 record it might be visible and the next it
might not be on the same report So far I have


If Not Me![6-Month].Value > Forms!frmFilterCriteria!
BeginDate And Not [6-Month].Value < Forms!
frmFilterCriteria!EndDate) Then
Me![6-Month].Visible = False
End If

I can't get between to work properly.

My report is filtered on another date field, so I can't
just limit the recordsource by the 6-month field. But
the user doesn't want it included if it doesn't also fall
within the date range.

I think we should work on the report's record source query
and figure out how to filter out the undesired records.

Please post back with the current query (Copy/Paste SQL
view) along with an explanation of how the between condition
interacts with the existing criteria.

 

[Marshall Barton]

Thanks for the reply and help. I don't think affecting
the recordsource will help in this situation because
there are 9 other date fields in the report that the user
only wants to see if they also fall within the date range
of 1 field, the Next Comp Date. But the recordsource is
based upon the Next Comp date and not the other date
fields. The user would rather see nothing for the 9 date
fields than a date that doesn't fall within the date
range of the Next Comp date, but the record would still
be in the report if the Next Comp Date field was within
the date range. I figured it would be easier if I could
just make the data visible or not visible, because the 9
date fields shouldn't affect the report's overall
recordsource.

OK, that's what your original code was trying to do, but it
had the boolean logic mixed up. I think all you needed to
do was add parenthesis around everthing except the first Not
and get rid of the second Not.

Alternatively, you could just change the And to an Or.

 

[Doug]

Thanks for the help. I have the following If Not (Me!
[6month] > Forms!frmFilterCriteria!BeginDate And Me!
[6month] < Forms!frmFilterCriteria!EndDate) Then
Me![6month].ForeColor = 16777215

End If

which works, but when i put it in the open event it
limits the report to 1 record. Is there a way to include
the code without limiting the records?

Thanks again

 

[Marshall Barton]

Thanks for the help. I have the following If Not (Me!
[6month] > Forms!frmFilterCriteria!BeginDate And Me!
[6month] < Forms!frmFilterCriteria!EndDate) Then
Me![6month].ForeColor = 16777215

End If

which works, but when i put it in the open event it
limits the report to 1 record. Is there a way to include
the code without limiting the records?

Well, I never heard of that effect before, but you should
not try to refer to a control or field's value in the open
event. Try moving the code to the Format event of the
section containing the [6Month] text box.

There's no reason to be messing with the ForeColor, just set
the text box's Visible property to False.

OTOH, do you need to set it to True when then date is in the
range? If so, you could use this:

Me![6month].Visible = (Me![6month] >
Forms!frmFilterCriteria!BeginDate And Me![6month] <
Forms!frmFilterCriteria!EndDate)
--
Marsh
MVP [MS Access]