Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

MS Access and VB Script

Hi Experts,

I have an access database which has two tables “Current_Year” and “Prior_Year”.  I would want a vb code to help me split the table in various text files based on the account number at a selected or given location. So I would end up with two text files one for Current Year table and another for Prior year table and the files should be placed in a folder at the destination given with folder name as the account number.
Thanks a lot in advance.

Regards,
Sai
Test-.mdb
0
Saichand
Asked:
Saichand
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you show what you've attempted so far?

You can "split" a table by using queries. For example:

SELECT * FROM YourTable WHERE Year(YourDateField) = 2010

To do this, Create a new query, select your Table, and add all the fields you need to show in the Text file. You'll need to add a Calculated column to your query as well. To do that, move to a blank "column" in the query grid, and enter this in the "Field" row:

MyYear: Year([YourDateField])

This will create a column named "MyYear". You can then set a criteria on that column:

=2010

This will filter all the data in that table for any value where Year of your Date field = 2010. Save that query, and use it below.

From there, you can use the builtin Export functions of Access to move the data to a Text file. In 2007/2010 you would select the External Data tab - Export group - Text files. This brings up a wizard you can use to export that data to a standard TXT file.
0
 
TinTombStoneCommented:
I have modified Test.mdb to include a form that does what you asked
Give it a go, see what you think

Test-.mdb
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now