Export table fields !

[achalrikhi]

Hello Friends,

I had an issue for exporting access table data to a text file format but I found a simple solution now. Like this :

DoCmd.TransferText acExportDelim, "exportdata", "Data", "C:\Users\welcome\Desktop\output.txt", True

this is working very fine. Only issue is that I do not want certain columns from the table and I don't know how to use select statement and where ? for eg.

I want a query like this :

Select Field 1, Field 3, Filed 4 from < TABLE NAME > where SERIES ( Table field ) = "EQ"

Please advice that how and where to put this query to export data along with Do.Cmd statement.


Regards

Achal

 

[Joe4]

Only the Date column have the format like " 6/1/2015 0:00:00 " Can this be as simple as " 6/1/2015 " ?

Use a Calculated field instead, i.e.

MyDate: Format([DateFieldName],"m/d/yyyy")

And I just wanted to know if it is possible to duplicate the first column and paste it in the last column

You can add a field to a query as many times as you like.

change all the headers name in output ?

You can use calculated fields to rename all the headers you like.

For example, let's say that you have a field named "Address" and you want the header of this field to be "HomeAddress". Then, just create a Calculated field like in the first item I posted above like this:

HomeAddress: [Address]

 

[achalrikhi]

Dear Joe,

Its amazing. You are THE MAGICIAN. The issue was very big until you throw light about queries and enlightened me. After some struggling and efforts it worked the way I wanted and the final product was... wow...! Thank you so much Sir.

I wonder if the query can sort the columns and I can pick some specific records based on the field content. I mean for eg. my table has a field named "SERIES". There are various series in that column like "EQ", "BE", "DR", "DZ", "FZ" etc but I want the records only with the "SERIES = EQ & BE " and another column "DATE" has a three dates. Some records has this months date, some of them have next month and some has next to next months date. And I want the records only with current months date.

I am expecting too much right ? Please forgive me.

Up till now, you have helped me in a great manner... by teaching me the new fundamentals of access. This forum is a great place.

Thank you once again

Regards
Achal

 

[Joe4]

You should be able to do all of that with Query Criteria.
Here is a good article to get you started: Access Tips: Query and Filter Criteria

Read through it, and try it out. If you get stuck with anything, post back here.

 

[achalrikhi]

You should be able to do all of that with Query Criteria.
Here is a good article to get you started: Access Tips: Query and Filter Criteria

Read through it, and try it out. If you get stuck with anything, post back here.

Thank you very much Joe,

I am going through it and try out. I will reply back soon.

Regards

Achal

 

[achalrikhi]

Hello,

Just in case I use any thing like " match ", is it necessary to rebuild the older query also ?

Regards
Achal

 

[Joe4]

Just in case I use any thing like " match ", is it necessary to rebuild the older query also ?

I am not sure I understand your question...

 

[achalrikhi]

Just create a query that has only the fields you want to export.
Then, just select that name of the Query in the TransferText command instead of the Table name (you can export queries just as easily as you can export tables).

Dear Joe,

As you said, I made a query and it is working fine, but like I asked, if I want to filter the data on the base of my column "SERIES" which has various series like EQ, BE, DR, etc. I put EQ in my query ( Criteria ) for matching text.

Now the result is great but it does have the column "SERIES", which I do not want in my final result. ( Once the records are sorted and taken as per the criteria, only those records are allowed, without the column "SERIES".

please advice

Regards

Achal

 

[Joe4]

You do not need to Show the fields that you are just using for Criteria. Just uncheck the "Show" box under that field in the Query Builder (its on the row just above where you are placing the Criteria).

 

[achalrikhi]

You do not need to Show the fields that you are just using for Criteria. Just uncheck the "Show" box under that field in the Query Builder (its on the row just above where you are placing the Criteria).

O My God...!

I must slap my self. This worked amazingly fine. Thank you very much Joe. I am just completing the task. I will post it as soon as it is done.

Regards

Achal

 

[achalrikhi]

Dear Joe,

At last, I got the result, the way I wanted, thank you very much once again. All things are working perfectly fine. I have done one more thing. I have added one line after the code to export the csv to text and the line is :
DoCmd.RunSQL "DELETE * FROM < Table name >;" ( as I do not want to store the data to the table for long )
Now if you allow me, I want to ask,

This command is working very fine, but after deleting the content a big window appears asking the user, “ Do you want to delete all the records ? “ Yes / No.

So can we avoid this permission box ?

And as you have rightly suggested about the criteria and calculated fields, This is working absolutely fine. I have a column having three dates, for eg. 25/06/2015, 30/07/2015 and 29/08/2015. All the dates have several thousand records, and I want only the records which is of the latest date i.e. 25/06/2015 amongst the three. If I put the criteria as 25/06/2015, the result is great but then what will happen, when the dates will not have 25/06/2015 and something else like 30/07/2015, 29/08/2015 and 24/09/2015 ?

Is there a way out for this problem ?

And lastly, the CSV file I download everyday is with a different name, I have to change the name of this file as per the name I have put it in the code for importing the CSV file. Can we make the code in such a way that it will take the files name as it is and EXPORT also in the same name in txt format ?
I hope I am not confusing…! These are the doubts and questions in my mind, and I am quite curious to know the solution.

Thanks & Regards

Achal