All text and NUMBERS should be enclosed with " "

[Garry]

Hi all

I am required to output a report to a comma delimited csv file.

This report containes summing over group and cumulative over group output

All text and NUMBERS should be enclosed with " "

I am getting the following as an example :-

"Text","Text",Number

I need

"Text","Text","Number"

any ideas please, cheers

 

[Arvin Meyer [MVP]]

Here's some code that I used. Note that Chr$(34) is a double-quote. This
should output everything surrounded by a double-quote. If it doesn't, add an
extra " & Chr$(34)" or even """ (3 double-quotes) where you need it:

Function MakeTextFile()
On Error GoTo Error_Handler
Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim qdef As QueryDef

Set db = CurrentDb
Set qdef = db.QueryDefs("qryWhatever")

If Not IsNull(Me.txtFileName) Then
Me.txtFilePath = ""

Set rst = qdef.OpenRecordset

rst.MoveFirst

Open "S:\POFiles\" & "PO-" & Format(Date, "mmddyy") & ".txt" For Append
As #1

Print #1, Chr$(34) & "*" & Chr$(34) & "," & Chr$(34) & Me.txtFileName &
Chr$(34) & _
"," & Chr$(34) & Me.txtFileName & Chr$(34) & "," & Chr$(34) & Chr$(34) &
"," _
& Chr$(34) & Chr$(34) & "," & Chr$(34) & Chr$(34) & "," & Chr$(34) &
Chr$(34) & "," _
& Chr$(34) & Chr$(34) & "," & Chr$(34) & Chr$(34) & "," & Chr$(34) &
Chr$(34) & "," _
& Chr$(34) & Chr$(34) & "," & Chr$(34) & Chr$(34) & ",," & Chr$(34) &
Chr$(34) & "," _
& "1," & Chr$(34) & Chr$(34) & "," & Chr$(34) & Chr$(34)

Do Until rst.EOF
Print #1, Chr$(34) & "C" & Chr$(34) & "," & Chr$(34) &
Format(rst!CostCode, "0.000") & Chr$(34) & "," _
& Chr$(34) & Space(30) & Chr$(34) & "," & Chr$(34) & "S " & Chr$(34) &
"," _
& Format(Date, "mmddyyyy") & ",0.00," & Chr$(34) & Chr$(34) & "," &
rst!FormatCost _
& ",,1," & Chr$(34) & Chr$(34) & "," & Chr$(34) & Chr$(34)

rst.MoveNext
Loop

Close #1
MsgBox "Done!"

End If

Exit_Here:
Exit Function
Set qdef = Nothing
rst.Close
Set rst = Nothing
Set db = Nothing

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Close #1
Resume Exit_Here
End Function

 

[Dale Fye]

Arvin,

To make thing easier to read, I have a fnQuotes( ) function that I use all
the time for stuff like this, or when writing dynamic SQL in my code. I find
this infinitely easier to read than either of these, and a lot quicker to
type.

Chr$(34) & me.txtABC & chr$(34)
or
""" & me.txtABC & """

The function looks like:

Public Function fnQuotes(Expression as Variant) as String

fnQuotes = chr$(34) _
& Replace(NZ(Expression, ""), chr$(34), chr$(39)) _
& chr$(34)

End Function

It accepts a Variant Expression to account for those occassions where a
field value is Null. It then replaces any double quotes within the
Expression with single quotes (apostrophe), and then wraps the whole thing in
double quotes.

So the loop in your code would look like:

Do Until rst.EOF
Print #1, Quotes("C") & "," _
& Quotes(Format(rst!CostCode, "0.000")) & "," _
& Quotes(Space(30)) & "," _
& Quotes("S ") & "," _
& Quotes(Format(Date, "mmddyyyy") & ",0.00,") "," _
& Quotes(rst!FormatCost & ",,1,")
rst.MoveNext
Loop

Dale

 

[Arvin Meyer [MVP]]

Looks neat but I think you mean:

Do Until rst.EOF
Print #1, Quotes("C") & "," _
& fnQuotes(Format(rst!CostCode, "0.000")) & "," _
& fnQuotes(Space(30)) & "," _
& fnQuotes("S ") & "," _
& fnQuotes(Format(Date, "mmddyyyy") & ",0.00,") "," _
& fnQuotes(rst!FormatCost & ",,1,")
rst.MoveNext
Loop

I could have also used a variable like:

Dim strDQ as String
strDQ = Chr$(34) & "," & Chr$(34)

The reason I did it the longer way was to keep track of every thing in my
text editor. I use NoteTab as a text editor for code that's tedious like
that and I can keep track of all the commas, quotes, etc. better to make
sure I didn't miss anything. One thing for sure, missing anything in
something that complex is difficult to find.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Dale Fye]

You're right. At work, I have it without the fn in the application I am
currently working on.

Dale

 

[Guest]

There is an application method in all recent versions of Access.

(david)