Received: from smtp.stellison.co.uk ([212.56.66.225]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f03IxL711843 for ; Wed, 3 Jan 2001 13:59:21 -0500 (EST) (envelope-from francis@stellison.co.uk) Received: (qmail 28754 invoked from network); 3 Jan 2001 19:01:04 -0000 Received: from unknown (HELO neo) (unknown) by unknown with SMTP; 3 Jan 2001 19:01:04 -0000 From: "Francis Solomon" To: , , , Subject: RE: [SQL] Numeric and money Date: Wed, 3 Jan 2001 19:00:54 -0000 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) In-reply-To: <01C07575.F26395D0.mdavis@sevainc.com> X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 Importance: Normal X-Archive-Number: 200101/84 X-Sequence-Number: 2250 Hi Michael, > create table tst (id int, amount numeric(9,2)); > insert into tst values (1, 1.10); > insert into tst values (2, 1.00); > insert into tst values (2, 2.00); > select * from tst where amount = 1; -- works > select * from tst where amount = 1.1; -- fails > select * from tst where amount = 1.10; -- fails You could try: SELECT * FROM tst WHERE amount=1.1::numeric; > select amount::varchar from tst; -- fails This is a bit ugly, but it works: SELECT ltrim(to_char(amount, '9999999D99')) FROM tst; > select amount::money from tst; -- fails I'm not quite sure why you need to do this. 'amount' is already 'numeric(9,2)' which is as close as you get to 'money'. If you want to get the result into a var of type 'Currency' in your VB/VBA code (inside Access), can't you just CCur() the field? > select id || ', ' || id from tst; -- works > select id || ', ' || amount from tst; -- fails Again, a bit ugly, but ... SELECT id || ', ' || ltrim(to_char(amount, '9999999D99')) FROM tst; > > >From within Access, I can't update any table with a numeric > data type > because of the "select * from tst where amount = 1.1;" > failure. These > limitations have caused me to wonder what other PostgreSQL > users are using > for their money values? Is numeric(9,2) the best choice for > money? I I am using numeric(9,2) for all my "money" values with VB6 and it works fine. I use a wrapper function that I wrote to "fix up" arguments so that postgres plays nicely with them. I tend to manipulate recordset values with VB/VBA's conversion functions after they're returned, like CCur() as mentioned above. I'm willing to share my wrappers if you'd like them. Hope this helps Francis Solomon