Export to Comma Delimited Text (1 Viewer)

[Cindy_B]

I am having problems exporting a query to a text file. I am not using any code or macros so far:
Users enter info on a form (combination of text, numbers and date)
Then there is a query that I need exported to a comma delimited text file.
There are problems:
1)The Short Date is showing up in the text file with the 0:00:00 time that CANNOT be there;
2)There MUST be quotes around the date and amounts, not just the text fields;
3)Amounts are showing up with "$" sign if I use Currency format and either with extra decimal places or no decimal places if I use Standard format.

This comma delimited text file will be used by an external source to update to their database. The programmer receiving this file has specified that the text file MUST BE comma delimited.

For example, I am getting this:
12/9/2002 0:00:00,"22222","200204",$50.50,$66.01,$66.01,$50.55,$50.55,$50.55,$50.55,$50.55,$50.55,$50.55,$0.00,$536.30,$0.00,"123456789"

When I need:
"12/9/2002","22222","200204","50.50","66.01","66.01","50.55","50.55",...,"123456789"

Thanks for any help!

 

[crosmill]

Create a query with the data you want and the format you want it in.

Create a macro that in the action filed set "transfers text" (last on the list) in the action Arguments at the bottom, set to export delimited, give the file a name of "yourName.csv".

Table name should be the name of your query.

Put a button on a form, select miscilanious action "Run Macro"

That should do the trick!

Post back if get stuck

 

[Pat Hartman]

You'll need to convert the numeric data to strings in the query to get quotes around them in the output file.

Select Format(YourDate,"mm/dd/yyyy") As OutDate, CStr(Format(YourNumField,"########.00")) As OutNum, etc.

Then export the query using the TransferText Method.


Don't use macros. If you don't know how to code something, create a macro and then convert the macro to code using the menu option.

 

[Cindy_B]

Thanks for the comments but here's what's happening now:

crosmil-As for the *.csv file, this is exporting it to an Excel spreadsheet.

Pat-Concerning converting the numeric data to strings, I understand and that makes sense to get the quotes, but bear with me here, I am not an expert at this. I know what you're saying because I do some programming, but WHERE do I convert the data in the query?

It's been a few years since I've worked with Access in this way so it will take me a few days to get back in the flow of things!

Thanks again and let me know!

 

[crosmill]

If the file is saved as .csv then it is, you've just got your system (default) setup so that excel opens it. Right CLick Open With notepad, to check it is true csv.

Where you select fields for your query, right click build and put the expresion in there, you'll need to put your field names in [].

Hope this helps!!

 

[Cindy_B]

OK this helps...I put the expressions in the select field and built the expressions. For example, I used:
Cstr(Format(MyNumField, "########.00))
to convert the currency to text, is this correct?
Anyway, then when I tried to run TransfrerText, I get the message "the MS jet database could not find the object 'myfile.txt'" that I have specified as the file name.
Also, just running the query looks good, but the manual Export selection is grayed out and I can't select it.
If I go into the query and change the expressions back to just selecting the fields, I can run TransferText but I'm back to where I started with no quotes around the numbers and date.

Hope I explained what's happening so you can understand my problem

 

[Cindy_B]

Hey I figured out that I was using an old specification name. Looks like it works now!!! THANKS...I'm sure you'll see more ?'s from me in the days to come!

 

[crosmill]

Glad to have been of service.