Import textfile some value shows Null value

[nmapeso]

Hello, Im using this code to export table to text file. But some record have empty value or null. Is there a way to show on text file to show blank instead of Null

Private Sub cmdTxt1_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = Application.CurrentDb
Set rs = db.OpenRecordset("tblT5")
rs.MoveFirst

'remove the file (comment this line out if you run the func 'the first time)
Kill "C:/Documents and Settings/hlcy/Desktop/T5.txt"

Open "C:/Documents and Settings/hlcy/Desktop/T5.txt" For Output As #1

'if you don't want the fieldnames in your flatfile,
'comment out the following two lines
Print #1, ; "!"; rs.Fields("T5").Name; Chr(9) _
; rs.Fields("NAME1").Name; Chr(9) _
; rs.Fields("NAME2").Name; Chr(9) _
; rs.Fields("ADDRESS1").Name; Chr(9) _
; rs.Fields("ADDRESS2").Name; Chr(9) _
; rs.Fields("CITY").Name; Chr(9) _
; rs.Fields("PROV").Name; Chr(9) _
; rs.Fields("CODE").Name; Chr(9) _
; rs.Fields("SIN").Name; Chr(9) _
; rs.Fields("RECTYPE").Name; Chr(9) _
; rs.Fields("INTEREST").Name; Chr(9) _
; rs.Fields("ACCOUNTNO").Name

Do Until rs.EOF
Print #1, rs.Fields("T5").Value; Chr(9) _
; rs.Fields("NAME1").Value; Chr(9) _
; rs.Fields("NAME2").Value; Chr(9) _
; rs.Fields("ADDRESS1").Value; Chr(9) _
; rs.Fields("ADDRESS2").Value; Chr(9) _
; rs.Fields("CITY").Value; Chr(9) _
; rs.Fields("PROV").Value; Chr(9) _
; rs.Fields("CODE").Value; Chr(9) _
; rs.Fields("SIN").Value; Chr(9) _
; rs.Fields("RECTYPE").Value; Chr(9) _
; rs.Fields("INTEREST").Value; Chr(9) _
; rs.Fields("ACCOUNTNO").Value
'rs.Fields("Fieldname1").Value _
'; Chr(9); rs.Fields("Fieldname2").Value; Chr(9) _
'; Format(rs.Fields("YourDatefield").Value, "yyyy-mm-dd")
rs.MoveNext
Loop

Close #1

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

Example -
text file
!T5 NAME1 NAME2 ADDRESS1 ADDRESS2 CITY PROV CODE SIN RECTYPE INTEREST ACCOUNTNO
T5 Sworik, Brian Test 4530 Crosswind Dr Testing Fenwick ON L0S1C0 230123890 2 1002.23 60-215888-0
T5 Minns-roy, Eddie Null 210 Sommerset St Null Ottawa ON K2P0J4 102392389 1 234.45 60-B4-7033-6
T5 Calhoun, Ronald Null 426 Drummond St Null Brighton BC K0K1H0 300121234 3 234 60-12-2387-1
T5 Mapeso, Noel Null 6390 Spinnaker Circle Null Mississauga ON L5W1R1 343923438 1 233.39 60-12-1239-0
T5 James Cameron Dale Tecson 34 Islington Avenue Null Etobicoke ON L4H1H3 239012389 3 234.45 60-12-2349
T5 Al Dean Null 234 Finch East Avenue Null Scarborough QC J3R1U4 123909889 2 100.34 60-12-238988
T5 Jeremy Falo Null 290 Steeles Ave E Null Toronto BC Y3U7H6 234343343 3 200.5 60-12-2345
T5 John Spencer Null 30 Drummond Dr Null Windsor ON H3J1U5 343912329 3 300.5 60123293109

 

[PHV]

Have you tried to play with the Nz function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nmapeso]

Thanks PHV,
That works...I have used Nz previously on some of my project long time ago but sometimes I just forgot. Thanks for reminding.