[AccessD] Text to Number Conversion Problem

Gustav:

Here's the item in the query that's barfing:

Incoming: Val(nz([ExpectedAvailableAmt]))-nz([orgamt])

When org amount is zero and ExpectedAvailableAmt = 39,000 it will show 39 on
the report.

I tried the Replace function but got a data type mismatch error:

Incoming: Val(Replace(",","",([ExpectedAvailableAmt])))-nz([orgamt])

BTW orgamt is a numeric field but can be null.  Clues?

TIA

Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin at
Beach Access Software
Sent: Monday, November 10, 2008 6:32 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Text to Number Conversion Problem

Gustav:

Well, I would need Cdbl because there could be a fractional amount in that
field.  And actually, even though it works in the test query I made it
doesn't work in the report's query.  So I made a backup of the beck end,
converted the field to double, and the fields with the comma converted
correctly.  But the query for the report still doesn't work after the
conversion.  I get a data type mismatch. Any clue as to why?

TIA 


Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, November 10, 2008 6:06 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Text to Number Conversion Problem

Hi Rocky

Wouldn't CLng() convert that correctly?
Or use Replace to strip the comma.

/gustav

>>> rockysmolin at bchacc.com 10-11-2008 14:56 >>>
Dear List:

Legacy system.  The previous designed defined a numeric field as text.
Don't know why. BUT...

When the field contains a comma it doesn't convert right when converted to
numeric (for use on a report in this case).

IOW, the user enter 39000 and everything's OK.  The user enters 39,000 and
the report says 39. Drops everything after the comma.

The right solution, of course, is to convert the back end to numeric.  And I
may urge him to do this.  Have to convert the data as well making sure that
39,000 converts to 39000 and not 39.

Nevertheless, short of doing the 'right' thing, is there a way to get the
text 39,000 to convert to the numeric 39000 instead of 39?

MTIA



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com