DonnaCulff
Registered User.
- Local time
- Yesterday, 21:46
- Joined
- Aug 25, 2009
- Messages
- 30
Good afternoon all,
I hope everyone is well?
I was wondering If anyone could help with the following problem I am having. I need to export the results of a query in the following format, each row having a line number...
1,61545001,,21/04/2009 08:00,21/04/2009 17:00,16/04/2009 17:00
2,61545001,,28/04/2009 08:00,28/04/2009 17:00,23/04/2009 17:00
3,61545001,,05/05/2009 08:00,05/05/2009 17:00,30/04/2009 17:00
4,61545001,,12/05/2009 08:00,12/05/2009 17:00,07/05/2009 17:00
5,61545001,,19/05/2009 08:00,19/05/2009 17:00,14/05/2009 17:00
6,61545001,,26/05/2009 08:00,26/05/2009 17:00,21/05/2009 17:00
7,61545001,,02/06/2009 08:00,02/06/2009 17:00,28/05/2009 17:00
8,61545001,,09/06/2009 08:00,09/06/2009 17:00,04/06/2009 17:00
9,61545001,,16/06/2009 08:00,16/06/2009 17:00,11/06/2009 17:00
The fields involved are:
''''''''''''''''''My Code'''''''''''''''''''''
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim fld As DAO.Field
Dim strDelimeter As String
Dim strText As String
Dim blnColumnHeaders As Boolean
Dim FilePartOne As String
Dim mdbpath As String
Set db = CurrentDb
blnColumnHeaders = True
strDelimeter = Chr(9)
Set rst = db.OpenRecordset("select * from tblData")
Open "S:\Telemark\PROJECTS\ACTIVE_PROJECTS\exportfile.txt" For Output As #1
Many thanks for any help / ideas in advance
Donna
I hope everyone is well?
I was wondering If anyone could help with the following problem I am having. I need to export the results of a query in the following format, each row having a line number...
1,61545001,,21/04/2009 08:00,21/04/2009 17:00,16/04/2009 17:00
2,61545001,,28/04/2009 08:00,28/04/2009 17:00,23/04/2009 17:00
3,61545001,,05/05/2009 08:00,05/05/2009 17:00,30/04/2009 17:00
4,61545001,,12/05/2009 08:00,12/05/2009 17:00,07/05/2009 17:00
5,61545001,,19/05/2009 08:00,19/05/2009 17:00,14/05/2009 17:00
6,61545001,,26/05/2009 08:00,26/05/2009 17:00,21/05/2009 17:00
7,61545001,,02/06/2009 08:00,02/06/2009 17:00,28/05/2009 17:00
8,61545001,,09/06/2009 08:00,09/06/2009 17:00,04/06/2009 17:00
9,61545001,,16/06/2009 08:00,16/06/2009 17:00,11/06/2009 17:00
The fields involved are:
- txtUnitCode e.g 61545001
- txtStartDate e.g. 21/04/2009
- txtStartTime e.g. 08:00
- txtEndDate e.g. 11/06/2009
- txtEndTime e.g. 17:00
''''''''''''''''''My Code'''''''''''''''''''''
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim fld As DAO.Field
Dim strDelimeter As String
Dim strText As String
Dim blnColumnHeaders As Boolean
Dim FilePartOne As String
Dim mdbpath As String
Set db = CurrentDb
blnColumnHeaders = True
strDelimeter = Chr(9)
Set rst = db.OpenRecordset("select * from tblData")
Open "S:\Telemark\PROJECTS\ACTIVE_PROJECTS\exportfile.txt" For Output As #1
If blnColumnHeaders = True Then
For Each fld In rst.Fields
strText = strText & fld.Name & strDelimeter
Next fld
'get rid of the last delimeter
strText = Left(strText, Len(strText) - Len(strDelimeter))
strText = strText & vbNewLine
End If
Do While Not rst.EOF
For Each fld In rst.Fields
strText = strText & fld.Value & strDelimeter
Next
'get rid of the extra delimeter
strText = Left(strText, Len(strText) - Len(strDelimeter))
strText = strText & vbNewLine
rst.MoveNext
Loop
'removes the extra line
strText = Left(strText, Len(strText) - Len(vbNewLine))
Print #1, strText
Close #1
End Sub
I have tried to create a specification also but it doesnt look like I want it to (the data is used by a web program and must be exact) can I do this in code only? I know there is some formatting above but with a loop required for the incrementing of the line number and an unfamilarity with formatting Im stuggling a little.For Each fld In rst.Fields
strText = strText & fld.Name & strDelimeter
Next fld
'get rid of the last delimeter
strText = Left(strText, Len(strText) - Len(strDelimeter))
strText = strText & vbNewLine
End If
Do While Not rst.EOF
For Each fld In rst.Fields
strText = strText & fld.Value & strDelimeter
Next
'get rid of the extra delimeter
strText = Left(strText, Len(strText) - Len(strDelimeter))
strText = strText & vbNewLine
rst.MoveNext
Loop
'removes the extra line
strText = Left(strText, Len(strText) - Len(vbNewLine))
Print #1, strText
Close #1
End Sub
Many thanks for any help / ideas in advance
Donna