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

 

[Pat Hartman]

You can do it without any code at all. However, you first need to create an export spec. To do this, you'll need to go through the export wizard. Choose fixed width as the export file format and thw wizard will walk you through the process. Before finishing the export, you need to remember to press the advanced button. Here you will be able to specify a date format and a name to call the saved spec. Test. Once the export produces a correctly formatted file, you can automate the process by using the TransferText Method/Action and referencing the saved spec.