syntax error with null values (1 Viewer)

[Josef P.]

By “VBA SQL construction” do you mean a string composition? Then the Variant variable is requested as a string and returns an empty string.
The same with Debug.Print: what you see is a string.
To be precise, the unassigned variant variable is empty. And Empty generates the behavior described above.

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/variant-data-type:

The value Empty denotes a Variant variable that hasn't been initialized (assigned an initial value). A Variant containing Empty is 0 if it is used in a numeric context, and a zero-length string ("") if it is used in a string context.

 

[cheekybuddha]

Joseph is correct, June, a Variant variable initialises to Empty - what you see is as a result of datatype coercion.

However, the point is that the function StringFormatSQL() handles NULL's passed as a parameter and correctly converts to the the word 'NULL' in the resulting output SQL string.

See here for the definition of the function

Pass an empty textbox into a Variant variable and it will get the value Null.

 

[June7]

And where did I disagree? Basically along same lines as I was saying - Variant defaults (initializes) to empty string but accepts all values and will implicitly convert (coercion) scalar.

My point is

CurrentDb.Execute "UPDATE table SET numberfield = " & x & " WHERE something"

fails if x is not set to a numeric value or NULL.

You assume textbox is not set to an empty string. A textbox with empty string does not give NULL to Variant. I tested.

 

[cheekybuddha]

A textbox with empty string does not pass NULL to Variant. I tested.

Was it bound?

 

[cheekybuddha]

Variant defaults (initializes) to empty string

This is not correct. It initialises to Empty (not the same as an empty string).

 

[June7]

UNBOUND textbox.

A subtlety I never focused on before. Gets more interesting.
UPDATE action set text field to Null although I had to change field in table to allow zero length. UPDATE using "", vbEmptyString, or unpopulated Variant all sent NULL to text field.
UPDATE of number field fails.

Now trying to figure out how could ever get ZLS into a field - import?

 

[CJ_London]

Now trying to figure out how could ever get ZLS into a field - import?

Not at my computer to check but think one way is to have it populated with some text, then select and hit the enter or left arrow key to delete it - on a form if the control is unbound or bound to a field that allows zls it leaves a zls behind

 

[gemma-the-husky]

So how does the computer actually represent "Empty", "Null", "0" or a zls.
That would explain what is going on.

 

[The_Doc_Man]

So how does the computer actually represent "Empty", "Null", "0" or a zls.
That would explain what is going on.

There is a concept called a "descriptor" that defines a string by defining a starting address of the string and a length. A ZLS is a string for which the length of that string is defined as zero.

For numeric fields, a value of 0 is trivial... it has a value for which no bits are set.

Technically, not all variables can be empty or null. But if you have a Variant data type, that variable's descriptor includes some definitional flags to include "Empty" and "Null" as (mutually exclusive) values. "Empty" is the special case of having never been loaded with a value. "Null" is the special case of having been set to keyword NULL.

The default value of a Variant is "Empty" whereas the default value of an object or specific class name is Nothing (which is a null address). However, no data type has other than Objects has a null default value. Strings as variables are defaulted to ZLS, not NULL.

When we get to fields in anything that HAS a field, the default value is almost always null (in the record) EXCEPT when there is a declared default data value in the FieldDef or if the Required flag is set. Or at least I found a reference suggesting that condition.