Formatting in MS Access

Question

I am trying to format a Access table fields to include all values as in the images below. However when I copy and paste from excel to the Access table, only significant figures appear in the table. The fields are currently of Text Datatype, I have tried Number Datatype but the values get rounded up. How do I correct this to accomplish this task? Is there a SQL query I can run to return the expected formatting?

Currently if I copy and paste from excel to Access:

Expected:

Perhaps If I leave the fields as Text Datatype, would the Format() method using the approach below sort it out:

SELECT Format(ExchangeRate, "#.######"), Format(ForeignGross, "#.##")  
FROM tab1; 

Answer 1

In Access, set the Format property for those two fields, respectively:

0.000000
0.00

Never store amounts or quantities as text. Change Field Data Type to Number and use Field Size: Double and Format: 0.000000 and 0.00 respectively.