better to export from spec or create export in VB

[tagteam]

I have a table that I want to export to 500 byte file. I dont need all the fields and I dont see a way to select some fields to not export like the option on the import specification.

Is there a way to use VB to export certain fields from a table or query to a flat 500 byte fixed length file. I will need to be able to specify the number of characters for each field and have them be blank if there is no value there.


Thanks

[ranman256]

Build a query of ONLY the fields you want to export.
export THAT query.

[tagteam]

Thank you that works well.

I also found this code

Public Type NameofRecord
  VarRecordFormat As String * 5
  VarBillingInvoicingParty As String * 4
  VarBilledParty As String * 4
  
End Type
 
Public Sub OutputTextfile()
Dim rs As DAO.Recordset
Dim objFile As Object, TextFile As Object
Dim TextRecord As NameofRecord
Set rs = CurrentDb.OpenRecordset("q_500ByteExport")
Set objFile = CreateObject("Scripting.FileSystemObject")
Set TextFile = objFile.CreateTextFile("C:\tmp\export.txt", True)
Do Until rs.EOF
  With TextRecord
    .VarRecordFormat = CStr(rs![RecordFormat])
    .VarBillingInvoicingParty = CStr(rs![BillingInvoicingParty])
    .VarBilledParty = CStr(rs![BilledParty])
    
    TextFile.WriteLine (.VarRecordFormat & .VarBillingInvoicingParty & .VarBilledParty)
  End With
  rs.MoveNext
Loop
rs.Close
TextFile.Close
End Sub

It works like I want but the problem is that if there is a null value in any of the fields it does not work. Does anyone know how to get this to work with null fields?
Thanks

[June7]

The CStr() function errors on Null, as do all conversion functions.

But if exporting Null is an issue, handle possible Null with Nz() function. Provide some alternate value:

Nz(rs!RecordFormat, "none")

The Nz() function results in a string entity and CStr() is unnecessary.

[tagteam]

Thanks the Nz seems to be working. The problem with just using the query is that if the field was blank it didnt keep the specified number of characters and a file that should have been 500 chars was only 137 because a lot of the fields are blank.