Export Query formatting problems - numeric and date/time

[Guest]

Hi all,

I am trying to fix an export of a query where I am trying to format data into a fixed length flat file. I have a problem with two of my fields. In the query window I have a numeric field which shows dollars only (no decimals), example:

1000
1
2500
2400

However, when I export the query I get results like the following:

1000.00
1.00
2500.00
2400.00

The same seems to be happening with a date field. Again, in the query window:

7/12/2004

In the flat file it has the following (it adds time, I formatted to change the order to YMD):

20040712 00000

I am in Access 2000. HELP!!!!! How do I fix this to eliminate the decimals and the time stamp?

 

[Guest]

As a bit of further clarification: I tried everything I could think of, from formatting the numbers in the query window (using a Clng command, a format number command) to changing the source table data (to integer from double, to text) and nothing works.

I can solve the date issue since they are all fixed lengths by changing the size of the output to 8 digits long, but the numeric field doesn't work, and since the length is variable, I cannot just simply cut it down. Still looking for suggestions!

 

[Gary Walter]

Hi Daniel,

I'd just write the file myself,
formatting as I like:

Private Sub cmdExportQuery_Click()
On Error GoTo Err_cmdExportQuery
Dim hFile As Long
Dim varPath As Variant
Dim strTextLine As String
Dim lngRecCount As Long
Dim rst As DAO.Recordset
Dim varReturn As Variant
Dim strMsg As String

DoCmd.Hourglass True
varPath = "C:\newfile.txt"
Set rst = CurrentDB.OpenRecordset("nameofyourquery", dbOpenDynaset)

'**** collect all records from rst into strTextLine *****
'**** (comma delimited, text in quotes) *****

With rst

.MoveFirst

lngRecCount = 0
strTextLine = ""
Do While (Not .EOF)

lngRecCount = lngRecCount + 1
' tell user what you are doing by writing to Status Bar
strMsg = " Now Outputting Record " & lngRecCount & " to the file :" &
varPath
varReturn = SysCmd(acSysCmdSetStatus, strMsg)
'Chr(34) = " (double quote)
strTextLine = strTextLine & !fieldID & ", "
strTextLine = strTextLine & Chr(34) & !fieldTxt & Chr(34) & ", "
strTextLine = strTextLine & Format(!fieldcur," "##,##0") & ", "
strTextLine = strrTextLine & Format(!fielddate, "yyyymmdd") & vbCrLf
.MoveNext
Loop

End With

rst.Close

'**** Save the collected string to the text file *****

'Will be using Put method so want to first
'delete file if it already exists
On Error Resume Next
Kill varPath
On Error GoTo Err_cmdExportQuery

'Get a new file handle
hFile = FreeFile

'Open the varPath to write the memo
Open varPath For Binary Access Write As #hFile

'write collected lines to file
Put #hFile, 1, strTextLine

' done so close file.
Close #hFile

varReturn = SysCmd(acSysCmdSetStatus, "Export Process Complete.")

Exit_cmdExportQuery:
Set rst = Nothing
Reset
DoCmd.Hourglass False
Exit Sub
Err_cmdExportQuery:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
Resume Exit_cmdExportQuery
End Sub

You have *full control* of output now.

Good luck,

Gary Walter

As a bit of further clarification: I tried everything I could think of, from

formatting the numbers in the query window (using a Clng command, a format number
command) to changing the source table data (to integer from double, to text) and
nothing works.

I can solve the date issue since they are all fixed lengths by changing the size of

the output to 8 digits long, but the numeric field doesn't work, and since the length
is variable, I cannot just simply cut it down. Still looking for suggestions!fixed length flat file. I have a problem with two of my fields. In the query window
I have a numeric field which shows dollars only (no decimals), example: