Transfer Text

[NetworkTrade]

Access03; have working button with:
DoCmd.TransferText acExportDelim, , "EXP2-a", "C:\TxtTEST.txt"

EXP2-a is a Aggregate query (sum)

Questions:
1. one field is a date; when query shows 08/01/2008 the txt file is
displaying 39661 when the query shows 07/31/2008 the txt file is
displaying 39660 . It is displaying an absolute value counting from a system
start date I guess. How do I get this as the date format?

2. all the fields are 'GroupBy' except for one which is SUM. This one
displays in the query as 48 but in the txt file as 48.00 - - - pretty minor
but can this be changed?....perhaps in the table parameters maybe

....just surprised to see the txt file differ from the query result in these
two...

3. the Docmd will need ultimately to have dynamic path / file name in the
string based on values in txt boxes in Form1. What is correct syntax to
inset?
" ' " Forms!Form1.PathName" ' " ??

thanks in advance for input

 

[John W. Vinson]

Access03; have working button with:
DoCmd.TransferText acExportDelim, , "EXP2-a", "C:\TxtTEST.txt"

EXP2-a is a Aggregate query (sum)

Questions:
1. one field is a date; when query shows 08/01/2008 the txt file is
displaying 39661 when the query shows 07/31/2008 the txt file is
displaying 39660 . It is displaying an absolute value counting from a system
start date I guess. How do I get this as the date format?

Correct analysis! Date/Time values are stored as a count of days and fractions
of a day (times) since midnight, December 30, 1899. For TransferText use a
query with a calculated field explicitly formatting the value:

ExpDate: Format([fieldname], "mm/dd/yyyy")

2. all the fields are 'GroupBy' except for one which is SUM. This one
displays in the query as 48 but in the txt file as 48.00 - - - pretty minor
but can this be changed?....perhaps in the table parameters maybe

Again use the Format.

...just surprised to see the txt file differ from the query result in these
two...

They just do.

3. the Docmd will need ultimately to have dynamic path / file name in the
string based on values in txt boxes in Form1. What is correct syntax to
inset?
" ' " Forms!Form1.PathName" ' " ??

DoCmd.TransferText acExportDelim, , "EXP2-a", Forms!Form1.PathName

 

[NetworkTrade]

aha ...much thanks Mr. V.
--
NTC

Access03; have working button with:
DoCmd.TransferText acExportDelim, , "EXP2-a", "C:\TxtTEST.txt"

EXP2-a is a Aggregate query (sum)

Questions:
1. one field is a date; when query shows 08/01/2008 the txt file is
displaying 39661 when the query shows 07/31/2008 the txt file is
displaying 39660 . It is displaying an absolute value counting from a system
start date I guess. How do I get this as the date format?

Correct analysis! Date/Time values are stored as a count of days and fractions
of a day (times) since midnight, December 30, 1899. For TransferText use a
query with a calculated field explicitly formatting the value:

ExpDate: Format([fieldname], "mm/dd/yyyy")

2. all the fields are 'GroupBy' except for one which is SUM. This one
displays in the query as 48 but in the txt file as 48.00 - - - pretty minor
but can this be changed?....perhaps in the table parameters maybe

Again use the Format.

...just surprised to see the txt file differ from the query result in these
two...

They just do.

3. the Docmd will need ultimately to have dynamic path / file name in the
string based on values in txt boxes in Form1. What is correct syntax to
inset?
" ' " Forms!Form1.PathName" ' " ??

DoCmd.TransferText acExportDelim, , "EXP2-a", Forms!Form1.PathName

 

[ErezM via AccessMonster.com]

hi

1. change the query "EXP2-a" to have the date field set to Format([DateField],
"Short Date") instead of plain [DateField] - this will send the string
representation of the date and not the value (which is, by the way, the
number of days passed since 30/12/1899)

2. again, you need to export CInt([SumField]) or CLng([SumField]) to let the
query know you want whole numbers

3. use DoCmd.TransferText acExportDelim, , "EXP2-a", txtPath & "\" &
txtFileName & ".txt"

if 1 and 2 above cannot be accomplished directly inside the aggregate query,
use a new query on top of it, that just reads all the results from your
original query and formats the 2 problematic fields

good luck
Erez