By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,508 Members | 2,949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,508 IT Pros & Developers. It's quick & easy.

Three Possibilities in a Control Source

P: 16
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
Sep 17 '07 #1
Share this Question
Share on Google+
14 Replies


Scott Price
Expert 100+
P: 1,384
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..........


Expand|Select|Wrap|Line Numbers
  1. 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"
  2.  
  3. 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"
  4.  
  5. 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"
  6.  
  7. EndIF
  8.  
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
Sep 17 '07 #2

P: 16
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....
Sep 17 '07 #3

Scott Price
Expert 100+
P: 1,384
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function OsteoResults(ResultID As Integer) As String
  2.  
  3. Dim db As DAO.Database
  4. Dim rs As DAO.Recordset
  5. Dim Message As String
  6. Dim MyVar As Double
  7. Dim MyVar1 As Double
  8. Dim MyVar2 As Double
  9.  
  10. Set db = CurrentDb
  11. Set rs = db.OpenRecordset("ScoreCard Query", dbOpenTable)
  12.  
  13. With rs
  14.     .Index = "ResultID"
  15.     .Seek "=", ResultID
  16.     If .NoMatch = False Then
  17.         MyVar = .Fields![1/3 Forearm T Score]
  18.         Select Case MyVar
  19.             Case Is < -2.5
  20.                 Message = "Osteoporosis"
  21.             Case -2.5 To -1
  22.                 Message = "Osteopenia"
  23.             Case Is > -1
  24.                 Message = "Normal"
  25.         End Select
  26.         Select Case Message
  27.             Case Is <> "Osteoporosis"
  28.                 MyVar1 = .Fields![Ap Spine T Score]
  29.                     Select Case MyVar1
  30.                         Case Is < -2.5
  31.                             Message = "Osteoporosis"
  32.                         Case -2.5 To -1
  33.                             Message = "Osteopenia"
  34.                         Case Is > -1
  35.                             Message = "Normal"
  36.                     End Select
  37.             End Select
  38.         Select Case Message
  39.             Case Is <> "Osteoporosis"
  40.                 MyVar2 = .Fields![Total Hip T Score]
  41.                     Select Case MyVar2
  42.                         Case Is < -2.5
  43.                             Message = "Osteoporosis"
  44.                         Case -2.5 To -1
  45.                             Message = "Osteopenia"
  46.                         Case Is > -1
  47.                             Message = "Normal"
  48.                     End Select
  49.         End Select
  50.       End If
  51.       .Close
  52.   End With
  53. Set rs = Nothing
  54. OsteoResults = Message
  55. End Function
  56.  
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:

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me!ResultID) Then
  2.     Me.Text4 = OsteoResults(Me!ResultID)
  3. End If
  4.  
Any questions, just ask!

Regards,
Scott
Sep 17 '07 #4

P: 16
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function OsteoResults(Patient_ID As Integer) As String
  2.  
  3. Dim db As DAO.Database
  4. Dim rs As DAO.Recordset
  5. Dim Message As String
  6. Dim MyVar As Double
  7. Dim MyVar1 As Double
  8. Dim MyVar2 As Double
  9.  
  10. Set db = CurrentDb
  11. Set rs = db.OpenRecordset("Visit Table-baseline", dbOpenTable)
  12.  
  13. With rs
  14.     .Index = "Patient ID"
  15.     .Seek "=", "Patient ID"
  16.     If .NoMatch = False Then
  17.         MyVar = .Fields![1/3 Forearm T score]
  18.         Select Case MyVar
  19.             Case Is < -2.5
  20.                 Message = "Osteoporosis"
  21.             Case -2.5 To -1
  22.                 Message = "Osteopenia"
  23.             Case Is > -1
  24.                 Message = "Normal"
  25.         End Select
  26.         Select Case Message
  27.             Case Is <> "Osteoporosis"
  28.                 MyVar1 = .Fields![Ap spine T Score]
  29.                     Select Case MyVar1
  30.                         Case Is < -2.5
  31.                             Message = "Osteoporosis"
  32.                         Case -2.5 To -1
  33.                             Message = "Osteopenia"
  34.                         Case Is > -1
  35.                             Message = "Normal"
  36.                     End Select
  37.             End Select
  38.         Select Case Message
  39.             Case Is <> "Osteoporosis"
  40.                 MyVar2 = .Fields![Total Hip T Score]
  41.                     Select Case MyVar2
  42.                         Case Is < -2.5
  43.                             Message = "Osteoporosis"
  44.                         Case -2.5 To -1
  45.                             Message = "Osteopenia"
  46.                         Case Is > -1
  47.                             Message = "Normal"
  48.                     End Select
  49.         End Select
  50.       End If
  51.       .Close
  52.   End With
  53. Set rs = Nothing
  54. OsteoResults = Message
  55. End Function
Thanks again for all your help!!!!
Sep 19 '07 #5

Scott Price
Expert 100+
P: 1,384
When you click (in VBA editor window) Debug>Compile, what line of code doesn't it like?

Regards,
Scott
Sep 19 '07 #6

Scott Price
Expert 100+
P: 1,384
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
Sep 19 '07 #7

P: 16
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.....
Sep 20 '07 #8

Scott Price
Expert 100+
P: 1,384
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
Sep 20 '07 #9

Scott Price
Expert 100+
P: 1,384
Alright, Have a try with this code:

Expand|Select|Wrap|Line Numbers
  1. Public Function OsteoResults(Patient_ID As Integer) As String
  2.  
  3. Dim db As DAO.Database
  4. Dim rs As DAO.Recordset
  5. Dim Message As String
  6. Dim MyVar As Double
  7. Dim MyVar1 As Double
  8. Dim MyVar2 As Double
  9.  
  10. Set db = CurrentDb
  11. Set rs = db.OpenRecordset("ScoreCard Query", dbOpenTable)
  12.  
  13. With rs
  14.     .Index = "Patient_ID"
  15.     .Seek "=", Patient_ID
  16.  
  17.     If .NoMatch = False Then
  18.         MyVar = .Fields![1/3 Forearm T Score]
  19.         If Not IsNull(MyVar) Then
  20.             Select Case MyVar
  21.                 Case Is < -2.5
  22.                     Message = "Osteoporosis"
  23.                 Case -2.5 To -1
  24.                     Message = "Osteopenia"
  25.                 Case Is > -1
  26.                     Message = "Normal"
  27.             End Select
  28.         End If
  29.  
  30.         Select Case Message
  31.             Case Is <> "Osteoporosis"
  32.                 MyVar1 = .Fields![Ap Spine T Score]
  33.                     If Not IsNull(MyVar1) Then
  34.                         Select Case MyVar1
  35.                             Case Is < -2.5
  36.                                 Message = "Osteoporosis"
  37.                             Case -2.5 To -1
  38.                                 Message = "Osteopenia"
  39.                             Case Is > -1
  40.                                 Message = "Normal"
  41.                         End Select
  42.                     End If
  43.             End Select
  44.  
  45.         Select Case Message
  46.             Case Is <> "Osteoporosis"
  47.                 MyVar2 = .Fields![Total Hip T Score]
  48.                     If Not IsNull(MyVar2) Then
  49.                         Select Case MyVar2
  50.                             Case Is < -2.5
  51.                                 Message = "Osteoporosis"
  52.                             Case -2.5 To -1
  53.                                 Message = "Osteopenia"
  54.                             Case Is > -1
  55.                                 Message = "Normal"
  56.                         End Select
  57.                     End If
  58.         End Select
  59.       End If
  60.       .Close
  61.   End With
  62. Set rs = Nothing
  63. OsteoResults = Message
  64. End Function
  65.  
Basically all I did was add the If Not IsNull(MyVar) Then... Lines.

Hope this works for you!

Regards,
Scott
Sep 20 '07 #10

P: 16
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?
Sep 20 '07 #11

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

Thanks!
Sep 20 '07 #12

Scott Price
Expert 100+
P: 1,384
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
Sep 20 '07 #13

P: 16
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

Expand|Select|Wrap|Line Numbers
  1. Dim Message As String
  2. Dim MyVar As Double
  3. Dim MyVar1 As Double
  4. Dim MyVar2 As Double
  5. Dim MyVar3 As Double
  6.  
  7. Forearm = Forms![Scorecard-form].[1/3 Forearm T score]
  8. AP = Forms![Scorecard-form].[Ap spine T Score]
  9. HIP = Forms![Scorecard-form].[Total Hip T Score]
  10. FEM = Forms![Scorecard-form].[Femoral Neck T Score]
  11.  
  12.         MyVar = Forearm
  13.         If Not IsNull(Forearm) Then
  14.           Select Case MyVar
  15.             Case Is < -2.5
  16.                 Message = "Osteoporosis"
  17.             Case -2.5 To -1
  18.                 Message = "Osteopenia"
  19.             Case Is > -1
  20.                 Message = "Normal"
  21.             Case Is = ""
  22.                 Message = "Not Evaluated"
  23.             End Select
  24.         End If
  25.         Select Case Message
  26.             Case Is <> "Osteoporosis"
  27.                 MyVar1 = AP
  28.                 If Not IsNull(AP) Then
  29.                     Select Case MyVar1
  30.                         Case Is < -2.5
  31.                             Message = "Osteoporosis"
  32.                         Case -2.5 To -1
  33.                             Message = "Osteopenia"
  34.                         Case Is > -1
  35.                             Message = "Normal"
  36.                         Case Is = ""
  37.                             Message = "Not Evaluated"
  38.                     End Select
  39.                 End If
  40.                 End Select
  41.         Select Case Message
  42.             Case Is <> "Osteoporosis"
  43.                 MyVar2 = HIP
  44.                 If Not IsNull(HIP) Then
  45.                     Select Case MyVar2
  46.                         Case Is < -2.5
  47.                             Message = "Osteoporosis"
  48.                         Case -2.5 To -1
  49.                             Message = "Osteopenia"
  50.                         Case Is > -1
  51.                             Message = "Normal"
  52.                         Case Is = ""
  53.                             Message = "Not Evaluated"
  54.                     End Select
  55.                 End If
  56.         End Select
  57.           Select Case Message
  58.             Case Is <> "Osteoporosis"
  59.                 MyVar3 = FEM
  60.                 If Not IsNull(FEM) Then
  61.                     Select Case MyVar3
  62.                         Case Is < -2.5
  63.                             Message = "Osteoporosis"
  64.                         Case -2.5 To -1
  65.                             Message = "Osteopenia"
  66.                         Case Is > -1
  67.                             Message = "Normal"
  68.                         Case Is = ""
  69.                             Message = "Not Evaluated"
  70.                     End Select
  71.                 End If
  72.         End Select
  73.             OsteoResults = Message
  74. End Function
Thanks again for all of your help..........
Sep 20 '07 #14

Scott Price
Expert 100+
P: 1,384
Expand|Select|Wrap|Line Numbers
  1.         Dim Message As String
  2.         Dim Forearm As Double
  3.         Dim AP As Double
  4.         Dim HIP As Double
  5.         Dim FEM As Double
  6.  
  7.         Forearm = Forms![Scorecard-form].[1/3 Forearm T score]
  8.         AP = Forms![Scorecard-form].[Ap spine T Score]
  9.         HIP = Forms![Scorecard-form].[Total Hip T Score]
  10.         FEM = Forms![Scorecard-form].[Femoral Neck T Score]
  11.  
  12.                  If Not IsNull(Forearm) Then
  13.                   Select Case Forearm
  14.                     Case Is < -2.5
  15.                         Message = "Osteoporosis"
  16.                     Case -2.5 To -1
  17.                         Message = "Osteopenia"
  18.                     Case Is > -1
  19.                         Message = "Normal"
  20.                     Case Is = ""
  21.                         Message = "Not Evaluated"
  22.                     End Select
  23.                 End If
  24.                 Select Case Message
  25.                     Case Is <> "Osteoporosis"
  26.                          If Not IsNull(AP) Then
  27.                             Select Case AP
  28.                                 Case Is < -2.5
  29.                                     Message = "Osteoporosis"
  30.                                 Case -2.5 To -1
  31.                                     Message = "Osteopenia"
  32.                                 Case Is > -1
  33.                                     Message = "Normal"
  34.                                 Case Is = ""
  35.                                     Message = "Not Evaluated"
  36.                             End Select
  37.                         End If
  38.                         End Select
  39.                 Select Case Message
  40.                     Case Is <> "Osteoporosis"
  41.                         If Not IsNull(HIP) Then
  42.                             Select Case HIP
  43.                                 Case Is < -2.5
  44.                                     Message = "Osteoporosis"
  45.                                 Case -2.5 To -1
  46.                                     Message = "Osteopenia"
  47.                                 Case Is > -1
  48.                                     Message = "Normal"
  49.                                 Case Is = ""
  50.                                     Message = "Not Evaluated"
  51.                             End Select
  52.                         End If
  53.                 End Select
  54.                   Select Case Message
  55.                     Case Is <> "Osteoporosis"
  56.                         If Not IsNull(FEM) Then
  57.                             Select Case FEM
  58.                                 Case Is < -2.5
  59.                                     Message = "Osteoporosis"
  60.                                 Case -2.5 To -1
  61.                                     Message = "Osteopenia"
  62.                                 Case Is > -1
  63.                                     Message = "Normal"
  64.                                 Case Is = ""
  65.                                     Message = "Not Evaluated"
  66.                             End Select
  67.                         End If
  68.                 End Select
  69.                     OsteoResults = Message
  70.         End Function
Try this.

Regards,
Scott
Sep 20 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.