Option Compare Database
Sub sbExportAll()
' create your own csv
Dim db As Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim myLine As String
Set db = CurrentDb
Set rs = db.OpenRecordset("QEMP501Comp")
Set rs1 = db.OpenRecordset("QEMP501Emp")
Set rs2 = db.OpenRecordset("QEMP501Totals")
Open "C:\Users\Server\Documents\Accounting\Test\Emp501.csv" For Output As #1
Do While Not rs.EOF
If Not IsNull(rs!C1) Then
myLine = myLine & rs!C1 & ","
End If
If Not IsNull(rs!CompanyName_CertNo) Then
myLine = myLine & rs!CompanyName_CertNo & ","
End If
If Not IsNull(rs!Test_CertType) Then
myLine = myLine & rs!Test_CertType & ","
End If
If Not IsNull(rs!C3) Then
myLine = myLine & rs!C3 & ","
End If
If Not IsNull(rs!PAYENo_Nature) Then
myLine = myLine & rs!PAYENo_Nature & ","
End If
If Not IsNull(rs!C4) Then
myLine = myLine & rs!C4 & ","
End If
If Not IsNull(rs!SDLNo_TaxYE) Then
myLine = myLine & rs!SDLNo_TaxYE & ","
End If
If Not IsNull(rs!C5) Then
myLine = myLine & rs!C5 & ","
End If
If Not IsNull(rs!UIFNo_Name) Then
myLine = myLine & rs!UIFNo_Name & ","
End If
If Not IsNull(rs!C6) Then
myLine = myLine & rs!C6 & ","
End If
If Not IsNull(rs!ContactPerson_FirstNames) Then
myLine = myLine & rs!ContactPerson_FirstNames & ","
End If
If Not IsNull(rs!C7) Then
myLine = myLine & rs!C7 & ","
End If
If Not IsNull(rs!PhoneContact_Initials) Then
myLine = myLine & rs!PhoneContact_Initials & ","
End If
If Not IsNull(rs!C8) Then
myLine = myLine & rs!C8 & ","
End If
If Not IsNull(rs!EmailContact_ID) Then
myLine = myLine & rs!EmailContact_ID & ","
End If
If Not IsNull(rs!C9) Then
myLine = myLine & rs!C9 & ","
End If
If Not IsNull(rs!Payroll_Passport) Then
myLine = myLine & rs!Payroll_Passport & ","
End If
If Not IsNull(rs!C10) Then
myLine = myLine & rs!C10 & ","
End If
If Not IsNull(rs!TrYear_CountryPP) Then
myLine = myLine & rs!TrYear_CountryPP & ","
End If
If Not IsNull(rs!C11) Then
myLine = myLine & rs!C11 & ","
End If
If Not IsNull(rs!Period_Bdate) Then
myLine = myLine & rs!Period_Bdate & ","
End If
If Not IsNull(rs!C12) Then
myLine = myLine & rs!C12 & ","
End If
If Not IsNull(rs!SIC7_TaxNo) Then
myLine = myLine & rs!SIC7_TaxNo & ","
End If
If Not IsNull(rs!C13) Then
myLine = myLine & rs!C13 & ","
End If
If Not IsNull(rs!SEZ_Sic7Emp) Then
myLine = myLine & rs!SEZ_Sic7Emp & ","
End If
If Not IsNull(rs!C14) Then
myLine = myLine & rs!C14 & ","
End If
If Not IsNull(rs!TradeClass_SEZEmp) Then
myLine = myLine & rs!TradeClass_SEZEmp & ","
End If
If Not IsNull(rs!C15) Then
myLine = myLine & rs!C15 & ","
End If
If Not IsNull(rs!AddressCO_EmailEmp) Then
myLine = myLine & rs!AddressCO_EmailEmp & ","
End If
If Not IsNull(rs!C16) Then
myLine = myLine & rs!C16 & ","
End If
If Not IsNull(rs!PHAddressCity_PHBusEmp) Then
myLine = myLine & rs!PHAddressCity_PHBusEmp & ","
End If
If Not IsNull(rs!C17) Then
myLine = myLine & rs!C17 & ","
End If
If Not IsNull(rs!ZIPCO_CellEmp) Then
myLine = myLine & rs!ZIPCO_CellEmp & ","
End If
If Not IsNull(rs!C18) Then
myLine = myLine & rs!C18 & ","
End If
' Remove the last (useless comma)
myLine = Left(myLine, Len(myLine) - 1)
Write #1, myLine ' Or was it print #1, Myline ???
rs.MoveNext
myLine = ""
Loop
Do While Not rs1.EOF
If Not IsNull(rs1!C1) Then
myLine = myLine & rs1!C1 & ","
End If
If Not IsNull(rs1!CompanyName_CertNo) Then
myLine = myLine & rs1!CompanyName_CertNo & ","
End If
If Not IsNull(rs1!Test_CertType) Then
myLine = myLine & rs1!Test_CertType & ","
End If
If Not IsNull(rs1!C3) Then
myLine = myLine & rs1!C3 & ","
End If
If Not IsNull(rs1!PAYENo_Nature) Then
myLine = myLine & rs1!PAYENo_Nature & ","
End If
If Not IsNull(rs1!C4) Then
myLine = myLine & rs1!C4 & ","
End If
If Not IsNull(rs1!SDLNo_TaxYE) Then
myLine = myLine & rs1!SDLNo_TaxYE & ","
End If
If Not IsNull(rs1!C5) Then
myLine = myLine & rs1!C5 & ","
End If
If Not IsNull(rs1!UIFNo_Name) Then
myLine = myLine & rs1!UIFNo_Name & ","
End If
If Not IsNull(rs1!C6) Then
myLine = myLine & rs1!C6 & ","
End If
If Not IsNull(rs1!ContactPerson_FirstNames) Then
myLine = myLine & rs1!ContactPerson_FirstNames & ","
End If
If Not IsNull(rs1!C7) Then
myLine = myLine & rs1!C7 & ","
End If
If Not IsNull(rs1!PhoneContact_Initials) Then
myLine = myLine & rs1!PhoneContact_Initials & ","
End If
If Not IsNull(rs1!C8) Then
myLine = myLine & rs1!C8 & ","
End If
If Not IsNull(rs1!EmailContact_ID) Then
myLine = myLine & rs1!EmailContact_ID & ","
End If
If Not IsNull(rs1!C9) Then
myLine = myLine & rs1!C9 & ","
End If
If Not IsNull(rs1!Payroll_Passport) Then
myLine = myLine & rs1!Payroll_Passport & ","
End If
If Not IsNull(rs1!C10) Then
myLine = myLine & rs1!C10 & ","
End If
If Not IsNull(rs1!TrYear_CountryPP) Then
myLine = myLine & rs1!TrYear_CountryPP & ","
End If
If Not IsNull(rs1!C11) Then
myLine = myLine & rs1!C11 & ","
End If
If Not IsNull(rs1!Period_Bdate) Then
myLine = myLine & rs1!Period_Bdate & ","
End If
If Not IsNull(rs1!C12) Then
myLine = myLine & rs1!C12 & ","
End If
If Not IsNull(rs1!SIC7_TaxNo) Then
myLine = myLine & rs1!SIC7_TaxNo & ","
End If
If Not IsNull(rs1!C13) Then
myLine = myLine & rs1!C13 & ","
End If
If Not IsNull(rs1!SEZ_Sic7Emp) Then
myLine = myLine & rs1!SEZ_Sic7Emp & ","
End If
If Not IsNull(rs1!C14) Then
myLine = myLine & rs1!C14 & ","
End If
If Not IsNull(rs1!TradeClass_SEZEmp) Then
myLine = myLine & rs1!TradeClass_SEZEmp & ","
End If
If Not IsNull(rs1!C15) Then
myLine = myLine & rs1!C15 & ","
End If
If Not IsNull(rs1!AddressCO_EmailEmp) Then
myLine = myLine & rs1!AddressCO_EmailEmp & ","
End If
If Not IsNull(rs1!C16) Then
myLine = myLine & rs1!C16 & ","
End If
If Not IsNull(rs1!PHAddressCity_PHBusEmp) Then
myLine = myLine & rs1!PHAddressCity_PHBusEmp & ","
End If
If Not IsNull(rs1!C17) Then
myLine = myLine & rs1!C17 & ","
End If
If Not IsNull(rs1!ZIPCO_CellEmp) Then
myLine = myLine & rs1!ZIPCO_CellEmp & ","
End If
If Not IsNull(rs1!C18) Then
myLine = myLine & rs1!C18 & ","
End If
If Not IsNull(rs1!PHWorkEmp) Then
myLine = myLine & rs1!PHWorkEmp & ","
End If
If Not IsNull(rs1!C19) Then
myLine = myLine & rs1!C19 & ","
End If
If Not IsNull(rs1!PHBusTownEmp) Then
myLine = myLine & rs1!PHBusTownEmp & ","
End If
If Not IsNull(rs1!C20) Then
myLine = myLine & rs1!C20 & ","
End If
If Not IsNull(rs1!PHZipEmp) Then
myLine = myLine & rs1!PHZipEmp & ","
End If
If Not IsNull(rs1!C21) Then
myLine = myLine & rs1!C21 & ","
End If
If Not IsNull(rs1!EmpNo) Then
myLine = myLine & rs1!EmpNo & ","
End If
If Not IsNull(rs1!C22) Then
myLine = myLine & rs1!C22 & ","
End If
If Not IsNull(rs1!AssPStart) Then
myLine = myLine & rs1!AssPStart & ","
End If
If Not IsNull(rs1!C23) Then
myLine = myLine & rs1!C23 & ","
End If
If Not IsNull(rs1!AssPEnd) Then
myLine = myLine & rs1!AssPEnd & ","
End If
If Not IsNull(rs1!C24) Then
myLine = myLine & rs1!C24 & ","
End If
If Not IsNull(rs1!PayPYear) Then
myLine = myLine & rs1!PayPYear & ","
End If
If Not IsNull(rs1!C25) Then
myLine = myLine & rs1!C25 & ","
End If
If Not IsNull(rs1!PayPWork) Then
myLine = myLine & rs1!PayPWork & ","
End If
If Not IsNull(rs1!C26) Then
myLine = myLine & rs1!C26 & ","
End If
If Not IsNull(rs1!EmpResAdd) Then
myLine = myLine & rs1!EmpResAdd & ","
End If
If Not IsNull(rs1!C27) Then
myLine = myLine & rs1!C27 & ","
End If
If Not IsNull(rs1!EmpResTown) Then
myLine = myLine & rs1!EmpResTown & ","
End If
If Not IsNull(rs1!C28) Then
myLine = myLine & rs1!C28 & ","
End If
If Not IsNull(rs1!EmpResZip) Then
myLine = myLine & rs1!EmpResZip & ","
End If
If Not IsNull(rs1!C29) Then
myLine = myLine & rs1!C29 & ","
End If
If Not IsNull(rs1!PostSameRes) Then
myLine = myLine & rs1!PostSameRes & ","
End If
If Not IsNull(rs1!C30) Then
myLine = myLine & rs1!C30 & ","
End If
If Not IsNull(rs1!StreetAddYN) Then
myLine = myLine & rs1!StreetAddYN & ","
End If
If Not IsNull(rs1!C31) Then
myLine = myLine & rs1!C31 & ","
End If
If Not IsNull(rs1!POBoxYN) Then
myLine = myLine & rs1!POBoxYN & ","
End If
If Not IsNull(rs1!C32) Then
myLine = myLine & rs1!C32 & ","
End If
If Not IsNull(rs1!POBagYN) Then
myLine = myLine & rs1!POBagYN & ","
End If
If Not IsNull(rs1!C33) Then
myLine = myLine & rs1!C33 & ","
End If
If Not IsNull(rs1!PostOffice) Then
myLine = myLine & rs1!PostOffice & ","
End If
If Not IsNull(rs1!C34) Then
myLine = myLine & rs1!C34 & ","
End If
If Not IsNull(rs1!PostZip) Then
myLine = myLine & rs1!PostZip & ","
End If
If Not IsNull(rs1!C35) Then
myLine = myLine & rs1!C35 & ","
End If
If Not IsNull(rs1!BoxBagNO) Then
myLine = myLine & rs1!BoxBagNO & ","
End If
If Not IsNull(rs1!C36) Then
myLine = myLine & rs1!C36 & ","
End If
If Not IsNull(rs1!BankAccType) Then
myLine = myLine & rs1!BankAccType & ","
End If
If Not IsNull(rs1!c38) Then
myLine = myLine & rs1!c38 & ","
End If
If Not IsNull(rs1!BankAccNo) Then
myLine = myLine & rs1!BankAccNo & ","
End If
If Not IsNull(rs1!c39) Then
myLine = myLine & rs1!c39 & ","
End If
If Not IsNull(rs1!BranchCode) Then
myLine = myLine & rs1!BranchCode & ","
End If
If Not IsNull(rs1!c40) Then
myLine = myLine & rs1!c40 & ","
End If
If Not IsNull(rs1!BankName) Then
myLine = myLine & rs1!BankName & ","
End If
If Not IsNull(rs1!c41) Then
myLine = myLine & rs1!c41 & ","
End If
If Not IsNull(rs1!BranchName) Then
myLine = myLine & rs1!BranchName & ","
End If
If Not IsNull(rs1!c42) Then
myLine = myLine & rs1!c42 & ","
End If
If Not IsNull(rs1!AccHolder) Then
myLine = myLine & rs1!AccHolder & ","
End If
If Not IsNull(rs1!c43) Then
myLine = myLine & rs1!c43 & ","
End If
If Not IsNull(rs1!AccRelation) Then
myLine = myLine & rs1!AccRelation & ","
End If
If Not IsNull(rs1!c44) Then
myLine = myLine & rs1!c44 & ","
End If
If Not IsNull(rs1!Income) Then
myLine = myLine & rs1!Income & ","
End If
If Not IsNull(rs1!c45) Then
myLine = myLine & rs1!c45 & ","
End If
If Not IsNull(rs1!Gross) Then
myLine = myLine & rs1!Gross & ","
End If
If Not IsNull(rs1!c46) Then
myLine = myLine & rs1!c46 & ","
End If
If Not IsNull(rs1!PensionDed) Then
myLine = myLine & rs1!PensionDed & ","
End If
If Not IsNull(rs1!c47) Then
myLine = myLine & rs1!c47 & ","
End If
If Not IsNull(rs1!TotalDed) Then
myLine = myLine & rs1!TotalDed & ","
End If
If Not IsNull(rs1!c48) Then
myLine = myLine & rs1!c48 & ","
End If
If Not IsNull(rs1!uifded) Then
myLine = myLine & rs1!uifded & ","
End If
If Not IsNull(rs1!c49) Then
myLine = myLine & rs1!c49 & ","
End If
If Not IsNull(rs1!SDL) Then
myLine = myLine & rs1!SDL & ","
End If
If Not IsNull(rs1!c50) Then
myLine = myLine & rs1!c50 & ","
End If
If Not IsNull(rs1!TotalDedCE) Then
myLine = myLine & rs1!TotalDedCE & ","
End If
If Not IsNull(rs1!c51) Then
myLine = myLine & rs1!c51 & ","
End If
If Not IsNull(rs1!reasoncode) Then
myLine = myLine & rs1!reasoncode & ","
End If
If Not IsNull(rs1!c52) Then
myLine = myLine & rs1!c52 & ","
End If
If Not IsNull(rs1!Paye) Then
myLine = myLine & rs1!Paye & ","
End If
If Not IsNull(rs1!C78End) Then
myLine = myLine & rs1!C78End & ","
End If
' Remove the last (useless comma)
myLine = Left(myLine, Len(myLine) - 1)
Write #1, myLine ' Or was it print #1, Myline ???
rs1.MoveNext
myLine = ""
Loop
Do While Not rs2.EOF
If Not IsNull(rs2!C1) Then
myLine = myLine & rs2!C1 & ","
End If
If Not IsNull(rs2!CompanyName_CertNo) Then
myLine = myLine & rs2!CompanyName_CertNo & ","
End If
If Not IsNull(rs2!C2) Then
myLine = myLine & rs2!C2 & ","
End If
If Not IsNull(rs2!Test_CertType) Then
myLine = myLine & rs2!Test_CertType & ","
End If
If Not IsNull(rs2!C3) Then
myLine = myLine & rs2!C3 & ","
End If
If Not IsNull(rs2!PAYENo_Nature) Then
myLine = myLine & rs2!PAYENo_Nature & ","
End If
If Not IsNull(rs2!C4) Then
myLine = myLine & rs2!C4 & ","
End If
' Remove the last (useless comma)
myLine = Left(myLine, Len(myLine) - 1)
Write #1, myLine ' Or was it print #1, Myline ???
rs2.MoveNext
myLine = ""
Loop
Close
End Sub