Null String value Retrieved from DB

[DaveyCrockett]

Hello all!

I have a dilemma. I have an application that is retrieving null values from the database and when I try to concatenate all of the retieved fields (ie.. grs.Fields(0) & grs.Fields(1) & grs.fields(2))

let's say Field(0) = "Hello"
Field(1) = null
Field(2) = "What is your Name"

if I try to print this out, say like a msgbox. If value 1 is a null string, my output gets cut off prematurely... I would only get "Hello" in my msgbox.

Does anyone know how to go through each of the values and if it is null, replace that value (for the entire length) with spaces or zeros..

I tried this, but the Replace is not working:

Dim i As Integer
i = 0
For i = 0 To 54 'i have 55 fields to check/ a big table

'get the length of the field
Debug.Print grs.Fields(i).Name & ": " & grs.Fields(i).DefinedSize & " ** Actual Size: " & grs.Fields(i).ActualSize
Dim str As String
Dim fieldsize As Long
str = grs.Fields(i).Value
If IsNull(grs.Fields(i).Value) Then
grs.Fields(i).Value = Replace(IsNull(str), Val(str), "0", 1, grs.Fields(i).DefinedSize)
Debug.Print grs.Fields(i).Value
End If
Next i

any help would be greatly appreciated.
Thanks

 

[petevick]

I had this problem and I came up with this solution using IIf....

SELECT IIf(Client.Title <> Null, Client.Title, '') + ' ' + IIf(Client.ForeName <> Null, Client.ForeName, '') + ' ' + IIf(Client.Surname <> Null, Client.Surname, '') AS cName From Client

Client is the table name

Pete Vickerstaff - Hedra Software

http://www.hedrasoftware.co.uk

 

[Golom]

Access has a function called "NZ" that automatically converts NULLs to strings (or numbers if the data type is numeric).

Using NZ, you can condense petevick's code to

SELECT NZ(Client.Title)+ ' ' + NZ(Client.ForeName) + ' ' + NZ(Client.Surname) AS cName From Client

 

[paulbent]

A quick work around is to append "" to the field value:

grs.Fields(0) & "" & _
grs.Fields(1) & "" & _
grs.fields(2) & ""

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[CCLINT]

For numbers, do something like this:

If IsNull(rs.Field("NumberField&quot.Value Then myNumberVariable = 0

myStringVariable = rs.Field("NumberField&quot.Value & vbNullString
or
myStringVariable = Format$(rs.Field("NumberField&quot.Value)

You could create a simple function which accepts an ADODB Field object, and then use a Select Case on the field Type.

For all types of number fields, default it to 0, and for text fields default to vbNullString. Similar to the ACCESS NZ function...

 

[petevick]

The NZ function does not work from VB tho Golom (pity), unless I have to make a reference to an Access resource?.

I havent tried it yet, but would your append "" work within a SQL statement Paul, that would condense my code quite a lot.

Pete Vickerstaff - Hedra Software

http://www.hedrasoftware.co.uk

 

[paulbent]

Yes, but my quick example wasn't very good. You need to assign the field value, with "" or vbNullString appended, to a string variable. Then the string variable will evaluate to an empty string when the field value is null.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[petevick]

I'm not sure I totally follow what you are saying Paul, could you perhaps post a bit of code example to clarify, that would help me understand your description better.

Pete Vickerstaff - Hedra Software

http://www.hedrasoftware.co.uk