Three Possibilities in a Control Source

[datasec23]

Hi...

I am working in MS Access 2000 and I have a field where depending on the values of other fields, I would like the field to return a particular word. Ideally, I would like the statement to be in the control source, but I have moved to trying to have the coding in VB. It seems my problem is with the "between" statement in my code in the third paragraph. I have posted my code below. I did not find an answer on the forum, so any help would be appreciated. I am sure this is not the optimal way to go about this..........


If ([ScoreCard Query]![1/3 Forearm T score] Or [ScoreCard Query]![Ap spine T Score] Or [ScoreCard Query]![Total Hip T Score] < "-2.5") Then Me.BoneDensityResult = "Osteoperosis"

If ([ScoreCard Query]![1/3 Forearm T score] Or [ScoreCard Query]![Ap spine T Score] Or [ScoreCard Query]![Total Hip T Score] between "-1.0" and "-2.5") Then Me.BoneDensityResult = "Osteopenia"

If ([ScoreCard Query]![1/3 Forearm T score] Or [ScoreCard Query]![Ap spine T Score] Or [ScoreCard Query]![Total Hip T Score] > "1.0") Then Me.BoneDensityResult = "Normal"

EndIF

End Sub

[Scott Price]

Hi...

I am working in MS Access 2000 and I have a field where depending on the values of other fields, I would like the field to return a particular word. Ideally, I would like the statement to be in the control source, but I have moved to trying to have the coding in VB. It seems my problem is with the "between" statement in my code in the third paragraph. I have posted my code below. I did not find an answer on the forum, so any help would be appreciated. I am sure this is not the optimal way to go about this..........

If ([ScoreCard Query]![1/3 Forearm T score] Or [ScoreCard Query]![Ap spine T Score] Or [ScoreCard Query]![Total Hip T Score] < "-2.5") Then Me.BoneDensityResult = "Osteoperosis"
 
If ([ScoreCard Query]![1/3 Forearm T score] Or [ScoreCard Query]![Ap spine T Score] Or [ScoreCard Query]![Total Hip T Score] between "-1.0" and "-2.5") Then Me.BoneDensityResult = "Osteopenia"
 
If ([ScoreCard Query]![1/3 Forearm T score] Or [ScoreCard Query]![Ap spine T Score] Or [ScoreCard Query]![Total Hip T Score] > "1.0") Then Me.BoneDensityResult = "Normal"
 
EndIF
 

End Sub

What is the nature of the [1/3 Forearm T score] and [Ap spine T Score] and [Total Hip T Score] fields? Are they text boxes, etc on a form?

Is [ScoreCard Query] a form or a query?

Are the results of each field a numeric value or a string value or True/False boolean values?

What would be some sample data for each of these three fields?

I think I'll end up suggesting a Select Case statement for this, which is just a wee teensy bit more 'elegant' than multiple If--Then statements, even though the If--Then's will work fine also!

Regards,
Scott

[datasec23]

Scott...

The data is entered as numeric values (Double). The ScoreCard Query is a query. Basically, I enter numbers on a form, and have the output in a report. In between, I pull the data from a query based on information from the form. The code above is an attempt to manipulate the field output by using the IF statements.

Thanks so much....

[Scott Price]

Sorry to take so long... I'm assuming that you want any result on any of the three T Scores greater than -2.5 to show as Osteoporosis? Results from -1 to -2.5 inclusive will show Osteopenia, and results less than -1 will show Normal.

This function works fine in my test db on test data.

Public Function OsteoResults(ResultID As Integer) As String
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Message As String
Dim MyVar As Double
Dim MyVar1 As Double
Dim MyVar2 As Double
 
Set db = CurrentDb
Set rs = db.OpenRecordset("ScoreCard Query", dbOpenTable)
 
With rs
    .Index = "ResultID"
    .Seek "=", ResultID
    If .NoMatch = False Then
        MyVar = .Fields![1/3 Forearm T Score]
        Select Case MyVar
            Case Is < -2.5
                Message = "Osteoporosis"
            Case -2.5 To -1
                Message = "Osteopenia"
            Case Is > -1
                Message = "Normal"
        End Select
        Select Case Message
            Case Is <> "Osteoporosis"
                MyVar1 = .Fields![Ap Spine T Score]
                    Select Case MyVar1
                        Case Is < -2.5
                            Message = "Osteoporosis"
                        Case -2.5 To -1
                            Message = "Osteopenia"
                        Case Is > -1
                            Message = "Normal"
                    End Select
            End Select
        Select Case Message
            Case Is <> "Osteoporosis"
                MyVar2 = .Fields![Total Hip T Score]
                    Select Case MyVar2
                        Case Is < -2.5
                            Message = "Osteoporosis"
                        Case -2.5 To -1
                            Message = "Osteopenia"
                        Case Is > -1
                            Message = "Normal"
                    End Select
        End Select
      End If
      .Close
  End With
Set rs = Nothing
OsteoResults = Message
End Function
 

To use, create a standard code module in your vba editor window. Copy and paste after carefully making sure that each field name corresponds to the names you have in your database. Change any that need changing. I used this code from a table named ScoreCard Query with a primary key field of ResultID.

Now this function will be available whereever you want to use it in your database. You can use it in a query, form, etc. I made an unbound text box on a form, and in the On Current event of the form I called the function with this code:

If Not IsNull(Me!ResultID) Then
    Me.Text4 = OsteoResults(Me!ResultID)
End If
 

Any questions, just ask!

Regards,
Scott

[datasec23]

Scott...thank you so much for taking the time to do this!! I did as you said and pasted the code into a VB module and changed the table name and instead of ResultID I have Patient ID so I changed that too. Now my errors are different. When I go in to the form the code starts debugging and I get a "Compile error: User-defined type not defined". I have pasted the edited code below:

Public Function OsteoResults(Patient_ID As Integer) As String
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Message As String
Dim MyVar As Double
Dim MyVar1 As Double
Dim MyVar2 As Double
 
Set db = CurrentDb
Set rs = db.OpenRecordset("Visit Table-baseline", dbOpenTable)
 
With rs
    .Index = "Patient ID"
    .Seek "=", "Patient ID"
    If .NoMatch = False Then
        MyVar = .Fields![1/3 Forearm T score]
        Select Case MyVar
            Case Is < -2.5
                Message = "Osteoporosis"
            Case -2.5 To -1
                Message = "Osteopenia"
            Case Is > -1
                Message = "Normal"
        End Select
        Select Case Message
            Case Is <> "Osteoporosis"
                MyVar1 = .Fields![Ap spine T Score]
                    Select Case MyVar1
                        Case Is < -2.5
                            Message = "Osteoporosis"
                        Case -2.5 To -1
                            Message = "Osteopenia"
                        Case Is > -1
                            Message = "Normal"
                    End Select
            End Select
        Select Case Message
            Case Is <> "Osteoporosis"
                MyVar2 = .Fields![Total Hip T Score]
                    Select Case MyVar2
                        Case Is < -2.5
                            Message = "Osteoporosis"
                        Case -2.5 To -1
                            Message = "Osteopenia"
                        Case Is > -1
                            Message = "Normal"
                    End Select
        End Select
      End If
      .Close
  End With
Set rs = Nothing
OsteoResults = Message
End Function

Thanks again for all your help!!!!

[Scott Price]

When you click (in VBA editor window) Debug>Compile, what line of code doesn't it like?

Regards,
Scott

[Scott Price]

In line 1 of your code you are referring to Patient_ID, but in lines 14 and 15 your are referring to Patient ID, without the underscore.

Also looking at a possible problem in lines 14, 15 and 11... Whenever referring to a table name or field name that contains nonstandard characters (spaces, etc.) they should be enclosed in square brackets: []

Let me know if this works!

Regards,
Scott

[datasec23]

Hi Scott,

With a little tweaking I was able to make it work great! The only place it gets hung up is when it comes across any of the fields with a Null value. Any thoughts on how to get over this? I have tried inserting IsNull values into the code, but with no luck.....

[Scott Price]

Hi Scott,

With a little tweaking I was able to make it work great! The only place it gets hung up is when it comes across any of the fields with a Null value. Any thoughts on how to get over this? I have tried inserting IsNull values into the code, but with no luck.....

Hmm... I had made my fields with a default value of 0, so there never will be a null result passed to the function, however I see now after thinking further that 0 isn't really the best solution, since a result of 0 will give a "Normal" value for the test.

Let me think this over a bit and see what I can come up with.

Regards,
Scott

[Scott Price]

Alright, Have a try with this code:

Public Function OsteoResults(Patient_ID As Integer) As String
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Message As String
Dim MyVar As Double
Dim MyVar1 As Double
Dim MyVar2 As Double
 
Set db = CurrentDb
Set rs = db.OpenRecordset("ScoreCard Query", dbOpenTable)
 
With rs
    .Index = "Patient_ID"
    .Seek "=", Patient_ID
 
    If .NoMatch = False Then
        MyVar = .Fields![1/3 Forearm T Score]
        If Not IsNull(MyVar) Then
            Select Case MyVar
                Case Is < -2.5
                    Message = "Osteoporosis"
                Case -2.5 To -1
                    Message = "Osteopenia"
                Case Is > -1
                    Message = "Normal"
            End Select
        End If
 
        Select Case Message
            Case Is <> "Osteoporosis"
                MyVar1 = .Fields![Ap Spine T Score]
                    If Not IsNull(MyVar1) Then
                        Select Case MyVar1
                            Case Is < -2.5
                                Message = "Osteoporosis"
                            Case -2.5 To -1
                                Message = "Osteopenia"
                            Case Is > -1
                                Message = "Normal"
                        End Select
                    End If
            End Select
 
        Select Case Message
            Case Is <> "Osteoporosis"
                MyVar2 = .Fields![Total Hip T Score]
                    If Not IsNull(MyVar2) Then
                        Select Case MyVar2
                            Case Is < -2.5
                                Message = "Osteoporosis"
                            Case -2.5 To -1
                                Message = "Osteopenia"
                            Case Is > -1
                                Message = "Normal"
                        End Select
                    End If
        End Select
      End If
      .Close
  End With
Set rs = Nothing
OsteoResults = Message
End Function
 

Basically all I did was add the If Not IsNull(MyVar) Then... Lines.

Hope this works for you!

Regards,
Scott

[datasec23]

Thanks Scott...so in the code you tell it that if it is not Null go through the case statements, but shouldn't I tell it to skip that field and go to the next case statement in the code if it is Null? I would think I would have to send the code somewhere if that field is Null?

[datasec23]

I am still getting a "Run-time Error 94" Invalid use of Null.

Thanks!

[Scott Price]

I am still getting a "Run-time Error 94" Invalid use of Null.

Thanks!

Copy and paste the code you're using now :) Also please indicate which line is highlighted by the Error 94 message.

The way I wrote it, the Select statements only fire if the field value is NOT null. That way, if one IS null, it skips the Select and goes to the next If Not IsNull() statement, tests for Null again, and only fires the Select if NOT null.

This worked just fine in the test db I've set up.

Regards,
Scott

[datasec23]

Being a newbie...I had to cut down some of your code, because I wasn't sure what it all meant. Someday I hope to be able to write code like you did :) Everything works fine except for Null. Here is the edited code. This is the line that gets highlighted MyVar = Forearm

Dim Message As String
Dim MyVar As Double
Dim MyVar1 As Double
Dim MyVar2 As Double
Dim MyVar3 As Double
 
Forearm = Forms![Scorecard-form].[1/3 Forearm T score]
AP = Forms![Scorecard-form].[Ap spine T Score]
HIP = Forms![Scorecard-form].[Total Hip T Score]
FEM = Forms![Scorecard-form].[Femoral Neck T Score]
 
        MyVar = Forearm
        If Not IsNull(Forearm) Then
          Select Case MyVar
            Case Is < -2.5
                Message = "Osteoporosis"
            Case -2.5 To -1
                Message = "Osteopenia"
            Case Is > -1
                Message = "Normal"
            Case Is = ""
                Message = "Not Evaluated"
            End Select
        End If
        Select Case Message
            Case Is <> "Osteoporosis"
                MyVar1 = AP
                If Not IsNull(AP) Then
                    Select Case MyVar1
                        Case Is < -2.5
                            Message = "Osteoporosis"
                        Case -2.5 To -1
                            Message = "Osteopenia"
                        Case Is > -1
                            Message = "Normal"
                        Case Is = ""
                            Message = "Not Evaluated"
                    End Select
                End If
                End Select
        Select Case Message
            Case Is <> "Osteoporosis"
                MyVar2 = HIP
                If Not IsNull(HIP) Then
                    Select Case MyVar2
                        Case Is < -2.5
                            Message = "Osteoporosis"
                        Case -2.5 To -1
                            Message = "Osteopenia"
                        Case Is > -1
                            Message = "Normal"
                        Case Is = ""
                            Message = "Not Evaluated"
                    End Select
                End If
        End Select
          Select Case Message
            Case Is <> "Osteoporosis"
                MyVar3 = FEM
                If Not IsNull(FEM) Then
                    Select Case MyVar3
                        Case Is < -2.5
                            Message = "Osteoporosis"
                        Case -2.5 To -1
                            Message = "Osteopenia"
                        Case Is > -1
                            Message = "Normal"
                        Case Is = ""
                            Message = "Not Evaluated"
                    End Select
                End If
        End Select
            OsteoResults = Message
End Function

Thanks again for all of your help..........

[Scott Price]

        Dim Message As String
        Dim Forearm As Double
        Dim AP As Double
        Dim HIP As Double
        Dim FEM As Double
 
        Forearm = Forms![Scorecard-form].[1/3 Forearm T score]
        AP = Forms![Scorecard-form].[Ap spine T Score]
        HIP = Forms![Scorecard-form].[Total Hip T Score]
        FEM = Forms![Scorecard-form].[Femoral Neck T Score]
 
                 If Not IsNull(Forearm) Then
                  Select Case Forearm
                    Case Is < -2.5
                        Message = "Osteoporosis"
                    Case -2.5 To -1
                        Message = "Osteopenia"
                    Case Is > -1
                        Message = "Normal"
                    Case Is = ""
                        Message = "Not Evaluated"
                    End Select
                End If
                Select Case Message
                    Case Is <> "Osteoporosis"
                         If Not IsNull(AP) Then
                            Select Case AP
                                Case Is < -2.5
                                    Message = "Osteoporosis"
                                Case -2.5 To -1
                                    Message = "Osteopenia"
                                Case Is > -1
                                    Message = "Normal"
                                Case Is = ""
                                    Message = "Not Evaluated"
                            End Select
                        End If
                        End Select
                Select Case Message
                    Case Is <> "Osteoporosis"
                        If Not IsNull(HIP) Then
                            Select Case HIP
                                Case Is < -2.5
                                    Message = "Osteoporosis"
                                Case -2.5 To -1
                                    Message = "Osteopenia"
                                Case Is > -1
                                    Message = "Normal"
                                Case Is = ""
                                    Message = "Not Evaluated"
                            End Select
                        End If
                End Select
                  Select Case Message
                    Case Is <> "Osteoporosis"
                        If Not IsNull(FEM) Then
                            Select Case FEM
                                Case Is < -2.5
                                    Message = "Osteoporosis"
                                Case -2.5 To -1
                                    Message = "Osteopenia"
                                Case Is > -1
                                    Message = "Normal"
                                Case Is = ""
                                    Message = "Not Evaluated"
                            End Select
                        End If
                End Select
                    OsteoResults = Message
        End Function

Try this.

Regards,
Scott