Results 1 to 11 of 11

Conditional calculations in the detail line

  1. #1
    stupesek is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    46

    Conditional calculations in the detail line

    A report that has these items in the detail line:

    LabNumber
    AnalysisName
    AnalysisResult
    Units
    Interpretation

    The Interpretation is a text box whose value is dependent on what on what the value of the AnalysisName and AnalysisResult are.

    i.e. If AnalysisName = Nitrate and AnalysisResult > 10 then Interpretation = "Unsafe! Treatment suggested."

    Else Go on to the next set of evaluations.

    I tried to do it as a function in a module named Interpretation() but that returned the same value for each line in the detail portion.

  2. #2
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma
    Posts
    397
    Will you please post the actual VBA code used in the function.

    Should this:

    Code:
    If AnalysisName = Nitrate and AnalysisResult > 10 then Interpretation = "Unsafe! Treatment suggested."
    really be:

    Code:
    If AnalysisName = "Nitrate" and AnalysisResult > 10 then Interpretation = "Unsafe! Treatment suggested."

  3. #3
    stupesek is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    46
    I perhaps was a little to simple in the original post. Here is the test code I am working with.

    Option Compare Database
    Option Explicit

    Function Interpertation() As String

    If [AnalysisName] = 66 Then
    Interpertation = "Works" 'this line just to test if I can put a phrase in the text box when analysis is nitrate
    Else 'Go to next evaluation

    End Function

    As is I get the Name? error when the code is part of the report's module code.

    AnalysisName is a interger field linking a look-up table with all the analysis parameters.

    I will have several such evaluations in the final code that will basically say "If AnalysisName = something and Result is > or< or = to something else then Interpretation = some sort of text.

    What I don't know is how to get the report module code to show up in the text box without the name? error. I can get text to show up when the code is in a module by itself and it becomes a function in the Expression Builder, but it doesn't evaluate properly. Everyone of my lines returns the same text despite a change in the Analysis Name.

  4. #4
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma
    Posts
    397
    Quote Originally Posted by stupesek View Post
    I perhaps was a little to simple in the original post. Here is the test code I am working with.

    Option Compare Database
    Option Explicit

    Function Interpertation() As String

    If [AnalysisName] = 66 Then
    Interpertation = "Works" 'this line just to test if I can put a phrase in the text box when analysis is nitrate
    Else 'Go to next evaluation

    End Function

    As is I get the Name? error when the code is part of the report's module code.

    AnalysisName is a interger field linking a look-up table with all the analysis parameters.

    I will have several such evaluations in the final code that will basically say "If AnalysisName = something and Result is > or< or = to something else then Interpretation = some sort of text.

    What I don't know is how to get the report module code to show up in the text box without the name? error. I can get text to show up when the code is in a module by itself and it becomes a function in the Expression Builder, but it doesn't evaluate properly. Everyone of my lines returns the same text despite a change in the Analysis Name.
    Where is [AnalysisName]?

    Do you have a control on the form withe the name [AnalysisName] or a field in the reccord source for the report?


    What is the Data Type for [AnalysisName] ?

    Your code is expecting [AnalysisName] to be a numeric data type. Is the correct?


    To use a function you should pass an argument and then return something.

    Code:
    Function fInterpertation(pstrAnalysisName as Long) As String
     
     If pstrAnalysisName  = 66 Then
            fInterpertation = "Works" 'this line just to test if I can put a phrase in the text box when analysis is nitrate
     Else 'Go to next evaluation
         
     End Function
    IOn the report use a text box that is NOT named AnalysisName. i would use txtAnalysisName

    then you can set the control source to be:

    Code:
    =fInterpertation([AnalysisName])
    TIP: Please use the code tags when posting code to make it readable.

    I would also urge you to be explicit with your references. Example use Me., etc.


    I would also suggest using a Select Case statement like this


    Code:
     Function fInterpertation(pstrAnalysisName as Long) As String
      
    Select Case pstrAnalysisName  
    
       Case  66 
               fInterpertation = "Works 66" 
      
       Case  67 
                fInterpertation = "Works 67" 
    
       Case Else
                fInterpertation  = "Unknown"
       End Select
    
          
      End Function

  5. #5
    stupesek is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    46
    Where does the code from the Case example reside? I put it in the Report code and get the name? error. Am I not referencing it properly in the source property?

    Secondly, is there a good reference book you could suggest, because I understand the basics of code writing, I don't know all the functions and reserved words in VBA.

  6. #6
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma
    Posts
    397
    Quote Originally Posted by stupesek View Post
    Where does the code from the Case example reside? I put it in the Report code and get the name? error. Am I not referencing it properly in the source property?
    Hard to see the issue without seeing your database or VBA code.

    Please post your VBA code.


    Quote Originally Posted by stupesek View Post
    Secondly, is there a good reference book you could suggest, because I understand the basics of code writing, I don't know all the functions and reserved words in VBA.
    I personally use MSDN and the Access help file.

    Also see: Books suggestions

  7. #7
    stupesek is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    46
    Function fInterpertation(pstrAnalysisName As Long) As String



    Select Case pstrAnalysisName

    Case 66
    fInterpertation = "Works 66"

    Case 14
    fInterpertation = "Works 14"

    Case Else
    fInterpertation = "Unknown"

    End Select


    End Function

    This code you posted looks exactly like what I am looking for, so I used it pretty much as you posted. Then changed the text box source to =fInterpertation

    Code is in the report code.

  8. #8
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma
    Posts
    397
    Quote Originally Posted by stupesek View Post
    Then changed the text box source to =fInterpertation
    Did you paste the actual control source from the control?

    Code:
    =fInterpertation
    is missing the parameter.

    Try:

    Code:
    =fInterpertation([AnalysisName])

    What is the data type for AnalysisName?

  9. #9
    stupesek is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    46
    [AnalysisName] is a long interger and is the join field to a look-up table named Parameter that holds all the pertinent info for that particular analysis.

  10. #10
    stupesek is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    46
    Function fInterpretations(pstrAnalysisName As Long) As String

    Select Case pstrAnalysisName

    Case 66
    fInterpretations = "Works 66"

    Case 14
    fInterpretations = "Works 14"

    Case Else
    fInterpretations = ""

    End Select


    End Function

    Finally!!!!! Above code worked!!

    Interpretation text box value =fInterpretations([AnalysisName])

    Put the code in a module I called "PVL Functions" not in the code for the report.

  11. #11
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma
    Posts
    397
    Glad to hear it worked for you!

    Thanks for posting your solution.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. subform calculations
    By genesis in forum Forms
    Replies: 0
    Last Post: 12-03-2009, 05:18 PM
  2. Multiple detail items per line
    By needafix in forum Reports
    Replies: 3
    Last Post: 10-22-2009, 10:04 AM
  3. Calculations in Access
    By dominick in forum Access
    Replies: 0
    Last Post: 07-28-2009, 06:39 AM
  4. Subform calculations
    By foureyes in forum Forms
    Replies: 4
    Last Post: 07-27-2009, 07:20 AM
  5. Time calculations
    By jimandann in forum Programming
    Replies: 2
    Last Post: 02-17-2009, 10:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums