How Do I Change Data Type

[LeadTechIG]

Hello all,

After years of working in my databases, I noticed that the programmer that originally built them designed the Data Type for the fields designated for "currency" as "text" instead. When I tried to change the data type, I got an error that reads "Microsoft Office Access can't change the data type. There isn't enough disk space or memory."

How do I get around this stumbling block? I would like greatly to change those data types to "currency".

Any help would be much appreciated.

[June7]

Could try creating a new field in the table, number or currency type (I would use number). Run an UPDATE query to populate the new field with data from the old field. Delete old field. Rename new field.

[LeadTechIG]

Thank you for your response.

I have attempted this since receiving your idea, and it isn't working - however, I'm quite confident that the problem could be user error on my end.

I created an update query with:

Field: OldField
Table: CorrectTable
Update To: NewField
Criteria: [Blank]
of: [Blank]

When I click "RUN" nothing happens. Do you have any suggestions?

[June7]

Post the exact SQL statement of the UPDATE action query. I think you have the fields backwards.

[LeadTechIG]

How does this look?

UPDATE [ASSET DATABASE] SET [ASSET DATABASE].[NEW FEE] = "OLD FEE";

[June7]

Try:

UPDATE [ASSET DATABASE] SET [NEW FEE] = Val([OLD FEE]);

[LeadTechIG]

Thank you. Your suggestions moved me in the right direction. However, the NEW FEE field is populated by "$0.00" instead of the values in the OLD FEE field.

[rpeare]

how is the [old fee] value actually stored does it appear as a currency string $5.00 for 5 dollars? if so you may need to strip the $ mark before using the VAL function

here's an example:

testcurrency	VALconversion
$5.00	0
$ 10.00	0
10	10
A10	0

notice the only one that got converted correctly was the one with nothing but a valid numeric value.

if your data is consistently in the $XX.XX format where there is no space and no other alpha characters in the data you could still use a modified version of june's suggestion:

val(right([old fee], len([old fee]) - 1))

you would then get this result for the same data set:

testcurrency	VALconversion
$5.00	5
$ 10.00	10
10	0
A10	10

Notice it captures everything EXCEPT the straight number so a further modification:

IIf(IsNumeric([testcurrency]),CCur([testcurrency]),Val(Right([testcurrency],Len([testcurrency])-1)))

yields this:

testcurrency	VALConversion
$5.00	$5.00
$ 10.00	$10.00
10	$10.00
A10	$10.00

[June7]

Thanks, rpeare, I completely overlooked that currency symbol and possibly other characters and/or space would probably be part of the original data. Consistency of structure is critical when manipulating strings. If there can be other configurations (such as multiple alpha/symbol characters as prefix), gets more complicated.

[LeadTechIG]

Thank you very much! The two of you have given me the information I need to fix what I need to fix. Your help is much appreciated!