Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Trying to resolve an "invalid use of Null" in VBA

Tags:

vba

ms-access

Quick snippet first:

Dim GUID As String
Dim givenNames, familyName, preferredName, gender, comments, carer, medicareNumber, patientNumber As String
Dim dob As Variant
Dim deceased, resolved, consultNotes As Boolean
Dim age As Variant

givenNames = Null
familyName = Null
preferredName = Null
gender = Null
dob = Null
comments = Null
deceased = False
resolved = False
carer = Null
age = Null
consultNotes = False
patientNumber = Null ' This is where I get the error

Any idea why this last variable would be the one to trip up? I've assigned Null to a bunch of other strings without any errors.

like image 467
HorusKol Avatar asked Apr 12 '11 05:04

HorusKol


Video Answer


3 Answers

In VBA/VB6, strings cannot be set to Null; only Variants can be set to null. In addition, when you declare variables inline comma-separated like in the question, only the last one will be typed as string; all of the others are typed as variants. To declare them on one line as a type you have to include the type

Dim a As String, Dim b As String ...

That's why it makes sense to just declare them on a single line.

(Btw, it should be noted that deceased, resolved are also typed as variants for the same reason.)

like image 81
Thomas Avatar answered Sep 23 '22 05:09

Thomas


The reason that it succeeds for givenNames, etc. is that you have unwittingly defined them as Variant type. It fails for patientNumber, because you successfully defined that as a String, and strings do not accept Null values.

Within a Dim statement, the As <type> clause applies to each individual variable in the list, so by putting it only at the end of the list, you applied the explicit type only to the last-listed variable. The implicit type of Variant is applied to the others.

like image 37
Steve Jorgensen Avatar answered Sep 23 '22 05:09

Steve Jorgensen


When I ran into that problem without knowing about the implicit type of Variant, I was able to use the work around of defining an extra variable such as BogusVariable at the end of the Dim statement list.

like image 23
Darrell Avatar answered Sep 20 '22 05:09

Darrell