0

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?

webworm
  • 10,587
  • 33
  • 120
  • 217

2 Answers2

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.)

Beth
  • 9,531
  • 1
  • 24
  • 43
0

You can use the money data type in SQL Server.

bobs
  • 21,844
  • 12
  • 67
  • 78
  • 3
    see my take on that http://stackoverflow.com/questions/582797/should-you-choose-the-money-or-decimalx-y-datatypes-in-sql-server/ – 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
  • 1
    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
  • 1
    @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
  • @David-W-Fenton I find SQLMenace to be unfailingly reliable in these topics. – Fionnuala Aug 25 '10 at 22:31