Database Formula

[jedel]

Hi All
I'm having a problem with a formula that I'm using in one of my forms. BAsically, I want a number of fields to meet a certain criteria / value before a checkbox is checked. The formula I'm using is:

Private Sub Form_Current()
If Me![Deployability].Value > (Date - 365) Then
Me![Airn Compliant] = 1
Else
Me![Airn Compliant] = 0
End If
If Me![Lst Med Board (Doc)].Value > (Date - 1835) Then
Me![Airn Compliant] = 1
Else
Me![Airn Compliant] = 0
End If
If Me![Date LF9].Value > (Date - 365) Then
Me![Airn Compliant] = 1
Else
Me![Airn Compliant] = 0
End If
Select Case Me![MedClass]
Case "1", "2"
Me![Airn Compliant] = 1
Case "3", "4"
Me![Airn Compliant] = 0
End Select
Select Case Me![Dent Class]
Case "1", "2"
Me![Airn Compliant] = 1
Case "3", "4"
Me![Airn Compliant] = 0
End Select
If Me![Last Med Board (medic)].Value > (Date - 365) Then
Me![Airn Compliant] = 1
Else
Me![Airn Compliant] = 0
End If
If Me.Last_Dental_Board.Value > Date - 365 Then
Me.AIRN_Compliant.Value = True
Else
Me.AIRN_Compliant = False
End If
If Me.DatePTEP.Value <> &quot;&quot; Then
Me.AIRN_Compliant = 1
Else
Me.AIRN_Compliant = 0
End If
End Sub

The problem is that it treats each if Then, and case argument separately. For example, Take the last If, then statement. If I was to place this at the start of the argument, then the opposite number of records would be checked.

Any Suggestions?

Cheers

Jedel

 

[mvrijnsoever]

Hi Jedel

Try using a compliant memory value first. Define this variable on the top of your subroutine:

Dim stCompliant As Integer
Dim stRefuseText As Text
stCompliant = 1 'If value is not modified, all tests were succesfull



Rather than modifying your checkbox value directly, set the stCompliant to 0 if a test fails. You can also create an error message at the end of the routine with the stRefuseText value.

A example is:

If Not Me![Deployability].Value > (Date - 365) Then
stCompliant = 0
stRefuseText = stRefuseText & &quot; Deployability Value is not within a valid range.&quot;
End If

Then at the end set the checkbox value:

If stCompliant = 1 Then
Me![Airn Compliant] = 1
End if

Cheers,

marius

 

[jedel]

mvrijnsoever ,
Thanks for that input. I will try this. i have had some success by using the &quot;And&quot; statement and making one big argument. This did not allow me to note which test failed as yours does. One question though, what if more than one test fails, does the text message just keep appearing one after the other?

Cheers

Jedel