IIF not working?

[Vita]

I have typed this IIF in a few different ways and none of which seem to trip. An if statement detects it though.



rs!TimeCreated = "" in this example
CrTimeLookup is a string

CrTimeLookup = IIf(Not (rs!TimeCreated = "") Or Not (IsNull(rs!TimeCreated)), rs!TimeCreated, "N/A")
'CrTimeLookup stays a blank string or gets set to rs!TimeCreated. I can't tell which it is doing.

CrTimeLookup = IIf(Not rs!TimeCreated = "" Or Not IsNull(rs!TimeCreated), rs!TimeCreated, "N/A")
'same result

CrTimeLookup = IIf((Not rs!TimeCreated = "") Or (Not IsNull(rs!TimeCreated)), rs!TimeCreated, "N/A")
'same result

Am I doing something wrong? I read that you can use an IIF anywhere you can use an expression.

[ranman256]

simplify some of the clunky functions:


Isnull2() will check for both null & ""

Public Function IsNull2(ByVal pvVal) As Boolean
On Error Resume Next
IsNull2 = IsNull(pvVal) Or pvVal = "" Or IsEmpty(pvVal)
'If IsEmpty(IsNull2) Then IsNull2 = True
End Function


Public Function Nzz(ByVal pvAmt)
On Error Resume Next
Select Case True
  Case IsNull(pvAmt)
    Nzz = Val(Nz(pvAmt, 0))
  Case pvAmt = ""
     Nzz = 0
  Case Else
     Nzz = pvAmt
End Select
End Function

[CJ_London]

Time is usually stored as a number, not text. Without a date element it will have a value of < 1 since it represents the number of seconds divided by 86400, the number of seconds in a day. Suggest show some example values of timecreated.

[Micron]

As an aside to prior comments, it is often better to assign the return value of an expression to a variable, then test the variable. I mean along the lines of
Dim varMyVariable As Variant

varMyVariable = rs!TimeCreated

Then use IIF or IF on varMyVariable. Then you can validate the variable value after it is assigned and deal with "" or Null or other values or data types, which may reveal things about the values that you didn't expect.

[xps35]

Use AND instead of OR.

[Minty]

Another option depending on the type of data you have, (which we are all guessing at!)

Iif(IsDate(rs.YourTime),rs.YourTime,"N/A")

By using NOT you need to switch to an AND as Peter point out.

[Vita]

Use AND instead of OR.

Ohhhh Duh since it is a ZLS the [Not IsNull(rs!TimeCreated)] Is true and so it is assigning the ZLS to CrTimeLookup!

simplify some of the clunky functions:


Isnull2() will check for both null & ""

Public Function IsNull2(ByVal pvVal) As Boolean
On Error Resume Next
IsNull2 = IsNull(pvVal) Or pvVal = "" Or IsEmpty(pvVal)
'If IsEmpty(IsNull2) Then IsNull2 = True
End Function


Public Function Nzz(ByVal pvAmt)
On Error Resume Next
Select Case True
  Case IsNull(pvAmt)
    Nzz = Val(Nz(pvAmt, 0))
  Case pvAmt = ""
     Nzz = 0
  Case Else
     Nzz = pvAmt
End Select
End Function

Oh this is smart! I am only doing it for one or two lines though. Is it normal/worth it to include a whole function for only one or two lines? The extra readability doesn't seem worth it?

Time is usually stored as a number, not text. Without a date element it will have a value of < 1 since it represents the number of seconds divided by 86400, the number of seconds in a day. Suggest show some example values of timecreated.

It actually is a number! The people who made the database made most fields strings though so unfortunately I have to work with that. It may also have something to do with the weird time format it is stored in. I made a separate post about it a while ago.

As an aside to prior comments, it is often better to assign the return value of an expression to a variable, then test the variable. I mean along the lines of
Dim varMyVariable As Variant

varMyVariable = rs!TimeCreated

Then use IIF or IF on varMyVariable. Then you can validate the variable value after it is assigned and deal with "" or Null or other values or data types, which may reveal things about the values that you didn't expect.

I will keep this in mind. Instead of this I had written a simple if statement to verify the same thing.

    If rs!TimeCreated = "" Then
    Debug.Print True
    End If

Another option depending on the type of data you have, (which we are all guessing at!)

Iif(IsDate(rs.YourTime),rs.YourTime,"N/A")

By using NOT you need to switch to an AND as Peter point out.

Yea the AND was the solution haha. I thought the data type was implied because I said it was a ZLS but I will be sure to clarify that in the future! Apologies!
It was a string (That should be a long or int) but I was just trying to essentially write an NZ() but include detection for ZLS.
The people who made the SQL server I'm pulling from have the date in a separate field.

(Side Tangent)
I wish they were the same because then I would save on some error checking when checking the date.
I currently have a statement specifically to check if the create date and update date are the same and the times are not then it sets a variable to true so that it will still display the times. (its a longer story but sometimes the update time gets set along with the create time in a separate program)