Losing my trailing zero

[Guest]

I'm using VB to pull data from a table to create an xml file. The Amount
field is stored as a datatype decimal and I can see the trailing zeros in the
table.( I'd store the amount as currency but I don't want the $ sign.) But
when I run it through my code I lose the trailing zeros in the xml file.

I've tried to convert to currency and string but I still lose any zeros to
the right of the decimal. Any idea how I can retain the zeroes?
Thanks
Todd

 

[fredg]

I'm using VB to pull data from a table to create an xml file. The Amount
field is stored as a datatype decimal and I can see the trailing zeros in the
table.( I'd store the amount as currency but I don't want the $ sign.) But
when I run it through my code I lose the trailing zeros in the xml file.

I've tried to convert to currency and string but I still lose any zeros to
the right of the decimal. Any idea how I can retain the zeroes?
Thanks
Todd

If you wish to force the display of trailing zeros, then set the
Format property of the control to:
#.00
(for 2 zeros or #.0000 for 4 zeros, etc.)
which will force two zeros.

 

[Douglas J. Steele]

Create a query that uses the Format function to present the data as you like
it, and then export the query, not the table.

 

[Guest]

So instead of using ADO to open the table I would open a query of the table ?
Embedded in the query is a format statement that gives me the correct number.

In my code what is the best datatype to use in my Dim AMOUNT as statement?

Thanks again

 

[Douglas J. Steele]

Where do you have a Dim AMOUNT statement? Aren't you just using the built-in
ExportXML method?

 

[Guest]

Actually I'm not using that method. It produces some formatting that our
ancient ERP system doesn't like. (as per our central office) We are forced
to build it via Writelines into a textfile. So I DIM a variable for each
column name and loop through building the file.

 

[Douglas J. Steele]

What does your code look like?

 

[Guest]

Whats the best way to show you?

What does your code look like?

 

[Guest]

Dim statements...

Set idsfile = fso.CreateTextFile("c:\ladeBL" & mm_new & dd_new & yyyy &
".xml", True)


idsfile.writeline ("<?xml version=""1.0""?>")
idsfile.writeline ("<LADE_00000060>")
idsfile.writeline ("<PROPERTIES>")
idsfile.writeline ("<SOURCE>LADE</SOURCE>")
idsfile.writeline ("<FUNCTION>ACB</FUNCTION>")
idsfile.writeline ("<INST>0000</INST>")
idsfile.writeline ("<INST_PIN>0001</INST_PIN>")
idsfile.writeline ("<LADE_ID>00000060</LADE_ID>")
idsfile.writeline ("<TARGET/>")
idsfile.writeline ("<COMP/>")
idsfile.writeline ("<OPER/>")
idsfile.writeline ("<DATE>" & yyyy & mm_new & dd_new & "</DATE>")
idsfile.writeline ("</PROPERTIES>")


' read Batch Table and collect values
Set rsMyRS = CurrentDb.OpenRecordset("batch")

i = 1000
If Not rsMyRS.EOF Then rsMyRS.MoveFirst
Do Until rsMyRS.EOF

i = i + 1
CUSTOMER_ID = rsMyRS!CUSTOMER_ID
If CUSTOMER_ID = "1" Then
rsMyRS.MoveNext
Else
Dollar_Amount = rsMyRS!AMOUNT
RC_ID = rsMyRS!RC_ID
/...Edited for Brevity../
PONbr = rsMyRS!PO_NBR

'write to xml file
idsfile.writeline ("<UTF_BATCH>")
idsfile.writeline ("<RC_ID>" & RC_ID & "</RC_ID>")
/.....Edited for brevity ..../
idsfile.writeline ("<AMOUNT>" & Dollar_Amount & "</AMOUNT>")
idsfile.writeline ("<POPULATE_SUBTABLE_FLAG>" & PopFlag
& "</POPULATE_SUBTABLE_FLAG>")
rsMyRS.MoveNext

End If
Debug.Print i & " " & CUSTOMER_ID
Loop
rsMyRS.Close

idsfile.writeline ("</LADE_00000060>")

'Close stuff
idsfile.Close

 

[Douglas J. Steele]

I'd define

Dim Dollar_Amount As String

and set

Dollar_Amount = Format(rsMyRS!AMOUNT, "Currency")

 

[Guest]

Tried it. I get the trailing zero but also the dollar sign which I can't have.
Is there a format without the dollar sign?

 

[Guest]

Well I got it.

Used FormatNumber(amount,2)

Thanks for the help.

Todd