Why am I getting type mismatch from label in access form?

user_6348445 0
Viewed 118

I am getting values from labels that are formatted as Percent (0.00%).

Therefore, I cast it to a double CDBL(label.caption) and I get the type-mismatch error.... see my code:

Forms(frmName).Label80.Caption = format(CDbl(Forms(frmName).Label123.Caption) + CDbl(Forms(frmName).Label162.Caption), "Percent")

Originally: label123 has 10.00% value and label162 has 0.00% value

so if I do cdbl(label123) it gives me 10 (good!)

if I do cdbl(label162) it produces an error

if I do val(label162) it produces an error

I'm thinking it has something to do with 0?? I can't seem to figure this out...

ms-access
3 Answers

You can also use the Format function to parse the percentage and return a string containing the equivalent value in General Number format, so that it may be successfully converted using the CDbl function, e.g.:

Forms(frmName).Label80.Caption = Format(CDbl(Format(Forms(frmName).Label123.Caption, "General Number")) + CDbl(Format(Forms(frmName).Label162.Caption, "General Number")), "Percent")

The advantage of this method is that the Format function recognises that the string represents a percentage and therefore automatically handles the division by 100:

?Format("10.48%", "General Number")
0.1048

The use of CDbl also allows for regional differences in decimal number representation (e.g. the use of a comma in place of a period) - my thanks to @Gustav for pointing this out.

user_75315980

Try removing the % signs:

Forms(frmName).Label80.Caption = Format(Val(Replace(Forms(frmName).Label123.Caption, "%", "")) + Val(Replace(Forms(frmName).Label162.Caption, "%", "")), "Percent")

Edit:

Using Val, only works having dot (.) as the decimal separator.

For a universal solution, use CDbl or CCur as these convert the localised format correctly where a value formatted as percent may display as 10,48%.

Forms(frmName).Label80.Caption = Format(CDbl(Replace(Forms(frmName).Label123.Caption, "%", "")) + CDbl(Replace(Forms(frmName).Label162.Caption, "%", "")), "Percent")
user_35272970

Use the following template for each label. The evaluate takes care of the percent sign.

L0 = CDbl(Evaluate(Label0.Caption))
user_31433390
Related