Conditional calculations in the detail line

[stupesek]

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.

[HiTechCoach]

Will you please post the actual VBA code used in the function.

Should this:

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

really be:

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

[stupesek]

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.

[HiTechCoach]

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.

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:

=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

 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

[stupesek]

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.

[HiTechCoach]

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.

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

[stupesek]

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.

[HiTechCoach]

Then changed the text box source to =fInterpertation

Did you paste the actual control source from the control?

=fInterpertation

is missing the parameter.

Try:

=fInterpertation([AnalysisName])

What is the data type for AnalysisName?

[stupesek]

[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.

[stupesek]

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.

[HiTechCoach]

Glad to hear it worked for you!

Thanks for posting your solution.