transfertext, format () does NOT work with pipes!?!

[Bob]

running access 2k - all I want to do is a SIMPLE pipe-delimitted
export with short dates!!!

I HAVE a saved export specification that selects a "|" as delimitter.
I AM exporting a query that uses format() to format a short date, and
numbers with NO decimals.

here's the command I'm using:
DoCmd.TransferText acExportDelim, "MyExportSpec", "MyQueryWithFORMAT",
"c:\mytext.txt", True

This command DOES give me a pipe-delimitted file, but:
dates appear as "11/12/2009 0:00" DESPITE the use of: Format
([Q_myq.a_date],"mm/dd/yyyy")
integers appear as "134.00" DESPITE the use of: Format
([Q_myq.a_val],"####")


IF I execute this command:
DoCmd.TransferText acExportDelim, "", "MyQueryWithFORMAT", "c:
\mytext.txt", True

I will get a text file WITH short dates, and numbers with no decimals.
BUT this is a COMMA delimitted file. this is NOT what I want.


Last I checked in my immediate window -
?typename( format(anyvalue,"anyformat") )
this DOES indicate format returns a STRING -
so WHY THE HELL DOES A PIPE-DELIMITTED SPECIFICATION CHANGE THE FORMAT
OF A STRING VALUE!?!?!?!?

and more simply - how the hell do I export a pipe-delimitted file with
the formatting I want?!?!?
and - WHY the hell is this soooooo fricking hard?!?!?!?

I CAN SIMPLY NOT be the only person wanting to do this!!! yet I find
little results in searches for this problem.....

TIA - Bob

 

[James A. Fortune]

running access 2k - all I want to do is a SIMPLE pipe-delimitted
export with short dates!!!

I HAVE a saved export specification that selects a "|" as delimitter.
I AM exporting a query that uses format() to format a short date, and
numbers with NO decimals.

here's the command I'm using:
DoCmd.TransferText acExportDelim, "MyExportSpec", "MyQueryWithFORMAT",
"c:\mytext.txt", True

This command DOES give me a pipe-delimitted file, but:
dates appear as "11/12/2009 0:00" DESPITE the use of: Format
([Q_myq.a_date],"mm/dd/yyyy")
integers appear as "134.00" DESPITE the use of: Format
([Q_myq.a_val],"####")

IF I execute this command:
DoCmd.TransferText acExportDelim, "", "MyQueryWithFORMAT", "c:
\mytext.txt", True

I will get a text file WITH short dates, and numbers with no decimals.
BUT this is a COMMA delimitted file. this is NOT what I want.

Last I checked in my immediate window -
?typename( format(anyvalue,"anyformat") )
this DOES indicate format returns a STRING -
so WHY THE HELL DOES A PIPE-DELIMITTED SPECIFICATION CHANGE THE FORMAT
OF A STRING VALUE!?!?!?!?

and more simply - how the hell do I export a pipe-delimitted file with
the formatting I want?!?!?
and - WHY the hell is this soooooo fricking hard?!?!?!?

I CAN SIMPLY NOT be the only person wanting to do this!!! yet I find
little results in searches for this problem.....

TIA - Bob

Take it easy. You're beginning to sound like one of Jeff Dunham's
puppets . BTW, it was eerie that the night before the Ft. Hood
shootings, an episode aired that featured Achmed the Dead Terrorist
trying to become a U.S. citizen so that he could better infiltrate the
U.S. After failing the citizenship test, he is given a final option
for becoming a U.S. citizen. The final scene shows him in Marine Boot
Camp along with "To be continued..."

If the TransferText method doesn't work for you, you can always use
brute force to loop through a recordset based on the query and use
something like 'Print #intFile, strX' to print a header string (before
the loop) and the data line by line to a text file, appending the pipe
after each field value as the delimeter. You would have total control
over how dates get formatted in the output file.

James A. Fortune
(e-mail address removed)

 

[Bob]

running access 2k - all I want to do is a SIMPLE pipe-delimitted
export with short dates!!!

I HAVE a saved export specification that selects a "|" as delimitter.
I AM exporting a query that uses format() to format a short date, and
numbers with NO decimals.

here's the command I'm using:
DoCmd.TransferText acExportDelim, "MyExportSpec", "MyQueryWithFORMAT",
"c:\mytext.txt", True

This command DOES give me a pipe-delimitted file, but:
dates appear as "11/12/2009 0:00" DESPITE the use of: Format
([Q_myq.a_date],"mm/dd/yyyy")
integers appear as "134.00" DESPITE the use of: Format
([Q_myq.a_val],"####")

IF I execute this command:
DoCmd.TransferText acExportDelim, "", "MyQueryWithFORMAT", "c:
\mytext.txt", True

I will get a text file WITH short dates, and numbers with no decimals.
BUT this is a COMMA delimitted file. this is NOT what I want.

Last I checked in my immediate window -
?typename( format(anyvalue,"anyformat") )
this DOES indicate format returns a STRING -
so WHY THE HELL DOES A PIPE-DELIMITTED SPECIFICATION CHANGE THE FORMAT
OF A STRING VALUE!?!?!?!?

and more simply - how the hell do I export a pipe-delimitted file with
the formatting I want?!?!?
and - WHY the hell is this soooooo fricking hard?!?!?!?

I CAN SIMPLY NOT be the only person wanting to do this!!! yet I find
little results in searches for this problem.....

TIA - Bob

Take it easy. You're beginning to sound like one of Jeff Dunham's
puppets . BTW, it was eerie that the night before the Ft. Hood
shootings, an episode aired that featured Achmed the Dead Terrorist
trying to become a U.S. citizen so that he could better infiltrate the
U.S. After failing the citizenship test, he is given a final option
for becoming a U.S. citizen. The final scene shows him in Marine Boot
Camp along with "To be continued..."

If the TransferText method doesn't work for you, you can always use
brute force to loop through a recordset based on the query and use
something like 'Print #intFile, strX' to print a header string (before
the loop) and the data line by line to a text file, appending the pipe
after each field value as the delimeter. You would have total control
over how dates get formatted in the output file.

James A. Fortune
(e-mail address removed)

hi James -
TYVM for your reply.... yes, as you can tell, I am extremely
frustrated that a very simple task has once again turned into a custom
programming applet under a microsoft product.

I guess I really shouldn't be surprised by now, but yet, I still am.

I find your reference to ft hood, amusing in the highest, because 1 hr
ago, I felt very much like going postal on the ms programmers that
failed to make a simple pipe-delimitted export work properly, and
easily.

At this point, it would seem I have no other choice than to write my
own export function - I appreciate the idea... but I am no less
frustrated, and am ticked off beyond the ability for words to describe
that I should have to take the time to manage this myself, when it
should be a no-brainer using built-in functions.

I apologize for my outburst, but I'm sick and tired of having to do my
own work-arounds for known microsoft bugs, that they simply refuse to
address.

again - I appreciate your reply, as it pulled me from my tunnel
vision / thinking that microsoft products should simply work as
expected. )
TX again, and </sarcasm> :O)
Bob