Solved How to convert "" to null in MS Access VBA

[nector]

The field below is a date release now sometimes the date field is empty , now in the Json dictionary it keeps return "" instead of null as result I'm always getting an error.

Company.Add "stockRlsDt", rs!stockreleasing.value

I want this field to return null if empty and actual date if not null

 

[arnelgp]

maybe:

Company.Add "stockRlsDt", IIF(IsNull(rs!stockreleasing), Null, rs!stockreleasing)

 

[cheekybuddha]

I would have thought:

Company.Add "stockRlsDt", IIF(Len(rs!stockreleasing & "") = 0, Null, rs!stockreleasing)

 

[arnelgp]

Company.Add "stockRlsDt", IIF(Len(rs!stockreleasing & "") = 0, Null, rs!stockreleasing)

it same as #2, since you cannot get the Length of a Null, so you add it to a NullString.

 

[cheekybuddha]

date field is empty , now in the Json dictionary it keeps return "" instead of null

Yes, but #2 does not add Null for empty string.

 

[Josef P.]

Company.Add "stockRlsDt", IIF(Len(rs!stockreleasing) = 0, Null, rs!stockreleasing)' <-- equal to #3
or (better readable):
Company.Add "stockRlsDt", NullIf(rs!stockreleasing, vbnullstring)
with:

Public Function NullIf(Value As Variant, expression As Variant) As Variant
   If Value = expression Then
      NullIf = Null
   Else
      NullIf = Value
   End If
End Function

 

[cheekybuddha]

Company.Add "stockRlsDt", IIF(Len(rs!stockreleasing) = 0, Null, rs!stockreleasing)' <-- equal to #3

Not quite - if rs!stockreleasing is actually Null then you will get an error.

Therefore safe to cover both possibilities like in #3.

NullIf() is nice addition too.

 

[Josef P.]

Not quite - if rs!stockreleasing is actually Null then you will get an error.

I'll bet against it
VBA.len(null) => null

 

[cheekybuddha]

Yes, you're right!

Null equates to False in the IIf()

 

[cheekybuddha]

I guess (without NullIf()) you can tidy even more to:
Company.Add "stockRlsDt", IIF(Len(rs!stockreleasing), rs!stockreleasing, Null)

 

[Josef P.]

Null equates to False in the IIf()

I'm being picky here: Null is neither true nor false, but it leads into the Else part of IIF.
=>
Company.Add "stockRlsDt", IIF(Len(rs!stockreleasing) = 0, Null, rs!stockreleasing)
Then rs!stockreleasing is inserted, which is Null in this case.

 

[cheekybuddha]

I'm being picky here: Null is neither true nor false, but it leads into the Else part of IIF

Yes, quite right.

From the IIf() point of view, it is Null therefore it is Not True

 

[cheekybuddha]

I'm being picky here: Null is neither true nor false, but it leads into the Else part of IIF.
=>
Company.Add "stockRlsDt", IIF(Len(rs!stockreleasing) = 0, Null, rs!stockreleasing)
Then rs!stockreleasing is inserted, which is Null in this case.

Yes, my phone did something weird and replied before I had finished editing, already fixed

 

[nector]

I'm still getting the same "" instead of null by using the provided function:

Company.Add "stockRlsDt", NullIf(rs!stockreleasing.Value, Null)

 

[cheekybuddha]

I'm still getting the same "" instead of null by using the provided function:

Company.Add "stockRlsDt", NullIf(rs!stockreleasing.Value, Null)

Check Josef's suggestion more carefully

 

[nector]

Okay thanks , this one has worked

IIF(Len(rs!stockreleasing), rs!stockreleasing, Null)

This was given by Josef P.

Many thanks him and all the contributors.

Regards

Chris

 

[cheekybuddha]

With the NullIf(), you used a different value for the second argument than what Josef had suggested.

 

[arnelgp]

With the NullIf(), you used a different value for the second argument

but again, you can't test it against Null.

 

[cheekybuddha]

but again, you can't test it against Null.

But if it is Null then it will return Null, which is what is wanted, no?

 

[arnelgp]

But if it is Null then it will return Null, which is what is wanted, no?

yes, i tested it you are Really a Genius for giving that function, don't you think?