Copy and paste from Access

[Bama Gambler]

I want to copy and paste from an access query into excel. Ok, no problem, except when I paste it rounds the numbers to two decimal places. How can I turn this off? I tried pasting as text. I tried a regular (ctrl-v) paste.

[thing]

I haven't had this happen, but a couple of questions:

Is the Access query displaying more than 2 decimals?

Have you tried paste:special:value?

[Bama Gambler]

Is the Access query displaying more than 2 decimals?yes, 6 decimals (fixed)

Have you tried paste:special:value?yes

Also note if I copy one value at a time it does all the decimal places. If I try to copy a record (or all records) then it only pastes two decimal places. Very strange.

[erosewater]

Is the Access query displaying more than 2 decimals?yes, 6 decimals (fixed)

Have you tried paste:special:value?yes

Also note if I copy one value at a time it does all the decimal places. If I try to copy a record (or all records) then it only pastes two decimal places. Very strange.

Try pre-formatting the cells (number with 6 decimals) where you want to copy the data and pasting as text.

[Bama Gambler]

Try pre-formatting the cells (number with 6 decimals) where you want to copy the data and pasting as text.No dice.

I finally got it to work. Edit, Paste Special, Biff5.

[Chuck]

I've found that there is some limit to the amount of data you copy and paste from Access to Excel at which point you lose the decimals. I haven't narrowed down what that limit is. But I do one of two things...

Copy and paste in smaller chunks.

Use the Data-Get External Data menu option which does not seem to have the same limitation.

Chuck

[Sunny]

I've found that there is some limit to the amount of data you copy and paste from Access to Excel at which point you lose the decimals. I haven't narrowed down what that limit is. But I do one of two things...

Copy and paste in smaller chunks.

Use the Data-Get External Data menu option which does not seem to have the same limitation.

Chuck

There is a difference between having too many data lines to paste into Excel and losing your decimals. I just finished a project that had the issue of preserving the decimals. Make sure you go into the tables and change the type to Numbers, General Numbers, and as many decimal places as you need. That should preserve the decimals no problem.

Unless I'm missing your question here.

[Sunny]

Try pre-formatting the cells (number with 6 decimals) where you want to copy the data and pasting as text.No dice.

I finally got it to work. Edit, Paste Special, Biff5.

Make sur eyou do GENERAL NUMBERS right below the NUMBERS. It's extremely important if you want to preserve all the decimals.

And export it as excel should do.

[Chuck]

There is a difference between having too many data lines to paste into Excel and losing your decimals. I just finished a project that had the issue of preserving the decimals. Make sure you go into the tables and change the type to Numbers, General Numbers, and as many decimal places as you need. That should preserve the decimals no problem.

Unless I'm missing your question here.

I didn't think I asked a question. But I'm not talking about the limitation of lines of data you can paste into Excel. There is some other limitation as to the *volume* of data that can be pasted that causes lost decimals. And in my experience, it hasn't had anything directly to do with how the format of the numbers was set in either Access of Excel. For example, I've had situations where I copied and pasted an entire table of, say, 20,000 records where the decimals were lost. But if I copy and paste one column at a time, the decimals are not lost. It's not an issue of how the numbers are *displayed*, but an actual loss of precision due to the volume being copied and pasted.

Chuck

[Sunny]

There is a difference between having too many data lines to paste into Excel and losing your decimals. I just finished a project that had the issue of preserving the decimals. Make sure you go into the tables and change the type to Numbers, General Numbers, and as many decimal places as you need. That should preserve the decimals no problem.

Unless I'm missing your question here.

I didn't think I asked a question. But I'm not talking about the limitation of lines of data you can paste into Excel. There is some other limitation as to the *volume* of data that can be pasted that causes lost decimals. And in my experience, it hasn't had anything directly to do with how the format of the numbers was set in either Access of Excel. For example, I've had situations where I copied and pasted an entire table of, say, 20,000 records where the decimals were lost. But if I copy and paste one column at a time, the decimals are not lost. It's not an issue of how the numbers are *displayed*, but an actual loss of precision due to the volume being copied and pasted.

Chuck

When you change the type and specify it, it does not just change the display, it actually changes the data.

[Dr T Non-Fan]

Try pre-formatting the cells (number with 6 decimals) where you want to copy the data and pasting as text.No dice.

I finally got it to work. Edit, Paste Special, Biff5.

Make sur eyou do GENERAL NUMBERS right below the NUMBERS. It's extremely important if you want to preserve all the decimals.

And export it as excel should do.

Before copying, change the "STYLE" to general. On a new sheet, the style is defaulted to something, and it might not be what you want it to be.

NOTE: Might not work.

[Chuck]

When you change the type and specify it, it does not just change the display, it actually changes the data.

I don't think so. I just checked. Create a double precision field in a table. Type in 3 decimal places. Then go change the format to fixed with 2 decimal places. Edit the field and you'll still see the original 3 places. Go back to change the fixed to 3 places again. The 3 decimals are retained. Now I don't do calcs in Access, so I don't know what gets used there.

But it still has nothing to do with the problem anyway, because when you copy and paste too much data, you only get 2 places regardless of how many you have the field set for.

At least that's what happens on my machine.

Chuck

[Brad Gile]

When you change the type and specify it, it does not just change the display, it actually changes the data.

I don't think so. I just checked. Create a double precision field in a table. Type in 3 decimal places. Then go change the format to fixed with 2 decimal places. Edit the field and you'll still see the original 3 places. Go back to change the fixed to 3 places again. The 3 decimals are retained. Now I don't do calcs in Access, so I don't know what gets used there.

But it still has nothing to do with the problem anyway, because when you copy and paste too much data, you only get 2 places regardless of how many you have the field set for.

At least that's what happens on my machine.

Chuck

Double Precision is a data type, but "2 decimal places" is not. Change the data type from Double Precision to Integer and see what happens.
I don't use Access, so I don't really know what happens, but I would expect Supersonic to be correct. :)



Brad

[Chuck]

Double Precision is a data type, but "2 decimal places" is not. Change the data type from Double Precision to Integer and see what happens.
I don't use Access, so I don't really know what happens, but I would expect Supersonic to be correct. :)


True, but I was originally responding to:

Make sure you go into the tables and change the type to Numbers, General Numbers, and as many decimal places as you need. That should preserve the decimals no problem.

I may be confused as to whether that was referring to Access or Excel. I assumed Access because of the reference to "tables", but I see now it makes more sense referring to Excel.

But either way, there is a copy/paste problem that goes beyond either the type or the formatting.

Chuck

[Bama Gambler]

But either way, there is a copy/paste problem that goes beyond either the type or the formatting.

ChuckCorrect. Use paste-special, Biff5.

[Chuck]

But either way, there is a copy/paste problem that goes beyond either the type or the formatting.

ChuckCorrect. Use paste-special, Biff5.

Hmmm. That's not working for me (whoever Biff was!). I get an "Excel can't paste error".

Upon more experimentation, I've found my problem to be the Fixed format for numbers in Access. If I create a table of 20,000 records with a single numeric field with the format set to Fixed with six decimal places, the 20,000 records lose precision down to two places when pasted into Excel. 10,000 records paste fine though.

But if I remove the fixed format (and set format to general with the decimals set to auto), I can paste many more records (I don't know if there is a point where this will be a problem too - I tried 65,000 records with three fields and it still works.)

In any case, "Data-Get External Data" in Excel works for me in all situations (so far).

Chuck

[Sunny]

sorry Chuck, haven't been checking back so missed your subsequent posts. I guess you've solved your problem?

[Chuck]

sorry Chuck, haven't been checking back so missed your subsequent posts. I guess you've solved your problem?

Yes...I've just discussing the alternative solutions.