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?

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
– jamheadart, Commented May 9, 2018 at 8: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
– jamheadart, Commented May 9, 2018 at 8:36
Why don't you check with IsNull(MyRecordset.Fields(0).Value) before you do x = MyRecordset.Fields(0).Value — Storax
– Storax, Commented May 9, 2018 at 8:40

Harassed Dad · Accepted Answer · 2018-05-09 13:42:03Z

3

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 & ""

answered May 9, 2018 at 13:42

Harassed Dad

4,7141 gold badge12 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Philippe Grondier · Accepted Answer · 2018-05-09 11:19:03Z

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 9, 2018 at 11:19

answered May 9, 2018 at 11:10

Philippe Grondier

11.1k3 gold badges37 silver badges75 bronze badges

1 Comment

jamheadart Over a year ago

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.

Storax · Accepted Answer · 2018-05-09 08:46:41Z

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 9, 2018 at 8:46

Storax

12.7k3 gold badges21 silver badges36 bronze badges

1 Comment

jamheadart Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related