remove apostophe from numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

working with an oracle database... a column of data contains numbers with an
apostrophe. That data is supposed to be dollars. Access will not allow me to
run a query that sums the values becuase incorrect data type. Any suggestions
on how to remove the apostrophe and change the data type to numbers?

Thanks in advance,
 
Try the Val() function, e.g. ...

SELECT Sum(Val([TestText])) AS TheSum
FROM tblTest
WHERE (((tblTest.TestText) Is Not Null));
 
working with an oracle database... a column of data contains numbers with an
apostrophe. That data is supposed to be dollars. Access will not allow me to
run a query that sums the values becuase incorrect data type. Any suggestions
on how to remove the apostrophe and change the data type to numbers?

Thanks in advance,
Click to expand...

Where's the apostrophe? At the beginning, the end, in the middle
somewhere?

With the apostrophe, Access will treat the number as Text. If it's at
the beginning, you can use

CCur(Mid([fieldname], 2))

if it's at the end,

CCur([fieldname])

and if it's in the middle somewhere, post back with an example.

John W. Vinson[MVP]
 
It's in the front. I have to manually go to design view of the table and
change the data type. How do i use the code that code you are suggesting?
(I'm a novice ACCESS user) :)

John Vinson said:
working with an oracle database... a column of data contains numbers with an
apostrophe. That data is supposed to be dollars. Access will not allow me to
run a query that sums the values becuase incorrect data type. Any suggestions
on how to remove the apostrophe and change the data type to numbers?

Thanks in advance,
Click to expand...

Where's the apostrophe? At the beginning, the end, in the middle
somewhere?

With the apostrophe, Access will treat the number as Text. If it's at
the beginning, you can use

CCur(Mid([fieldname], 2))

if it's at the end,

CCur([fieldname])

and if it's in the middle somewhere, post back with an example.

John W. Vinson[MVP]
Click to expand...
 
It's in the front. I have to manually go to design view of the table and
change the data type. How do i use the code that code you are suggesting?
(I'm a novice ACCESS user) :)
Click to expand...

Put the expression in a vacant Field cell in a Query:

Amount: CCur(Mid([fieldname], 2))

You can then sumit (or whatever you like, treating it as a currency
value).


John W. Vinson[MVP]
 
Back
Top