Please help with Error 2427...

[Mike Vytal]

First, let me say, I've searched and searched, and have been trying to make sense of all the posts, but they all seem to point to reports and not forms.

What I'm trying to do is simple. On my main form, I have a checkbox called 'Inactive'. I have an Activities subform, which has a Class Date. If the class date is more than 1 yr old, I want the Inactive box to equal "Yes", but if there are no records in the Activities subform, I get the Error 2427, you entered an expression that has no value.

My code is below. What am I doing wrong? Thanks in advance!

If Nz(Me![ParticipantActivity subform].[Form]![ProgramClassDate], #1/1/2020#) > DateAdd("yyyy", -1, Date) Then
Me!Inactive = False
Else
Me!Inactive = True
End If

 

[pbaldy]

I use this to handle empty subforms:

http://www.mvps.org/access/forms/frm0022.htm

 

[Mike Vytal]

Thanx for your response Paul...

Would you suggest I use it in the main form or as a global function? And if on the main form, should it be under the on current?

Again, thanx for your help...

 

[Mike Vytal]

Still a bit confused...

Function nnz(testvalue As Variant) As Variant
'Not Numeric return zero
If Not (IsNumeric(testvalue)) Then
nnz = 0
Else
nnz = testvalue
End If
End Function


Should I create a field called testvalue or substitute testvalue with ProgramClassDate. How do I fit this into my scheme?

 

[pbaldy]

What do you want to happen if the subform has no records? One way to use it would be to use the nnz instead of the nz you already have, but I don't know if that fits what you want to happen.

 

[Mike Vytal]

Here's the scenario: There are Activities (subform) that Participants (main form) were involved in. If the activity took place over 1 year ago, they are considered Inactive (checkbox on main form). If there are no records in the Activities subform, I get the Error 2427 "You entered a value that has no expression.", I suspect because the Me.Inactive is looking for a date.

I want to get rid of this error and I want the 'Inactive' checkbox on the mainform to be blank. Maybe substitute the 'no records' with a future date, so the participant would not be considered inactive.

 

[Mike Vytal]

Paul,

Here's what I've done so far. In my query, I created a new field (PCDate: Nz([ProgramClassDate],#1/1/2020). That gets me past the Error 2427, but the Inactive box is box is blank on all records. Is there a way to format this new field as a date? I believe the statement 'Me!Inactive = False' is looking for a date.

 

[pbaldy]

If your existing function did what you want otherwise, I'd try this:

If Nz(Me![ParticipantActivity subform].[Form]![ProgramClassDate], #1/1/2020#) > DateAdd("yyyy", -1, Date) OR nnz(...) = 0 Then

 

[Mike Vytal]

Paul, I know I'm on the right track. Is there a way to format PCDate as a date field. My statement is based on the PCDate being greater than 1 year ago. The Inactive box isn't working because it's looking for a date and the Nz statement produces a string (I believe). I'm fairly new, so please bare with me. Thanks again for your help.

If Me![ParticipantActivity subform].[Form]![PCDate] > DateAdd("yyyy", -1, Date) Then
Me!Inactive = False

 

[pbaldy]

Try using CDate() to convert it to a date. Based on your earlier example:

PCDate: CDate(Nz([ProgramClassDate],#1/1/2020))

 

[Mike Vytal]

Paul, You Are The Man...that Worked Out Great!!! Thanks A Million!!!

 

[pbaldy]

No problem; glad it worked for you.