Solved

MS Access and VB Script

Posted on 2011-02-21
2
254 Views
Last Modified: 2012-08-13
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
Comment
Question by:Saichand
2 Comments
 
LVL 84
ID: 34945978
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
 
LVL 6

Accepted Solution

by:
TinTombStone earned 500 total points
ID: 34946247
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question