Export text (space delimited)

[doran_doran]

I have a query,

"SELECT tbl_groups.GA_Number, tbl_groups.PlanNum, Format([PYE],"yyyy") AS Year, tbl_groups.Plan_Name, tbl_groups.GA_Name, tbl_groups.GA_Contact_Person, tbl_groups.Ga_Address1, tbl_groups.Ga_Address2, tbl_groups.Ga_City, tbl_groups.Ga_State, tbl_groups.Ga_Zip, tbl_groups.Primary_Administrator, DLookUp("([phone])","tbl_admin","[contact] = '" & [primary_administrator] & "'") AS Extension, tbl_groups.Manager, tbl_groups.Market_sgmt, DLookUp("([region_no])","tbl_region","[region] = '" & [region] & "'") AS [Region No], tbl_groups.BillingStructure
FROM tbl_groups
WHERE (((Format([PYE],"yyyy"))=[enter year]) AND ((tbl_groups.BillingStructure)="Group Billed"));
"

I need to export this query according to ATTACHED EXCEL FILE FORMAT (PLEASE SEE THE ATTACHED EXCEL FILE).

How can I do this? I have a form and I would love to do it using VBA.

PROMPT HELP WILL BE APPRECIATED. I SPENT ALMOST ALL DAY AND NOW I FEEL LIKE MY BOSS IS GOING TO KICK MY ASS.

LATER..

 

[WayneRyan]

Jamils,

Here's some VBA to do it, I didn't look up the
real lengths of the fields. This is just a
"proof of concept".

Dim dbs As DAO.Database
Dim rst As DAO.RecordSet
Dim sql As String

Open "SomeFile.Txt" For Output As #1

Set dbs = CurrentDb
sql = "SELECT GA_Number, " & _
      "       PlanNum, " & _
      "       Format([PYE],"yyyy") AS Year, " & _
      "       Plan_Name, " & _
      "       GA_Name, " & _
      "       GA_Contact_Person, " & _
      "       Ga_Address1, " & _
      "       Ga_Address2, " & _
      "       Ga_City, " & _
      "       Ga_State, " & _
      "       Ga_Zip, " & _
      "       Primary_Administrator, " & _
      "       DLookUp("[phone]", "tbl_admin", "[contact] = '" & [primary_administrator] & "'") AS Extension, " & _
      "       Manager, " & _
      "       Market_sgmt, " & _
      "       DLookUp("[region_no]", "tbl_region", "[region] = '" & [region] & "'") AS [Region No], " & _
      "       BillingStructure " & _
      "FROM tbl_groups " & _
      "WHERE Format([PYE],"yyyy") = #2004#) AND " & _
      "      BillingStructure = 'Group Billed';"
Set rst = dbs.OpenRecordSet(sql)
While Not rst.EOF and Not rst.BOF
   ' Format Fields
   ' For each field, print field contents, pad to length and follow with a space
   Print #1, rst!GA_Number & Space(5 - Len(rst!GA_Number));
   Print #1, rst!PlanNum & Space(2 - Len(rst!PlanNum));
   .
   .
   .
   Print #1, rst!BillingStructure & Space(??? - Len(rst!BillingStructure))
   ' No ";" at end completes the record.
   Wend
Close #1
Set rst = Nothing
Set dbs = Nothing

Wayne