DoCmd.TransferText acExportDelim

[Moxioron]

Hello all.

Having issues exporting a query to a delim text file.

DoCmd.TransferText acExportDelim, "", "qry_VISA Statement Final Index", "R:\DEPT-BR\CONSUMER LENDING\VISA\VISA Statements\VISA Final Index.txt", False

The file itself exports just fine. However, in the query I have a prompt for a date and when I use 02/01/2012 the result in the text file is:

30 00 32 00 2F 00 30 00 31 00 2F 00 32 00 30 00 31 00 33 00

In addition, anything that is not formatted as a number has quotes.

Two questions:

1. How do I get my date to actually export?
2. How do I get rid of the quotes?

Thanks for your help!

 

[mdlueck]

Please copy/paste the SQL contents of your query, and be sure to wrap the SQL within code tags via the [#] button on the message editor button bar.

Quotes within the CSV data? If so, you don't. And at least in Office 2007, MS is inconsistent (between Access / Excel at least) in CSV exports including / not including quotes. I had to roll my own CSV import VBA code, which processes the file character by character, in order to accommodate the CSV data inconsistency.

 

[Moxioron]

SELECT [tbl_Card Holder Journal Report_Update].[Miscellaneous Third Text], [qry_VISA Statement Index from PSCU Conversion].CardWithDashes, [Primary First Name] & " " & [Primary Middle Name] & " " & [Primary Last Name] AS FullName, [Cycle Date] AS CycleDate, "M:\" & "" & [Field2] AS [Drive&FileName]
FROM [qry_VISA Statement Index from PSCU Conversion] LEFT JOIN [tbl_Card Holder Journal Report_Update] ON [qry_VISA Statement Index from PSCU Conversion].CardNumber = [tbl_Card Holder Journal Report_Update].[Account Identifier];

 

[mdlueck]

However, in the query I have a prompt for a date and when I use 02/01/2012 the result in the text file is:

30 00 32 00 2F 00 30 00 31 00 2F 00 32 00 30 00 31 00 33 00

I formatted the query you kindly provided, as follows:

SELECT [tbl_Card Holder Journal Report_Update].[Miscellaneous Third Text],
       [qry_VISA Statement Index from PSCU Conversion].CardWithDashes,
       [Primary First Name] & " " & [Primary Middle Name] & " " & [Primary Last Name] AS FullName,
       [Cycle Date] AS CycleDate,
       "M:\" & "" & [Field2] AS [Drive&FileName]
FROM [qry_VISA Statement Index from PSCU Conversion]
LEFT JOIN [tbl_Card Holder Journal Report_Update] ON [qry_VISA Statement Index from PSCU Conversion].CardNumber = [tbl_Card Holder Journal Report_Update].[Account Identifier];

Same query, just added white space for readability.

I see no WHERE clause, nothing that appears it is querying for a specific date, nothing that looks like it would prompt for input of a date. Thus I am puzzled at what you wrote.

Perhaps it is not the query itself that is prompting for the input of the date, but some other VBA code which prompts prior to running the query?

Still, without a WHERE clause, the query will SELECT all rows, not considering a certain date.

hhhhmmmm......... Input from others please.

 

[Moxioron]

First off, thank you for taking time to look and respond to my issue.

[Cycle Date] is the prompt for the date. I need the cycle date, which is not in the table that the query is using, in the final text file (we use the information in an index upload).

Hope that helps.

 

[mdlueck]

[Cycle Date] is the prompt for the date.

If so, that will merely pass straight through coming out as column name CycleDate... the date value will be selected for display in the result set.

You need to add a WHERE clause, state that the date column in the table must be such-n-such date. Example:

SELECT LastName, Salary
FROM Employees
WHERE Salary > 21000;

Only you need to variable the 21000 so that the date promted for ends up in the WHERE CLAUSE.

 

[boblarson]

The file itself exports just fine. However, in the query I have a prompt for a date and when I use 02/01/2012 the result in the text file is:

30 00 32 00 2F 00 30 00 31 00 2F 00 32 00 30 00 31 00 33 00

In addition, anything that is not formatted as a number has quotes.


Two questions:

1. How do I get my date to actually export?
2. How do I get rid of the quotes?

You need to create an Export Specification. Which version of Access are you using?

 

[Moxioron]

I am using Access 2010.

I am a little confused about using Where. I generally only use where when I don't want the result displayed....betwee[start date]and[end date].

In this case I want the [Cycle Date] as part of the output. This is what I want my output to look like:

1234567,1234-5678-9012-3456,Fred Flinstone,02/01/2013,M:\1234567890123456_020113.pdf

Thanks for all of the responses.

 

[boblarson]

I'm not sure you do need the Where clause. If you want all of the data output but have the user put in what the CycleDate is, you should probably use a form for that and then refer to it in the query.

But again, you need to create an EXPORT SPECIFICATION to make sure it outputs what you want.
http://www.btabdevelopment.com/ts/2010ExpSpec

But one thing you need to understand is that, when you refer to the Export Specification in the code, you need to use the specification ID from the MSysIMEX table and not the name you will give it when creating the spec.