CODEWITHSUNDEEP
vbams-access
thanasis2020
thanasis2020

Reputation: 5

Extract all rows form table to text

I am using the following code to extract all rows from my table to a text file. I am facing a issue. If the value is empty, I am getting the previous row value for that column. Here is my code. 

Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim strQry As String
Dim aRow(1 To 16) As String
Dim aBody() As String
Dim lCnt As Long

lCnt = 1
ReDim aBody(1 To lCnt)

strQry = "SELECT * From tbllast"
Set db = CurrentDb
Set rec = CurrentDb.OpenRecordset(strQry)

If Not (rec.BOF And rec.EOF) Then
    Do While Not rec.EOF
        lCnt = lCnt + 1
        ReDim Preserve aBody(1 To lCnt)                       
        aRow(1) = rec("cust_id")                        
        aRow(2) = Format(rec("app_date"), "yyyy/mm/dd")  
        aRow(3) = rec("work_id")                         
        aRow(4) = rec("app_price")                       
        aRow(5) = rec("doc_id")                          
        aRow(6) = rec("ass_id")                          
        aRow(7) = rec("pla_id")                          
        aRow(8) = NULL                             
        aRow(9) = NULL                            
        aRow(10) = rec("app_memo")              
        aRow(11) = rec("fin_price")                      
        aRow(12) = NULL                            
        aRow(13) = rec("pay_id")                         
        aRow(14) = rec("receipt")                        
        aRow(15) = rec("fin_memo")   
        aRow(16) = Format(rec("app_date"), "yyyy/mm/dd") 
        aBody(lCnt) = "('" & Join(aRow, "','") & "'),"
        rec.MoveNext
        Debug.Print aBody(lCnt)
    Loop
End If

aBody(lCnt) = aBody(lCnt)

Debug.Print Join(aBody, vbNewLine)

I am facing the problem in all columns.

Any ideas.

Upvotes: 0

Views: 57

Answers (2)

Michael Alan Cohen
Michael Alan Cohen

Reputation: 86

You should check if a value is null before writing it with IsNull and maybe assigning it an empty string with ''. You can use the Nz function to achieve this:

aRow(1) = Nz(rec("cust_id").Value)

Upvotes: 0

Gustav
Gustav

Reputation: 55906

Use Nz on all fields that could be Null to return an empty string instead:

aRow(1) = Nz(rec("cust_id").Value) 
... etc.

Upvotes: 1

Related Questions