stripping out #NULL# in CSV export

[galorin]

I am using the following to write to a CSV file for use in mail merges, but when there is a NULL value, the CSV file contains #NULL# instead of a sensible NULL indicator, like nothing. How can I replace it with something that MSWord expects as a null entry for a mail merge?

Open "c:\Address.csv" For Output As #1

Write #1, "First Name", "Last Name", "Company", "Address 1", "Address 2", "Address 3", "Town", "County", "Postcode"

Person.MoveFirst
Comp.MoveFirst
  Do While Not Person.EOF
    If Not IsNull(Person![AltAdd1]) Then
     Write #1, Person![First Name], Person![Last Name], Person![Company], Person![AltAdd1], Person![AltAdd2], Person![AltAdd3], Person![AltTown], Person![AltCty], Person![AltPcd]
    Else
     Write #1, Person![First Name], Person![Last Name], Comp![Company], Comp![Address 1], Comp![Address 2], Comp![Address 3], Comp![Town], Comp![County], Comp![Postcode]
    End If
   Person.MoveNext
   Comp.MoveNext
  Loop
Close #1

Person.Close
Comp.Close

MsgBox "Records exported to C:\Address.CSV"

 

[Remou]

For the most part & "" should suit:

Write #1, Person![First Name] & "", Person![Last Name] & "", <...>

 

[galorin]

Found another method, involved a for loop and isNull(myField) because I could only do if myField = Null once and the code not fail.

 

[JerryKlmns]

Remou 's suggestion is most efficient because for every record you loop all fields and call a function. It is a needless resource & time consuming way to do it.

 

[galorin]

It's Windows programming. Of course it's going to be resource and time consuming. I've already needed to re-write code that was working because of an Office update that made my early valid and efficient code stop working.

If the performance is impacted by my looping then I'll change to Remou's suggestion, but until then, my code works.