Importing Data for use in report

[Guest]

I have created a Report based on Data that is exported out of another program
in .TXT format.
After I import the data as a table I set the report control source to match
& print out report using imported data.
All works great, except, when the data is imported it set's all fields to
txt fields in Table. In my report there are 3 currency fields, but I cannot
choose a format type of currency unless I change each field in the table to
be a Number field....
Is there an easier way to display $1,000.00 instead of 1000 ??? Using my
field as a .TXT ????
PLEASE HELP

 

[Guest]

Use the format property of the text box where you want to display your
currency to format the display.

 

[Guest]

The format property of the Text Box only seems to work if I change the
field's in the table from .TXT to Number....
Is there any other way to change the format in the report if the field is a
..TXT ????

Thanks for helping Klatuu

 

[Guest]

How are you doing the import? by hand, in VBA, in a Macro?
Are you importing to a new table or an existing table?

 

[Guest]

Thanks for your help.....
I am importing by Hand ?? I think....I right click on the table, go to
Import & create a new table using the .TXT file, the only option I check is
"First Row Contains Headers". The program that I am exporting from only
exports records in .TXT format. I always export the same fields, but the
data is different for every export...Might there be a better way to
accomplish this ? I have different people in my office each using this
method, so, I would love it if I could simplify it.....

 

[Guest]

I would create a table with the correct fields, field names, and data types.
I would create an import specification for the import to pull the data in
correclty.
Then to import the file, I would use the TransferText method in VBA (a Macro
will do if you don't know VBA) and transfer into the existing table rather
than creating a new table. This way, you will have the correct data types.

 

[Guest]

THANKS,
I like the idea, not exactly sure how to do it, But I think that sounds
really good, I will figure it out !!!!
Thanks for the help

 

[Guest]

Okay, post back if you get stuck in the mud.

THANKS,
I like the idea, not exactly sure how to do it, But I think that sounds
really good, I will figure it out !!!!
Thanks for the help

 

[Guest]

Klatuu, Hope your still out there .....
Well, I used the Transfer Text under Macro ??? That works perfectly, I
set it to open my Test.Txt file, then added open Report to the Macro, works
pretty good, opens the report using my sample Test.txt.
Now, I want to modify the Macro to Prompt me for the Data file, that way I
can choose different Data files without going into the design of the MAcro
every time I would like to open a differant data file. Additionally, I want
to modify it so that after it opens the specific Data File, it opens the
report & automatically changes the Record Source to whatever Data file I
choose to import ?? Can this be Done ?? I would think so, but I cannot
figure it out !!!
Thanks For Your Help !!!!!!!!!

Aaron

 

[Guest]

This will take some VBA, but still doable in a Macro.
You can run a VBA function from a Macro. If you go to this site, you will
see a Windows API that will present the common Open/Save file dialog. It
incudes a function called GetOpenFile. It will take a little modification,
because it is only a demo for how to use the API, but not much and I can help
with it.

First, copy the code at the site and paste it into a new module. Save the
module. Mine is named modCommonDialog. Now you will have to make some
changes. Once you have the code downloaded, let me know and I will post back
with the changes you need.

Then to use it, in the FileName box of the TransferText action in your
macro, you use it like this:

=GetOpenFile()

It will popup a dialog and allow you to navigate to the file. The file nane
you select will be used in the TransferText.

http://www.mvps.org/access/api/api0001.htm

 

[Guest]

Klatuu,

You Rock !!! Thanks for all of your help !!!
I opened that link & copied from right below the ****Code Start***** until
the bottom, End Function ??? It appears there are notes written in green
with ' before them ??? Will those affect this code ?? and, am I even
copying the correct Code ??? I think so ??? I saved the module as
modCommonDialog on my end....
Really appreciate your help....

Aaron

 

[Guest]

The lines in green with ' at the beginning are comments. They have no
effect on the code. Comments are good things.

As long as you don't see any lines in red, you should be good to go.

I sort of got lost in the thread, but if you can remind me, I can post a
version of the GetOpenFile function that will work for you.

Are you importing or exporting?
What is the file extension, or does it matter?
The only reason to specify a file extension is to make it easier for the
user to find a file in a directory.

 

[Guest]

Klatuu,

Not a problem, I do tend to ramble on sometimes....
I am importing .TXT files....and for this particular database they will
always be .TXT's... Once the .TXT is imported I have a report that is based
on the fields of the TXT to print out my info.... Currently after the Data is
imported I open the report in Design and change the record source to the name
of the newly imported file, the fields are always the same. In all of this
can I include something that will update that report to use whatever file is
choosen to import ??? Or, is that leading to a whole new problem ??? Also,
nothing showed red in the Code, so, I think I have it !!!

Aaron

 

[Guest]

Okay. Now, to modify the GetOpenFile code modified.
Open the modCommonDialog
Look for this line
"Access (*.mdb)", "*.MDB;*.MDA")
Change it to this
"Text (*.txt)", "*.txt")

Now you are good to go.

As to the name for the reports. A report record source is based on the name
of a table or query.

As I understand it, you now have a Macro (or will with the additional info
above) that will import the table. One of the things you have to give the
TransferText action is a table name. Since it is in the Macro, it should be
the same name all the time. The table name doesn't have specific
relationship to the file name imported, so the table name in your macro
should be the name you use in the report.

One additional thing you are going to need to do. In the macro you use to
import the text file, and just before you do the TransferText action, you
need to delete all the data in the imported table (the same table name as in
you TransferText). The reason is that if you import into an existing table,
it will append the import to the existing data.

This is simple enough. Use the RunSQL action. The code you will want to
put in is:

DELETE * FROM YourTableNameHere

 

[Guest]

Klatuu,
It Works !!!!
I get a wierd Prompt though, says "Enter Parameter" ID ?? I think it is
talking about the Primary Key ??? I do not really need a primary key for
this ??? I can just click OK & the report opens fine, so, not a big deal...I
think it has something to do with the import ???
Anyway, so far I have only run my Test File, & the report control source is
already set to that, I am going to attempt some real trials of different
file names & see if I can get any bugs worked out.....

THANK YOU VERY MUCH FOR YOUR HELP !!!!
Not sure I could have figured this all out without a little help...or, a lot
of help

Aaron

 

[Guest]

Is there a query involved in this?
Is there a field in the table named ID?
Is the report, perhaps, looking for something named ID.

What you are seeing most often happens when an unidentified criteria has
been detected in a query, but I have also seen it happen when a report is
looking for something it can't find.

 

[Guest]

You were exactly right...I'm not using a Query, Didn't seem necessary for
this....Anyway, I used a copy of an excisting report that Did use an ID
field... I removed it from my current report, But, I did not think about
the sorting & grouping...one of the Sorting levels was ID, with that
removed, no more ID msg. pop up !!!!!

Aaron

 

[Guest]

Glad you got it working.

You were exactly right...I'm not using a Query, Didn't seem necessary for
this....Anyway, I used a copy of an excisting report that Did use an ID
field... I removed it from my current report, But, I did not think about
the sorting & grouping...one of the Sorting levels was ID, with that
removed, no more ID msg. pop up !!!!!

Aaron

 

[Guest]

THANKS AGAIN !!!!!!!!!!

Glad you got it working.

 

[Guest]

KLATUU

So Sorry, one more thing & I think I'm DONE....
I put in the Run SQL as the first action in my macro, next is Transfer
Text, Followed by OpenReport.
In the SGL Statement I put DELETE * FROM MTG TABLE, MTG TABLE being the
Table name...When I run the MAcro it say's Syntax Error ????
Any Idea ???

Aaron