VBA code to account for greater/less than>,< signs for my Report.

[jplink49]

Hello,

I have code that is doing what I want it to do on my Report except when it comes across the occasional >,< signs.{For "Ammonia", when there is a number less than 1, put "BDL", for all other digits round to one decimal place(that's fine!)}. {For "CBOD", when there is a number less than 1, put "BDL", for all other digits round to a whole number (that's fine except when I have to include a > or < sign with the digit)} Then, I get this error: Run-time error '13': Type mismatch. Debugging shows it is getting hung-up at the Rounding part of the code. Again,(Me.ACOM) is a field the holds results for chemical test so it will be an integer, except when we have to include the <, > sign.
My question is this:
What can I add to my code that would allow it to tolerate the >,< the signs without giving me the error message? Here is my code:

Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer)
FIND_ACOM
End Sub

Private Sub FIND_ACOM()
If CStr(Me.ACOM) <> "PENDING" Then

Select Case Me.ANAL

Case "Ammonia (NH3-N)"
If Me.ACOM < 0.2 Then
Me.Text67 = "BDL"
Else
Me.Text67 = Format(Round(Me.ACOM, 1), "0.0")
End If
Case "CBOD"
If Me.ACOM < 1# Then
Me.Text67 = "BDL"
Else
Me.Text67 = Format(Round(Me.ACOM, 0), "0")
End Select
Else
Me.ACOM1 = Me.ACOM
End If
End Sub

 

[WayneRyan]

JP,

You can use the Replace function:

Me.Text67 = Format(Round(Replace(Replace(Me.ACOM, "<", ""), ">", ""), 1), "0.0")

If you want to retain the <> signs it will be a bit more complicated.

Wayne

 

[ecawilkinson]

Are you saying the most of the time me.ACOM is an integer but sometimes it is something like "<1" or ">5"? If so, what do you Access to do with it in that case.? Ignore them? Strip the sign out?

 

[chergh]

try something like:

if cbool(instr(1,me.ACOM,"<")) = TRUE then
   strVariable = right(me.acom, len(me.ACOM) - 1)
   Me.Text67 = "<" & Format(Round(clong(strVariable), 1), "0.0")
end if

This code tests to see if the < character is in your string if it is it then removes it using the right function and assigns the remaining characters to a variable. The removed character is then reinserted into your text box and the format and round functions are performed on the strVariable value which is converted to a numeric datatype using clong.

 

[jplink49]

Yes. Sometimes there are values such as "<1" or ">5". And when this is the case, I want Access to keep the sign. Display "<1" or ">5".

 

[jplink49]

Wayne, thanks for your reply. I see your code, but I need for it to retain the <> signs if they are there. Meaning to display "< 1" or "> 5".

 

[jplink49]

Chergh, this is what I want it to do. I'm about to try your code now.

 

[jplink49]

Chergh,

I've tried your code. I get this error message: Compile error: Sub or function not defined, and it's getting hung up on the "clong" part of the code{---Format(Round(clong(strvariable), 1), "0.0")---}. Do I need to go back and define a function in the beginnig of my code. I am a novice at coding.

 

[chergh]

I made a mistake with the function name it isn't "clong" it's "clng"

 

[jplink49]

Chergh

That Did It! Thanks A Lot!

 

[jplink49]

Hey Chergh... How can I include the > greater than sign in that code. (Your code works perfectly when handling the < less than sign; however sometimes we have results like
"> 5" etc.. How can I modify the code to handle both signs in the code?

 

[WayneRyan]

JP,

if cbool(instr(1,me.ACOM,"<")) = TRUE Or cbool(instr(1,me.ACOM,">")) = TRUE then

Wayne

 

[chergh]

You can't really use a simple "or" clause here as you are wanting to replace the same character as is removed so your probably going to have to use "elseif".

if cbool(instr(1,me.ACOM,"<")) = TRUE then
   strVariable = right(me.acom, len(me.ACOM) - 1)
   Me.Text67 = "<" & Format(Round(clong(strVariable), 1), "0.0")
elseif cbool(instr(1,me.ACOM,">")) = TRUE then
   strVariable = right(me.acom, len(me.ACOM) - 1)
   Me.Text67 = ">" & Format(Round(clong(strVariable), 1), "0.0")
end if

 

[jplink49]

Ok.. I understand. Thanks again Chergh.

 

[jplink49]

Hey Wayne,

Thanks for your help. I have noted your code.