IsNull anomaly

[madeleine]

Please can someone help me, I think I may go mad with this one:

Do While Not IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))
If IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))
Then
MsgBox "this is not actually possible"
End If

-
-
-
For some reason I end up in this loop and get the message "this is not
actually possible" popping up....without this check I end up in an
endless loop.

What I'm trying to do is suck in a load of data from excel and then, if
the date is not null, add the row to a table in the database, what I
can't work out is how to just check whether the date is not null, as
you can see above something is going wonderfully wrong.

I've tried feeding it into a local variable but still hit the same
problem.

Thanks

 

[Klatuu]

Most of what you have is unnecessary. You really don't need all that
formatting. It is only slowing down the process. You certainly don't need
both the CDate and FormateDateTime functions. CDate and FormatDateTime using
vbShortDate return exactly the same thing. Wait and do your formatting when
you are actually going to use the value.

Since F1 as a field name gives nobody a clue as to the nature of the data or
a guess at what the data type might be, I can only guess it may be a DateTime
field.

So, data types not withstanding, change your code to:

Do While Not IsNull(rst![F1])

Leave out the check. If this does not work, I would have a close look at
the values in the field for that table and see what they really are.

 

[madeleine]

Thanks for that I tried the rst.F1 way and the loop still evaluates it
to not be null, even when it is.

I ran the code below and checked the value of the field at the msgbox
point, it had the value down as null.

What I can't understand is that the loop evaluates IsNull to be false,
but the if statement directly afterwards evaluates it to be true!!! How
can the same variable, formatted in the same way be evaluated as both
IsNull false and IsNull true?

Do While Not IsNull(CDate(FormatDateTime(rst!F1, vbShortDate)))
If IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))
Then
MsgBox "this is not actually possible"

End If

 

[Klatuu]

That doesn't seem right. Without being able to see the data, I don't know
what else I can do for you. remember, that the function you are using are
meaningless. In fact, when I test the logic in the immediate window:
x = Null
?cdate(formatdatetime(x,vbshortdate))
I get an error 13 (type mismatch), which would seem reasonable.

Thanks for that I tried the rst.F1 way and the loop still evaluates it
to not be null, even when it is.

I ran the code below and checked the value of the field at the msgbox
point, it had the value down as null.

What I can't understand is that the loop evaluates IsNull to be false,
but the if statement directly afterwards evaluates it to be true!!! How
can the same variable, formatted in the same way be evaluated as both
IsNull false and IsNull true?

Do While Not IsNull(CDate(FormatDateTime(rst!F1, vbShortDate)))
If IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))
Then
MsgBox "this is not actually possible"

End If

Most of what you have is unnecessary. You really don't need all that
formatting. It is only slowing down the process. You certainly don't need
both the CDate and FormateDateTime functions. CDate and FormatDateTime using
vbShortDate return exactly the same thing. Wait and do your formatting when
you are actually going to use the value.

Since F1 as a field name gives nobody a clue as to the nature of the data or
a guess at what the data type might be, I can only guess it may be a DateTime
field.

So, data types not withstanding, change your code to:

Do While Not IsNull(rst![F1])

Leave out the check. If this does not work, I would have a close look at
the values in the field for that table and see what they really are.

 

[Michel Walsh]

Hi,


that happens if you have On Error Resume Next

===========
Public Sub Weird()
On Error Resume Next

Dim x As Variant
x = Null
Do While Not IsNull(CDate(Format(x, vbShortDate)))
If IsNull(CDate(Format(x, vbShortDate))) Then
MsgBox "hi"
End If

Debug.Assert False
Loop

End Sub
===============


The DO instruction fails, since CDate cannot handle the Null, so the
execution continues on the next expression, and again, the if fails.


Hoping it may help,
Vanderghast, Access MVP

Thanks for that I tried the rst.F1 way and the loop still evaluates it
to not be null, even when it is.

I ran the code below and checked the value of the field at the msgbox
point, it had the value down as null.

What I can't understand is that the loop evaluates IsNull to be false,
but the if statement directly afterwards evaluates it to be true!!! How
can the same variable, formatted in the same way be evaluated as both
IsNull false and IsNull true?

Do While Not IsNull(CDate(FormatDateTime(rst!F1, vbShortDate)))
If IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))
Then
MsgBox "this is not actually possible"

End If

Most of what you have is unnecessary. You really don't need all that
formatting. It is only slowing down the process. You certainly don't
need
both the CDate and FormateDateTime functions. CDate and FormatDateTime
using
vbShortDate return exactly the same thing. Wait and do your formatting
when
you are actually going to use the value.

Since F1 as a field name gives nobody a clue as to the nature of the data
or
a guess at what the data type might be, I can only guess it may be a
DateTime
field.

So, data types not withstanding, change your code to:

Do While Not IsNull(rst![F1])

Leave out the check. If this does not work, I would have a close look at
the values in the field for that table and see what they really are.

 

[madeleine]

I moved the While to after the loop rather than after the Do and it all
worked fine, I can only assume that the Do part of the statement is not
executed each time you iterated through the loop, but the loop part is.

Thanks very much for you help and interest, its good to know there are
helpful people out there.

Hi,


that happens if you have On Error Resume Next

===========
Public Sub Weird()
On Error Resume Next

Dim x As Variant
x = Null
Do While Not IsNull(CDate(Format(x, vbShortDate)))
If IsNull(CDate(Format(x, vbShortDate))) Then
MsgBox "hi"
End If

Debug.Assert False
Loop

End Sub
===============


The DO instruction fails, since CDate cannot handle the Null, so the
execution continues on the next expression, and again, the if fails.


Hoping it may help,
Vanderghast, Access MVP

Thanks for that I tried the rst.F1 way and the loop still evaluates it
to not be null, even when it is.

I ran the code below and checked the value of the field at the msgbox
point, it had the value down as null.

What I can't understand is that the loop evaluates IsNull to be false,
but the if statement directly afterwards evaluates it to be true!!! How
can the same variable, formatted in the same way be evaluated as both
IsNull false and IsNull true?

Do While Not IsNull(CDate(FormatDateTime(rst!F1, vbShortDate)))
If IsNull(CDate(FormatDateTime(rst!F1.Value, vbShortDate)))
Then
MsgBox "this is not actually possible"

End If

Most of what you have is unnecessary. You really don't need all that
formatting. It is only slowing down the process. You certainly don't
need
both the CDate and FormateDateTime functions. CDate and FormatDateTime
using
vbShortDate return exactly the same thing. Wait and do your formatting
when
you are actually going to use the value.

Since F1 as a field name gives nobody a clue as to the nature of the data
or
a guess at what the data type might be, I can only guess it may be a
DateTime
field.

So, data types not withstanding, change your code to:

Do While Not IsNull(rst![F1])

Leave out the check. If this does not work, I would have a close look at
the values in the field for that table and see what they really are.