Invalid use of Null

[mbentler]

I am trying to use a value in a Date/Time field in my code:
Public Function GetDays() As Integer

'Determine the number of days on an insurance policy

Dim dteStart As Date
Dim dteEnd As Date
Dim VarDays As Variant
Dim Insurance As Date


Dim varX As Variant

'Find value in the Insurance field

varX = DLookup("[CompanyName]", "tblCompanyList", "[Insurance] = "
& Forms!frmCompanyList!Insurance)
dteStart = Now()
dteEnd = varX
VarDays = DateDiff("d", dteStart, dteEnd)
Debug.Print VarDays

' Tell user the status of the companies insurance policy

If (VarDays >= 10) Then
MsgBox "Insurance Valid", vbOKOnly + vbInformation, "Insurance
Status"
ElseIf (VarDays < 10) And (VarDays > 2) Then
MsgBox "Insurance About to Expiry", vbOKOnly + vbExclamation,
"Insurance Status"
ElseIf (VarDays < 2) Then
MsgBox "Insurance Expired", vbOKOnly + vbCritical, "Insurance
Status"
End If

End Function

When running I get an "Invalid use of Null" even when there is a value
in the Insurance field.

Any suggestions

 

[Arvin Meyer [MVP]]

Set a break-point at the line:

varX = DLookup("[CompanyName]", ....'etc

and step through the code (F8). On which line does it break?

 

[Dirk Goldgar]

varX = DLookup("[CompanyName]", "tblCompanyList", "[Insurance] = "
& Forms!frmCompanyList!Insurance)
dteStart = Now()
dteEnd = varX

I'm curious. That DLookup expression is going to retrieve the value of a
field called "CompanyName", which you later assign to a Date variable.
"CompanyName" seems an odd name for a date/time field. Is it really a
date/time field, or are you pulling the wrong field?

 

[Arvin Meyer [MVP]]

varX = DLookup("[CompanyName]", "tblCompanyList", "[Insurance] =
" & Forms!frmCompanyList!Insurance)
dteStart = Now()
dteEnd = varX

I'm curious. That DLookup expression is going to retrieve the value
of a field called "CompanyName", which you later assign to a Date
variable. "CompanyName" seems an odd name for a date/time field. Is
it really a date/time field, or are you pulling the wrong field?

I also noticed that you first assign a Variant the value from DLookup(),
presumably so it can deal with a possible Null value, and then you assign
the value of the Variant to a Date variable which will not be able to
handle a Null. This would make sense if you tested the Variant for Null
first, but the code doesn't do so. That would certainly explain an
"invalid use of null" error.

Good catch. It will be interesting to see if when he steps through his code,
if that is the answer. I'd bet it is.

 

[mbentler]

Set a break-point at the line:

varX = DLookup("[CompanyName]", ....'etc

and step through the code (F8). On which line does it break?
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

I am trying to use a value in a Date/Time field in my code:
Public FunctionGetDays() As Integer

'Determine the number of days on an insurance policy

Dim dteStart As Date
Dim dteEnd As Date
Dim VarDays As Variant
Dim Insurance As Date

Dim varX As Variant

'Find value in the Insurance field

varX = DLookup("[CompanyName]", "tblCompanyList", "[Insurance] = "
& Forms!frmCompanyList!Insurance)
dteStart = Now()
dteEnd = varX
VarDays = DateDiff("d", dteStart, dteEnd)
Debug.Print VarDays

' Tell user the status of the companies insurance policy

If (VarDays >= 10) Then
MsgBox "Insurance Valid", vbOKOnly + vbInformation, "Insurance
Status"
ElseIf (VarDays < 10) And (VarDays > 2) Then
MsgBox "Insurance About to Expiry", vbOKOnly + vbExclamation,
"Insurance Status"
ElseIf (VarDays < 2) Then
MsgBox "Insurance Expired", vbOKOnly + vbCritical, "Insurance
Status"
End If

End Function

When running I get an "Invalid use of Null" even when there is a value
in the Insurance field.

Any suggestions

Arvin the code stops at the varX = DLookup line
I have tried defining varX as Date, but that didn't work either.
The DLookup is to find the field "Insurance" which is a date field and
apply it to my code so that I can keep track of the expiry dates of
Insurance certificates for the CompanyNames in my table tblCompanyList.

 

[mbentler]

varX = DLookup("[CompanyName]", "tblCompanyList", "[Insurance] = "
& Forms!frmCompanyList!Insurance)
dteStart = Now()
dteEnd = varX

I'm curious. That DLookup expression is going to retrieve the value of a
field called "CompanyName", which you later assign to a Date variable.
"CompanyName" seems an odd name for a date/time field. Is it really a
date/time field, or are you pulling the wrong field?

I am probably pulling the wrong field, but not sure how to get the
right one which is "Insurance", which is a date field.

 

[mbentler]

varX = DLookup("[CompanyName]", "tblCompanyList", "[Insurance] =
" & Forms!frmCompanyList!Insurance)
dteStart = Now()
dteEnd = varX

I'm curious. That DLookup expression is going to retrieve the value
of a field called "CompanyName", which you later assign to a Date
variable. "CompanyName" seems an odd name for a date/time field. Is
it really a date/time field, or are you pulling the wrong field?

I also noticed that you first assign a Variant the value from DLookup(),
presumably so it can deal with a possible Null value, and then you assign the
value of the Variant to a Date variable which will not be able to handle a Null.
This would make sense if you tested the Variant for Null first, but the code
doesn't do so. That would certainly explain an "invalid use of null" error.

But there are going to be Nulls in this field, as some of the
CompanyNames are "Not on File" which would have the effect of an empty
or Null ? Testing for Null would do what? I am hoping to get a Null to
produce a message that the Company is "Not on File"....perhaps you can
tell, I am new at this.

 

[mbentler]

varX = DLookup("[CompanyName]", "tblCompanyList", "[Insurance] =
" & Forms!frmCompanyList!Insurance)
dteStart = Now()
dteEnd = varX
I'm curious. That DLookup expression is going to retrieve the value
of a field called "CompanyName", which you later assign to a Date
variable. "CompanyName" seems an odd name for a date/time field. Is
it really a date/time field, or are you pulling the wrong field?

I also noticed that you first assign a Variant the value from DLookup(),
presumably so it can deal with a possible Null value, and then you assign
the value of the Variant to a Date variable which will not be able to
handle a Null. This would make sense if you tested the Variant for Null
first, but the code doesn't do so. That would certainly explain an
"invalid use of null" error.

Good catch. It will be interesting to see if when he steps through his code,
if that is the answer. I'd bet it is.

When I change the definitions to variants, I get a Null even when the
Insurance (date field) isn't empty.

 

[Rick Brandt]

varX = DLookup("[CompanyName]", "tblCompanyList", "[Insurance]

Arvin the code stops at the varX = DLookup line
I have tried defining varX as Date, but that didn't work either.
The DLookup is to find the field "Insurance" which is a date field and
apply it to my code so that I can keep track of the expiry dates of
Insurance certificates for the CompanyNames in my table
tblCompanyList.

DLookup returns the field named in the first argument. You want "Insurance" but
you are asking for "CompanyName". CompanyName would not be appropriate to
assign to a Date variable whether you get a Null or a Non-Null.

 

[mbentler]

varX = DLookup("[CompanyName]", "tblCompanyList", "[Insurance]

Arvin the code stops at the varX = DLookup line
I have tried defining varX as Date, but that didn't work either.
The DLookup is to find the field "Insurance" which is a date field and
apply it to my code so that I can keep track of the expiry dates of
Insurance certificates for the CompanyNames in my table
tblCompanyList.

DLookup returns the field named in the first argument. You want "Insurance" but
you are asking for "CompanyName". CompanyName would not be appropriate to
assign to a Date variable whether you get a Null or a Non-Null.

Ok, I have changed the line to:

varX = DLookup ("[Insurance]", "tblCompanyList", "[Insurance] = " &
Forms!frmCompanyList!Insurance

which still returns a null, even though there is a value in the field
of the table and the form.

I have also added:

If IsNull (VarDays) Then
MsgBox "Not On File", vbOKOnly.

And I do get the message box coming up....but there is a value in the
field.

Do I have to redefine my variables in some other way?

 

[Dirk Goldgar]

varX = DLookup("[CompanyName]", "tblCompanyList", "[Insurance] = "
& Forms!frmCompanyList!Insurance)
dteStart = Now()
dteEnd = varX

I'm curious. That DLookup expression is going to retrieve the value of a
field called "CompanyName", which you later assign to a Date variable.
"CompanyName" seems an odd name for a date/time field. Is it really a
date/time field, or are you pulling the wrong field?

I am probably pulling the wrong field, but not sure how to get the
right one which is "Insurance", which is a date field.

I suspect from what you've posted here and in other messages in this thread
that you want something like this:

'----- start of code snippet -----
varX = DLookup("Insurance", "tblCompanyList", _
"CompanyName = " & _
Chr(34) & Forms!frmCompanyList!CompanyName & Chr(34))

If IsNull(varX) Then
MsgBox "This company is not on file."
Else
dteStart = Now()
dteEnd = varX
VarDays = DateDiff("d", dteStart, dteEnd)

' ... rest of code validating insurance coverage ...

End If
'----- end of code snippet -----

Note that I'm assuming that the form "frmCompanyList" has a field named
"CompanyName", and that what you are trying to do is look up the Insurance
field -- a date/time field -- for the company whose name is displayed in
that field.

 

[mbentler]

varX = DLookup("[CompanyName]", "tblCompanyList", "[Insurance] = "
& Forms!frmCompanyList!Insurance)
dteStart = Now()
dteEnd = varX
I'm curious. That DLookup expression is going to retrieve the value of a
field called "CompanyName", which you later assign to a Date variable.
"CompanyName" seems an odd name for a date/time field. Is it really a
date/time field, or are you pulling the wrong field?

I am probably pulling the wrong field, but not sure how to get the
right one which is "Insurance", which is a date field.

I suspect from what you've posted here and in other messages in this thread
that you want something like this:

'----- start of code snippet -----
varX = DLookup("Insurance", "tblCompanyList", _
"CompanyName = " & _
Chr(34) & Forms!frmCompanyList!CompanyName & Chr(34))

If IsNull(varX) Then
MsgBox "This company is not on file."
Else
dteStart = Now()
dteEnd = varX
VarDays = DateDiff("d", dteStart, dteEnd)

' ... rest of code validating insurance coverage ...

End If
'----- end of code snippet -----

Note that I'm assuming that the form "frmCompanyList" has a field named
"CompanyName", and that what you are trying to do is look up the Insurance
field -- a date/time field -- for the company whose name is displayed in
that field.

Exactly, exactly, exactly.......Thank you so much, not sure what the
Chr (34) is, but this works perfectly....thank you so much, lost a lot
of sleep trying to figure this out....

 

[Arvin Meyer [MVP]]

Take the square brackets off of the field name Insurance. By enclosing it in
quotes you are asking for a fieldname that contains the square brackets.

Square brackets are necessary when using a field name with a space, but
unnecessary when you put the name in quotes.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

varX = DLookup("[CompanyName]", "tblCompanyList", "[Insurance]
Arvin the code stops at the varX = DLookup line
I have tried defining varX as Date, but that didn't work either.
The DLookup is to find the field "Insurance" which is a date field and
apply it to my code so that I can keep track of the expiry dates of
Insurance certificates for the CompanyNames in my table
tblCompanyList.

DLookup returns the field named in the first argument. You want
"Insurance" but
you are asking for "CompanyName". CompanyName would not be appropriate
to
assign to a Date variable whether you get a Null or a Non-Null.

Ok, I have changed the line to:

varX = DLookup ("[Insurance]", "tblCompanyList", "[Insurance] = " &
Forms!frmCompanyList!Insurance

which still returns a null, even though there is a value in the field
of the table and the form.

I have also added:

If IsNull (VarDays) Then
MsgBox "Not On File", vbOKOnly.

And I do get the message box coming up....but there is a value in the
field.

Do I have to redefine my variables in some other way?

 

[Dirk Goldgar]

Exactly, exactly, exactly.......Thank you so much, not sure what the
Chr (34) is, but this works perfectly....

Great! I'm glad I guessed right. The Chr(34) expressions are there to wrap
company name in quotes -- the double-quote character " , which is ASCII 34.
Since the company name is a text field, the value that you embed in the
criteria argument of the DLookup function must be represented as a string
literal, and that means it must be enclosed in quotes.

Without some sort of quoting, trying to lookup John Smith Company would
resolve to this being passed as the criteria argument:

CompanyName = John Smith Company

But what is needed is this:

CompanyName = "John Smith Company"

or this:

CompanyName = 'John Smith Company'

I elected to use the Chr(34) company to add the double-quotes instead of
single quotes, to avoid problems that might arise if you were looking for,
say, John Smith's Company (note the apostrophe/single-quote that is part of
the company name). With a company name like that, using single-quotes to
quote the company name won't work, because you'd end up with

CompanyName = 'John Smith's Company'

and that would not be parsed properly due to the apparent early end of the
quoted string.

Make sense?

 

[mbentler]

Great! I'm glad I guessed right. The Chr(34) expressions are there to wrap
company name in quotes -- the double-quote character " , which is ASCII 34.
Since the company name is a text field, the value that you embed in the
criteria argument of the DLookup function must be represented as a string
literal, and that means it must be enclosed in quotes.

Without some sort of quoting, trying to lookup John Smith Company would
resolve to this being passed as the criteria argument:

CompanyName = John Smith Company

But what is needed is this:

CompanyName = "John Smith Company"

or this:

CompanyName = 'John Smith Company'

I elected to use the Chr(34) company to add the double-quotes instead of
single quotes, to avoid problems that might arise if you were looking for,
say, John Smith's Company (note the apostrophe/single-quote that is part of
the company name). With a company name like that, using single-quotes to
quote the company name won't work, because you'd end up with

CompanyName = 'John Smith's Company'

and that would not be parsed properly due to the apparent early end of the
quoted string.

Make sense?

Yes it does. The code now works exactly as I wanted it to, even when I
have no information in the Insurance field.
Again thank you