Message When Field Is Null

[gracm25]

Good morning,

I'm currently running the below codes to do several calculations in a report. Everything works great. One more option that I'd like to add in is when the REPAIR_TYPE field is null, I'd like for it to return a string that says "APPLY SRT'S!!". I'd also like that string to be BOLD and have the same lngGreen coloring that I've added in one of the codes. I've tried to add it in several places, but I keep getting compile errors and I'm not sure where and how to place it. Any suggestions would be appreciated!!
____________________________________________________________
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim dblInterval As Double
Dim lngGreen As Long
Dim str As String

dblInterval = CDbl(dateTimeEnd - dateTimeStart)

lngGreen = RGB(0, 128, 64)

Me!ElapsedTimeString.ForeColor = vbBlack
Me!ElapsedTimeString.FontBold = False
Me!ElapsedTimeString.FontUnderline = False
Me!TimeRemaining.ForeColor = vbBlack
Me!TimeRemaining.FontBold = False
Me!TimeRemaining.FontUnderline = False

If REPAIR_TYPE = "MINOR" Then
If dblInterval < 1 Then
Me!ElapsedTimeString.ForeColor = vbRed
Me!ElapsedTimeString.FontBold = True
Me!ElapsedTimeString.FontUnderline = True
Me!TimeRemaining.ForeColor = vbRed
Me!TimeRemaining.FontBold = True
Me!TimeRemaining.FontUnderline = True
End If
ElseIf REPAIR_TYPE = "MAJOR" Then
If dblInterval < 1 Then
Me!ElapsedTimeString.ForeColor = vbRed
Me!ElapsedTimeString.FontBold = True
Me!ElapsedTimeString.FontUnderline = True
Me!TimeRemaining.ForeColor = vbRed
Me!TimeRemaining.FontBold = True
Me!TimeRemaining.FontUnderline = True
ElseIf dblInterval >= 1 And dblInterval < 3 Then
Me!ElapsedTimeString.ForeColor = vbBlue
Me!ElapsedTimeString.FontBold = True
Me!ElapsedTimeString.FontUnderline = True
Me!TimeRemaining.ForeColor = vbBlue
Me!TimeRemaining.FontBold = True
Me!TimeRemaining.FontUnderline = True
Else
' Add something here for when "MAJOR", dblInterval >= 3
Me!ElapsedTimeString.ForeColor = vbBlack
Me!ElapsedTimeString.FontBold = False
Me!ElapsedTimeString.FontUnderline = False
Me!TimeRemaining.ForeColor = vbBlack
Me!TimeRemaining.FontBold = False
Me!TimeRemaining.FontUnderline = False
End If
Else
Me!ElapsedTimeString.ForeColor = lngGreen
Me!ElapsedTimeString.FontBold = True
Me!ElapsedTimeString.FontUnderline = True
Me!TimeRemaining.ForeColor = lngGreen
Me!TimeRemaining.FontBold = True
Me!TimeRemaining.FontUnderline = True
End If

End Sub
____________________________________________________________

Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
'************************************************* ********************
' Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
' Returns the time elapsed between a starting Date/Time and an ending
' Date/Time formatted as a string that looks like this:
' "10 days, 20 hours, 30 minutes".
'************************************************* ********************
Dim interval As Double, str As String, days As Variant
Dim hours As String, minutes As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

days = Fix(CSng(interval))
hours = Format(interval, "h")
minutes = Format(interval, "n")

' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & minutes <> "00", ", ", " "))
' Hours part of the string
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(minutes <> "0", ", ", " "))
' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", minutes & " Minutes"))
ElapsedTimeString = IIf(str = "", "0", str)

End Function
____________________________________________________________
Public Function TimeDays(REPAIR_TYPE As String) As Integer
'************************************************* ********************
' Function TimeDays returns a "1" for MINOR repairs, a "3" for MAJOR
' repairs and a "0" for repairs without a type code assigned.
'************************************************* ********************
Dim itg As Integer

Select Case REPAIR_TYPE
Case "MINOR"
itg = 1
Case "MAJOR"
itg = 3
Case Else
itg = 0
End Select

TimeDays = itg
End Function
______________________________________________________________
Public Function TimeRemaining(TimeDays As Integer, dateTimeStart As Date, dateTimeEnd As Date) As String
'************************************************* ********************
' Function TimeRemaining(TimeDays As Integer, dateTimeStart As Date, dateTimeEnd As Date) As String
' Returns the time elapsed between a fixed number and starting Date/Time and an ending
' Date/Time formatted as a string that looks like this:
' "10 days, 20 hours, 30 minutes".
'************************************************* ********************
Dim interval As Double, str As String, days As Variant
Dim hours As String, minutes As String

If IsNull(TimeDays) = True Or _
IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = (TimeDays - (dateTimeEnd - dateTimeStart))

days = Fix(CSng(interval))
hours = Format(interval, "h")
minutes = Format(interval, "n")

' Days part of the string
If days < 0 Then
str = TimeRemaining
TimeRemaining = "EXPIRED!!"
Exit Function
End If
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & minutes <> "00", ", ", " "))
' Hours part of the string
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(minutes <> "0", ", ", " "))
' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", minutes & " Minutes"))
TimeRemaining = IIf(str = "", "0", str)

End Function

 

[Guus2005]

I see a lot of unformatted code. The lack of the WITH statement end the occurence of IIF statements makes your code very slow and unreadable.

Where do you get the compile message?

Use code tags to beautify your code and make it more readable.

[edit]To respond to you question:

If len(nz(fieldname,"")) = 0 then
   msgbox "Fieldname is Null!", vbExclamation
else
   msgbox "Fieldname is not null", vbInformation
endif

[/edit]