VBA ADODB recordset field is Null from MySQL - is there an easy way to check or prevent "NULL"?

Question

I pull a large recordset from a MySQL server and I assign a bunch of variables in an Excel VBA routine based on the field position, e.g.:

x = MyRecordset.Fields(0).Value
y = MyRecordset.Fields(1).Value
' etc...

The only time this throws an error is when one of the values is NULL, because VBA doesn't allow a string to be NULL. I have a question for both sides, VBA and the MySQL side in case the VBA side falls down:

VBA: I'm currently using On Error Resume Next to skip the NULL error - is there a better / more official way to achieve this? Is this error skipping slowing my code down?

MySQL: Is there a way to prevent "NULL" being sent, e.g. to quickly replace instances of NULL with an empty string in my SELECT statement?

https://stackoverflow.com/questions/5285448/mysql-select-only-not-null-values — Tehscript, May 09 '18 at 08:28
That would only give me rows that didn't contain NULL values in any field right? I still want those rows, I just want the one or two instances of NULL to be replaced with "" — jamheadart, May 09 '18 at 08:31
Maybe I just need to not allow any NULL values in my database to begin with and default everything to an empty string... — jamheadart, May 09 '18 at 08:36
Why don't you check with `IsNull(MyRecordset.Fields(0).Value)` before you do `x = MyRecordset.Fields(0).Value` — Storax, May 09 '18 at 08:40
Thanks! `IsNull` - I knew there was a proper check for it... — jamheadart, May 09 '18 at 08:43

score 3 · Answer 1 · answered May 09 '18 at 13:42

If I remember correctly, if you have a variable that might hold null, concatenating an empty string will turn the variable into an empty string, while having no affect on any other value. But that might not work with ADO recordsets

 x = MyRecordset.Fields(0).Value & ""

Philippe Grondier · Answer 2 · 2018-05-09T11:19:03.800

1

How to do that:

1 - on the VBA side

If I am not wrong, there is already a VBA function that does the job of converting nulls to default values. In your case that would then be

Nz(MyRecordset.Fields(1).Value,"")

Returning a zero length string if the field value is null.

2 - on the MYSQL side

You could use the COALESCE function, that sneds back the first non-null value in its parameters list. Your select will then look like:

SELECT COALESCE(myFieldName,'')

edited May 09 '18 at 11:19

answered May 09 '18 at 11:10

Philippe Grondier

10,900
3
33
72

Hi, thanks for the input, the `Nz()` function apparently only works in Access and I'm using Excel - Helpful though, since I did a search for it and I saw a good snippet to recreate it based on `IIF()` - as for the `COALESCE()` idea, also great, I will do some speed tests using both to see which one is faster - I think the post-query processing in Excel might be quicker and put the 'CPU' onus on the user's application rather than the server. – jamheadart May 09 '18 at 12:07

score 0 · Accepted Answer · answered May 09 '18 at 08:46

0

Use a function like that

Option Explicit

Function rcdString(rcdField As Variant) As String

    If IsNull(rcdField) Then
        rcdString = ""
    Else
        rcdString = rcdField.Value
    End If

End Function

And

x = rcdString(MyRecordset.Fields(0))

answered May 09 '18 at 08:46

Storax

11,158
3
16
33

Thanks, I'll accept this, I have a module of converting functions of a similar ilk so I think this would fit nicely in that toolbox. – jamheadart May 09 '18 at 08:57

VBA ADODB recordset field is Null from MySQL - is there an easy way to check or prevent "NULL"?

3 Answers3