Exporting Problem

[EthanHunt007]

I need to export a query as below

Mark, Smith, 10 Garden Street, London, LN1 4RF
Fred, Spencer, 12 Red Street, Luton, LU7 5TF

AS

Mark
Smith
10 Garden Street
London
LN1 4RF
Fred
Spencer
12 Red Street
Luton
LU7 5TF

any blank fields need to be spaced aswell

can any one please help.

Mark

 

[WayneRyan]

Mark,

In the simplest sense:

Dim rst As DAO.Recordset
Dim varArray As Variant

Open "C:\YourFile.Txt" For Output As #1

Set rst = CurrentDb.OpenRecordset("Select * From YourQuery")
While Not rst.EOF And Not rst.BOF
   varArray = Split(rst!QueryLine, ",")
   Print #1, varArray(0)
   Print #1, varArray(1)
   Print #1, varArray(2)
   Print #1, varArray(3)
   Print #1, varArray(4)
   rst.MoveNext
   Wend
Close #1

Wayne

 

[EthanHunt007]

I've tried using the above code but it error's at the split command?

 

[WayneRyan]

Ethan,

What's the error?

You're not using rst!QueryLine are you? You need to replace QueryLine
with your field name.

Need more info.

Wayne

 

[EthanHunt007]

do i put all the fieldnames in the line ie

varArray = Split(rst!sortcode,accountno,name,amount)

or just 1

 

[WayneRyan]

Ethan,

I was under the assumption that your Recordset had only 1 field:

Mark, Smith, 10 Garden Street, London, LN1 4RF

If your recordset has several individual fields, than you don't need the split
function, you could actually export it as a comma-separated-value from
your table or query.

If you need to split only SOME fields like "NAME", then you'd need to use
something like:

Dim rst As DAO.Recordset
Dim varArray As Variant
Dim i As Long

Open "C:\YourFile.Txt" For Output As #1

Set rst = CurrentDb.OpenRecordset("Select * From YourQuery")
While Not rst.EOF And Not rst.BOF

   Print #1, rst!sortcode
   Print #1, rst!accountno
   '
   varArray = Split(rst!Name, ",")
   For i = 0 TO UBound(varArray)
      Print #1, varArray(i)
      Next i
   '
   Print #1, amount
   rst.MoveNext
   Wend
Close #1

Need a little more info tho,
Wayne

 

[EthanHunt007]

This is the coding i have for a button, but its saying 2 few parameters expected 2.

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click

Dim rst As DAO.Recordset
Dim varArray As Variant

Open "C:\BacsQuery.Txt" For Output As #1

Set rst = CurrentDb.OpenRecordset("Select * From Bacs Query")
While Not rst.EOF And Not rst.BOF

Print #1, rst!Sortcode
Print #1, rst!ContactLastName
Print #1, rst!AccountNo
Print #1, rst!MarginNet
Print #1, rst!BacsRef
rst.MoveNext
Wend
Close #1

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub

query is called Bacs and it is generated by start date and end date

 

[WayneRyan]

Mark,

We're getting there:

Set rst = CurrentDb.OpenRecordset("Select * From Bacs")

But, if it is expecting a Start/End Date, I'd either:

Explicitly:

Set rst = CurrentDb.OpenRecordset("Select * " & _
                                  "From Bacs " & _
                                  "Where StartDate >= #1/1/2001# And " & _
                                  "      EndDate   <= #1/27/2008#")

Or use form controls:

Set rst = CurrentDb.OpenRecordset("Select * " & _
                                  "From Bacs " & _
                                  "Where StartDate >= #" & Forms!YourDialogForm!StartDate & "# And " & _
                                  "      EndDate   <= #" & Forms!YourDialogForm!StartDate & "#")

Wayne

 

[EthanHunt007]

how would i get it so that when i click the button a box pops up and says enter start date and then enter end date, so i can put different ones in. the field they look at are paidviadate and it filters all record sets between start and end.

 

[WayneRyan]

Mark,

You fire off a little popup form where someone enters the dates.

Then, that form fires off the report, form, Or your export code ... whatever.

Sorry, I forgot what we were talking about.

Only for the moment,
Wayne

 

[EthanHunt007]

Sorry to be a pain wayne, but how do i do that?

 

[WayneRyan]

Mark,

Can you post a sample of your DB? (In Access 2000)

I'll have a go at it tonight after work.

Wayne