Convert memo to numeric.

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

Guest

Hi,

I have a tricky one here. I am trying to generate a report in MS-Access
2003. The problem is that one of the fields I am running my query from has
values that should be stored as numeric stored as memo. So one record will
have a values like:
999999
999999.00
999,999.000
$999,999.00
$999999

They should all be 999,999.00 since this data is supposed to be dollar
amounts. I was thinking of creating a separate make table query to read
through the table and convert all/most of the values to be in the correct
format. I know how to create everything except any code needed to actually
format the data. Does anyone know how to do this?
 
Jim said:
Hi,

I have a tricky one here. I am trying to generate a report in
MS-Access 2003. The problem is that one of the fields I am running
my query from has values that should be stored as numeric stored as
memo.
Click to expand...

Some how that does not sound right. Could you explain?
 
On Mon, 2 Oct 2006 09:53:02 -0700, Jim Moberg <Jim
Hi,

I have a tricky one here. I am trying to generate a report in MS-Access
2003. The problem is that one of the fields I am running my query from has
values that should be stored as numeric stored as memo. So one record will
have a values like:
999999
999999.00
999,999.000
$999,999.00
$999999

They should all be 999,999.00 since this data is supposed to be dollar
amounts. I was thinking of creating a separate make table query to read
through the table and convert all/most of the values to be in the correct
format. I know how to create everything except any code needed to actually
format the data. Does anyone know how to do this?
Click to expand...

I'd suggest trying something very simple first: just create a Query
based on the table (needn't be a maketable query) and use a calculated
field

MoneyValue: CCur([memofield])

Or, add a Currency datatype field to your table and use an Update
query to update it to CCur([memofield]).

John W. Vinson[MVP]
 
John Vinson said:
On Mon, 2 Oct 2006 09:53:02 -0700, Jim Moberg <Jim
Hi,

I have a tricky one here. I am trying to generate a report in MS-Access
2003. The problem is that one of the fields I am running my query from has
values that should be stored as numeric stored as memo. So one record will
have a values like:
999999
999999.00
999,999.000
$999,999.00
$999999

They should all be 999,999.00 since this data is supposed to be dollar
amounts. I was thinking of creating a separate make table query to read
through the table and convert all/most of the values to be in the correct
format. I know how to create everything except any code needed to actually
format the data. Does anyone know how to do this?
Click to expand...

I'd suggest trying something very simple first: just create a Query
based on the table (needn't be a maketable query) and use a calculated
field

MoneyValue: CCur([memofield])

Or, add a Currency datatype field to your table and use an Update
query to update it to CCur([memofield]).

John W. Vinson[MVP]
Click to expand...

I did make a mistake when I originally posted. The line where I wrote "So
one record will have a values like:" should read as "So the field will have
values like:

999999
999999.00
999,999.000
$999,999.00
$999999
 
Jim Moberg said:
John Vinson said:
On Mon, 2 Oct 2006 09:53:02 -0700, Jim Moberg <Jim
Hi,

I have a tricky one here. I am trying to generate a report in MS-Access
2003. The problem is that one of the fields I am running my query from has
values that should be stored as numeric stored as memo. So one record will
have a values like:
999999
999999.00
999,999.000
$999,999.00
$999999

They should all be 999,999.00 since this data is supposed to be dollar
amounts. I was thinking of creating a separate make table query to read
through the table and convert all/most of the values to be in the correct
format. I know how to create everything except any code needed to actually
format the data. Does anyone know how to do this?
Click to expand...

I'd suggest trying something very simple first: just create a Query
based on the table (needn't be a maketable query) and use a calculated
field

MoneyValue: CCur([memofield])

Or, add a Currency datatype field to your table and use an Update
query to update it to CCur([memofield]).

John W. Vinson[MVP]
Click to expand...

I did make a mistake when I originally posted. The line where I wrote "So
one record will have a values like:" should read as "So the field will have
values like:

999999
999999.00
999,999.000
$999,999.00
$999999
Click to expand...
Thanks John. I created the simple query using the CCur function and it
seemed to work pretty well. Not all of the values were converted. They
resulted in an #Error value showing up in the field. Now I'm trying to
figure out just what is stored in the field. Null doesn't seem to be the
value as I put an IIF conditional in the query where the value would be set
to 0.00. The value still came out to be #Error.

When I try to filter the query output for "#Error" I get an error message
stating that the field has an invalid date type entered into it. If I look
at the same record in the table being read from there isn't anything showing.

Any ideas of how to see what is actually stored in these fields?
 
Try evaluating the value in the memo field with something like the following:

IIf(IsNull([memofield]) Or Not IsNumeric([memofield]),0,CCur([memofield]))

If the field is null or it doesn't evaluate to a number, you get a zero,
otherwise it converts the value in the memo field to a currency value.
--
Kevin Backmann


Jim Moberg said:
Jim Moberg said:
John Vinson said:
Hi,

I have a tricky one here. I am trying to generate a report in MS-Access
2003. The problem is that one of the fields I am running my query from has
values that should be stored as numeric stored as memo. So one record will
have a values like:
999999
999999.00
999,999.000
$999,999.00
$999999

They should all be 999,999.00 since this data is supposed to be dollar
amounts. I was thinking of creating a separate make table query to read
through the table and convert all/most of the values to be in the correct
format. I know how to create everything except any code needed to actually
format the data. Does anyone know how to do this?

I'd suggest trying something very simple first: just create a Query
based on the table (needn't be a maketable query) and use a calculated
field

MoneyValue: CCur([memofield])

Or, add a Currency datatype field to your table and use an Update
query to update it to CCur([memofield]).

John W. Vinson[MVP]
Click to expand...

I did make a mistake when I originally posted. The line where I wrote "So
one record will have a values like:" should read as "So the field will have
values like:

999999
999999.00
999,999.000
$999,999.00
$999999
Click to expand...
Thanks John. I created the simple query using the CCur function and it
seemed to work pretty well. Not all of the values were converted. They
resulted in an #Error value showing up in the field. Now I'm trying to
figure out just what is stored in the field. Null doesn't seem to be the
value as I put an IIF conditional in the query where the value would be set
to 0.00. The value still came out to be #Error.

When I try to filter the query output for "#Error" I get an error message
stating that the field has an invalid date type entered into it. If I look
at the same record in the table being read from there isn't anything showing.

Any ideas of how to see what is actually stored in these fields?
Click to expand...
 
When I try to filter the query output for "#Error" I get an error message
stating that the field has an invalid date type entered into it. If I look
at the same record in the table being read from there isn't anything showing.
Click to expand...

If the field is NULL, you will get an error because CCur() cannot
handle null values. You may want to use a criterion of IS NOT NULL on
the field to prevent this error.
Any ideas of how to see what is actually stored in these fields?
Click to expand...

Without seeing the database, no clue - but NULL is the first thing to
check.

How on Earth did this field get defined as memo!?

John W. Vinson[MVP]
 
John said:
How on Earth did this field get defined as memo!?
Click to expand...

Lack of research is usually the reason. MEMO is a tempting
one-size-fits-all; it can take monetary values including the currency
symbol! Simply convert it to CURRENCY and it doesn't take too many
monkeys typing away before you get a value of minus 5 billion in the
column. Then they start asking why values aren't always rounding up...
My favourite is the VNVARCHAR(50) column: Access thinks it should be
fifty, so why change it? And you never know, there may be one day when
I hire an employee with a UNICODE last name...

Jamie.

--
 
How on Earth did this field get defined as memo!?

I have the same question John. This database table is a prime example of
poor database design.
 
Thanks Kevin. This worked pretty well.

Kevin B said:
Try evaluating the value in the memo field with something like the following:

IIf(IsNull([memofield]) Or Not IsNumeric([memofield]),0,CCur([memofield]))

If the field is null or it doesn't evaluate to a number, you get a zero,
otherwise it converts the value in the memo field to a currency value.
--
Kevin Backmann


Jim Moberg said:
Jim Moberg said:
:

Hi,

I have a tricky one here. I am trying to generate a report in MS-Access
2003. The problem is that one of the fields I am running my query from has
values that should be stored as numeric stored as memo. So one record will
have a values like:
999999
999999.00
999,999.000
$999,999.00
$999999

They should all be 999,999.00 since this data is supposed to be dollar
amounts. I was thinking of creating a separate make table query to read
through the table and convert all/most of the values to be in the correct
format. I know how to create everything except any code needed to actually
format the data. Does anyone know how to do this?

I'd suggest trying something very simple first: just create a Query
based on the table (needn't be a maketable query) and use a calculated
field

MoneyValue: CCur([memofield])

Or, add a Currency datatype field to your table and use an Update
query to update it to CCur([memofield]).

John W. Vinson[MVP]


I did make a mistake when I originally posted. The line where I wrote "So
one record will have a values like:" should read as "So the field will have
values like:

999999
999999.00
999,999.000
$999,999.00
$999999
Click to expand...
Thanks John. I created the simple query using the CCur function and it
seemed to work pretty well. Not all of the values were converted. They
resulted in an #Error value showing up in the field. Now I'm trying to
figure out just what is stored in the field. Null doesn't seem to be the
value as I put an IIF conditional in the query where the value would be set
to 0.00. The value still came out to be #Error.

When I try to filter the query output for "#Error" I get an error message
stating that the field has an invalid date type entered into it. If I look
at the same record in the table being read from there isn't anything showing.

Any ideas of how to see what is actually stored in these fields?
Click to expand...
Click to expand...
 
Back
Top