If…Then statement question (Access 2K)

Topic

New Reply

I was wondering, if I could set up an If…Then statement to check a field which tells me which year the participant attended the retreat and then “hide” the experience field on the report if the the attend date is older than a year.

I want to do something like this: If John Doe attended the retreat in 2001, then his experience field is set to Not Visible.

TIA.

Reply | Quote

Replies

Just put your code in the Format Event of the report’s Detail section.
If …. your conditions… Then
txtexperience.visible = False
Else
txtexperience.visible = True
End if

Reply | Quote

Actually, it is the condition itself that I’m having problem with. How do I put it in proper syntax? I was thinking,

If Me!txtLastTimeAttend > DatePart(YYYY) Then
txtexperience.visible=false
Else
txtexperience.visible= True

Is this correct syntax?

Reply | Quote

Your criteria would be (assuming LastTimeAttend is a valid date field):

If Me!txtLastTimeAttend > Date - 365 Then
    txtexperience.Visible = True
Else
    txtexperience.Visible = False
End If 

In above example, if today is 31 July 2002, if the Attend date is 1 Aug 2001 or later txtExperience will be visible. The “Date” function returns current date. Dates are saved as numbers; 1 = one day.

HTH

Reply | Quote

Your syntax is wrong regardless of what your intentions. DatePart is wrong, and you are trying to compare a date to part of a date. Your first post said to make the textbox invisible if he last attended in 2001, so you would use:

If DatePart(“yyyy”,txtLastDateAttended) = 2001 then
‘… etc.

Of course, this checks that the last date attended was in 2001. If you want it to be BEFORE 2002, you would use <2002.

Reply | Quote

It works, but now I’m left with a gaping hole in middle of my report. Is it possible to somehow force the lines to “shrink” together? Right now, the experience text is set to Grow and Shrink = Yes but if there is lengthy text and it is set to Not Visible, it is still forcing lines of data apart, leaving huge gaps in the middle because the text is “still there”.

Reply | Quote

Is your Detail section set to AllowShrink=Yes?

Reply | Quote

I have it set to Yes, still see a huge gap between two records. It is because the text field is pushing two records apart, even though it is set to Not Visible.

Brent

Reply | Quote

You might try setting the height to 0 and Grow=No if you set it to invisible, and Grow=Yes if visible.

Or, you could change your controlsource to be an expression, something like:
IIF( datepart(“yyyy”,datelastattended=2001, null, experience)

Reply | Quote