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.