Create number field from text field

  • Thread starter Thread starter KV
  • Start date Start date
K

KV

I have a text field with values such as 12.33% as well as
the value A/M. I want to create a number field using the
information in this field. In other words, when there is
a A/M value in the text field, I want it to be translated
into the number 0.00 for the number field. If it is a
value such as 12.33% then I would like it to be
translated into the number 12.33 for the number field.
This only really needs to be done for new entries into
the database. I tried to solve this problem using the
following expression as a default value. This did not
seem to work. Will this type of expression not work as a
default? If not, how else could I solve this problem?

=IIf(
![TEXT_FIELD]="A/M","0",Val(
!
[TEXT_FIELD]))
....and then I have the control source as [NUMBER_FIELD]

Thank you for your help.
Sincerely,
KV
 
=IIf(
![TEXT_FIELD]="A/M","0",Val(
!
[TEXT_FIELD]))
Click to expand...


If you want this to be a numeric field, then the second parameter should be
0, not "0" (i.e. the number zero, not a one-char string). For safety, I
would use an explicit string slice for the third parameter rather than
trusting Val, because Val("12.33%") might be 0.1233 rather than 12.33 I
have not tested it, but I would go for something like

CDbl(Left(TEXT_FIELD, Len(TEXT_FIELD)-1))

...and then I have the control source as [NUMBER_FIELD]
....
I tried to solve this problem using the
following expression as a default value. This did not
seem to work. Will this type of expression not work as a
default? If not, how else could I solve this problem?
Click to expand...
No: a default value is only filled in when a new record is created (at
which time the TEXT_FIELD field is empty anyway, so there's nothing to
calculate).

If you are going to remove the old TEXT_FIELD and you want to preserve the
new NUMBER_FIELD, then put this expression into an UPDATE query. On the
other hand, if you just want to see this on a report or form, then you can
build the expression into the underlying query, because you wouldn't want
to keep the two values in the same table.

Hope that helps


Tim F
 
Back
Top