Take the 2-minute tour ×

I have an Access 2003 front-end that has data stored in a SQL Server backend database. I want to store monetary values (i.e. $1,3456.25) but I am not sure which datatype to use when going between SQL Server and Access. On the SQL Server side I figured Decimal (18,2) would be good to use as I only need a scale of two (two places to the right of the decimal). However, MS-Access does not have the decimal value type so I am unsure which data type to use in VBA when pulling data from the SQL Server. Should I be using a different datatype in SQL Server?

share|improve this question

2 Answers 2

MS Access has a currency data type, you can use that. Access and SQL Server should be precise enough for you, but if you run in to problems, you can always use a scaled integer (multiply by 100 to store whole number of pennies.)

share|improve this answer
+ 1 Good point about using a scaled integer. I had not thought of that. –  webworm Aug 25 '10 at 18:26

You can use the money data type in SQL Server.

share|improve this answer
see my take on that stackoverflow.com/questions/582797/… –  SQLMenace Aug 25 '10 at 18:09
If you used the "money" datatype in SQL Server what would the corresponding data type in Access/VBA be? –  webworm Aug 25 '10 at 18:24
SQL Server money data type will correspond to the Access Currency data type. –  bobs Aug 25 '10 at 18:41
I find @SQLMenace's cited article on SQL Server money type to be incredibly unenlightening -- lots of dogma and very little consideration of reality. I'm glad most SO users ignore the MS-ACCESS tag, since we generally avoid that kind of crap. –  David-W-Fenton Aug 25 '10 at 20:01
@David-W-Fenton, SQLMenace we just trying to demonstrate that the money datatype in SQL Server is not very reliable when calculations are used with it. –  DForck42 Aug 25 '10 at 21:42

Your Answer


