Link Excel Data to append ACCESS table

[MNNovice]

Denburt:

any field that has all numeric numbers in a field in Excel will need to have that formatted specifically to text if indeed the database has it stored as text for the corresponding table,

1. Are you referring to the fields in the Excel file? For example, Account (in Excel file) need to be changed to be formatted as text and NOT number?
2.

once the format is changed you may need to manually re-input the data (I did)

Manually re-input which data and where? All data in the Excel file?

I am wondering if it would be lot simpler for the end user to re-input data using sfrmPayroll on fromEchoEnter.

Thanks.

[Denburt]

Denburt:

1. Are you referring to the fields in the Excel file? For example, Account (in Excel file) need to be changed to be formatted as text and NOT number?
2. Manually re-input which data and where? All data in the Excel file?

I am wondering if it would be lot simpler for the end user to re-input data using sfrmPayroll on fromEchoEnter.

Thanks.

Yes MS Access looks at the Excel file and any fields that appear to be numbers get designated as such even though their related part in MS Access is a text field hence the errors. So if a related field in MS Access is designated as text you should format the related field in Excel as text.

I merely had several records in an Excel file for testing once I changed the formats for several Excel fields I had to re input those few records for the change to take effect. When you get your data as you say you do on a regular basis it may or may not be an issue, we can always deal with on a later basis.

[Denburt]

ok I took a look at your Excel file and ran a few more tests and this is what I have found. In Excel have you seen the green box in the top left of a cell that tells you this number is stored as text (look at your sheet then highlight a field that has a number hit f2 then enter it should show up)? Well as long as I see that in the Excel file then MS access has no problem treating this as a text field however when I don't see that then MS Access treats it as a number. once I made that adjustment it worked quite well. When they do a data dump I am not sure how it will handle this so that is what you need to look for and it should be easy to create a macro in Excel to handle this if it is needed. The only other issue I see is if they decide to add an EchoNo or another item from one of your foreign tables then you will not update those records until it is added in the appropriate table such as EchoID. If you are not sure how MS Access is treating your Linked table you can view it in design view and see if the fields you need are being treated as text or number. I hope this helps.

[NeoPa]

It is a very common problem when dealing with record type data in Excel (Importing / exporting etc) that the type of the numeric data (not the format, but the type the data is stored as) causes problems that are not obvious to spot.

This is so common it probably accounts for more than half of the problems I ever see in Excel.

To convert from one type to the other put a formula in one column to produce the results you want, then copy this column. Use Edit / Paste Special / Values over the original column to adjust the data permanently. The new column is no longer required after this point.

[NeoPa]

To convert from string to numeric is simple enough.

=Val(A1)

To convert the other way is less so. A simple conversion is :

=Text(A1,"General")

A more common requirement though, is to require the string equivalent to be at least X number of characters. If we take X=6 then this is often done as :

=Text(A1,"000000")

PS. I should add that to get the formula into the whole column simply copy the top cell and, after selecting all cells in the range, paste it back in.

[Denburt]

Thanks for the tidbits NeoPa, those things are good to know. I don't usually do to much in Excel or I try not to :) But I find myself clunking around in it from time to time. Usually sorting out or cleaning up formulas and such that others have written which can be very intersting at times.

[MNNovice]

Denburt / NeoPa

All day yesterday I struggled through this issue so much so that I got totally frustrated and decided not to pursue at this time.

As an alternate option, I chose to simply key in the payroll data. To my pleasant surprise it was not that big. However, I would like to learn the other way where I don't have to type in this info. May be when I have more time to deal with it.

I kept these notes in a file and shall look it over and give it another try at a later date. Many thanks for your help. Please see my other posting on "combining reports".

Thanks & regards.

[NeoPa]

You're more than welcome Den.

I play there quite a lot, and also have a few users who often get stuck on that particular problem.

[NeoPa]

Please see my other posting on "combining reports".

I'd be happy to go there if you provide a link.

[MNNovice]

NeoPa:

Here is the link to that posting. Thanks.

http://bytes.com/topic/access/answers/869642-how-do-i-combine-reports