Strange format of the result of caculated control..

G

Guest

Hi

I have a calculated control with in a query with this switch function:

I want to have the result in currency format and have set correctly but it
still looks like this when running the query: 800.000011920929

The null values looks ok.

Mattias

BeräknadSlagavgift:
Switch([KlubbatBeloppSEK]>[FromBel11];IIf([Spec%11]>0;[KlubbatBeloppSEK]*[Spec%11];[SpecBel11]);[KlubbatBeloppSEK]>[FromBel10];IIf([Spec%10]>0;[KlubbatBeloppSEK]*[Spec%10];[SpecBel10]);[KlubbatBeloppSEK]>[FromBel9];IIf([Spec%9]>0;[KlubbatBeloppSEK]*[Spec%9];[SpecBel9]);[KlubbatBeloppSEK]>[FromBel8];IIf([Spec%8]>0;[KlubbatBeloppSEK]*[Spec%8];[SpecBel8]);[KlubbatBeloppSEK]>[FromBel7];IIf([Spec%7]>0;[KlubbatBeloppSEK]*[Spec%7];[SpecBel7]);[KlubbatBeloppSEK]>[FromBel6];IIf([Spec%6]>0;[KlubbatBeloppSEK]*[Spec%6];[SpecBel6]);[KlubbatBeloppSEK]>[FromBel5];IIf([Spec%5]>0;[KlubbatBeloppSEK]*[Spec%5];[SpecBel5]);[KlubbatBeloppSEK]>[FromBel4];IIf([Spec%4]>0;[KlubbatBeloppSEK]*[Spec%4];[SpecBel4]);[KlubbatBeloppSEK]>[FromBel3];IIf([Spec%3]>0;[KlubbatBeloppSEK]*[Spec%3];[SpecBel3]);[KlubbatBeloppSEK]>[FromBel2];IIf([Spec%2]>0;[KlubbatBeloppSEK]*[Spec%2];[SpecBel2]);[KlubbatBeloppSEK]>[FromBel1];IIf([Spec%1]>0;[KlubbatBeloppSEK]*[Spec%1];[SpecBel1]))
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Controls are not in queries. What you describe is a calculated column.

If you just want to display this data as currency when the query
displays as a datasheet (not in a form/report):

1. In the QueryDef's QBE grid, on the Field cell of the calculated
column - Right click & select Properties.
2. In the column's Format property enter the word Currency.

If the value is displayed in a form, do the same thing: set the Format
property of the form's control to Currency.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmaHRIechKqOuFEgEQJtiwCcCMgPPEFQkwNWykQkfe0xRFLS6uYAoJii
tdS75tMVOlzxAdCvR5h1yLot
=2KcY
-----END PGP SIGNATURE-----
 
J

John Vinson

Hi

I have a calculated control with in a query with this switch function:

I want to have the result in currency format and have set correctly but it
still looks like this when running the query: 800.000011920929
Click to expand...

It's apparently doing the calculation as a Double Float (and you're
getting roundoff error). Try using the CCur() function to convert the
value to a Currency datatype (the Currency format is irrelevant, it
just controls how the number is displayed and can apply to any type of
number).

BeräknadSlagavgift:
CCur(Switch([KlubbatBeloppSEK]>[FromBel11];IIf([Spec%11]>0;[KlubbatBeloppSEK]*[Spec%11];[SpecBel11]);[KlubbatBeloppSEK]>[FromBel10];IIf([Spec%10]>0;[KlubbatBeloppSEK]*[Spec%10];[SpecBel10]);[KlubbatBeloppSEK]>[FromBel9];IIf([Spec%9]>0;[KlubbatBeloppSEK]*[Spec%9];[SpecBel9]);[KlubbatBeloppSEK]>[FromBel8];IIf([Spec%8]>0;[KlubbatBeloppSEK]*[Spec%8];[SpecBel8]);[KlubbatBeloppSEK]>[FromBel7];IIf([Spec%7]>0;[KlubbatBeloppSEK]*[Spec%7];[SpecBel7]);[KlubbatBeloppSEK]>[FromBel6];IIf([Spec%6]>0;[KlubbatBeloppSEK]*[Spec%6];[SpecBel6]);[KlubbatBeloppSEK]>[FromBel5];IIf([Spec%5]>0;[KlubbatBeloppSEK]*[Spec%5];[SpecBel5]);[KlubbatBeloppSEK]>[FromBel4];IIf([Spec%4]>0;[KlubbatBeloppSEK]*[Spec%4];[SpecBel4]);[KlubbatBeloppSEK]>[FromBel3];IIf([Spec%3]>0;[KlubbatBeloppSEK]*[Spec%3];[SpecBel3]);[KlubbatBeloppSEK]>[FromBel2];IIf([Spec%2]>0;[KlubbatBeloppSEK]*[Spec%2];[SpecBel2]);[KlubbatBeloppSEK]>[FromBel1];IIf([Spec%1]>0;[KlubbatBeloppSEK]*[Spec%1];[SpecBel1])))

John W. Vinson[MVP]
 
G

Guest

Hi John

Thank you for your reply.

Can you please help me with the syntax, can I apply the func the calculated
control I am working with right here.

Mattias

John Vinson said:
Hi

I have a calculated control with in a query with this switch function:

I want to have the result in currency format and have set correctly but it
still looks like this when running the query: 800.000011920929
Click to expand...

It's apparently doing the calculation as a Double Float (and you're
getting roundoff error). Try using the CCur() function to convert the
value to a Currency datatype (the Currency format is irrelevant, it
just controls how the number is displayed and can apply to any type of
number).

BeräknadSlagavgift:
CCur(Switch([KlubbatBeloppSEK]>[FromBel11];IIf([Spec%11]>0;[KlubbatBeloppSEK]*[Spec%11];[SpecBel11]);[KlubbatBeloppSEK]>[FromBel10];IIf([Spec%10]>0;[KlubbatBeloppSEK]*[Spec%10];[SpecBel10]);[KlubbatBeloppSEK]>[FromBel9];IIf([Spec%9]>0;[KlubbatBeloppSEK]*[Spec%9];[SpecBel9]);[KlubbatBeloppSEK]>[FromBel8];IIf([Spec%8]>0;[KlubbatBeloppSEK]*[Spec%8];[SpecBel8]);[KlubbatBeloppSEK]>[FromBel7];IIf([Spec%7]>0;[KlubbatBeloppSEK]*[Spec%7];[SpecBel7]);[KlubbatBeloppSEK]>[FromBel6];IIf([Spec%6]>0;[KlubbatBeloppSEK]*[Spec%6];[SpecBel6]);[KlubbatBeloppSEK]>[FromBel5];IIf([Spec%5]>0;[KlubbatBeloppSEK]*[Spec%5];[SpecBel5]);[KlubbatBeloppSEK]>[FromBel4];IIf([Spec%4]>0;[KlubbatBeloppSEK]*[Spec%4];[SpecBel4]);[KlubbatBeloppSEK]>[FromBel3];IIf([Spec%3]>0;[KlubbatBeloppSEK]*[Spec%3];[SpecBel3]);[KlubbatBeloppSEK]>[FromBel2];IIf([Spec%2]>0;[KlubbatBeloppSEK]*[Spec%2];[SpecBel2]);[KlubbatBeloppSEK]>[FromBel1];IIf([Spec%1]>0;[KlubbatBeloppSEK]*[Spec%1];[SpecBel1])))

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

John Vinson

Can you please help me with the syntax, can I apply the func the calculated
control I am working with right here.
Click to expand...

Yes; simply wrap your expression, whatever it is, in a call to the
Convert to Currency function:

=CCur(<your expression>)

or, if you want it rounded to two digits,

=Round(CCur(<your expression>), 2)

John W. Vinson[MVP]
 
G

Guest

Thanks

It works fine

Mattias

John Vinson said:
Yes; simply wrap your expression, whatever it is, in a call to the
Convert to Currency function:

=CCur(<your expression>)

or, if you want it rounded to two digits,

=Round(CCur(<your expression>), 2)

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

Guest

John

When running the query I noticed some errors in the in the fields/records
where the result of the Switch should be 0 (zero).

Can you help me to add the functionality below?


If KlubbatBeloppSEK = 0 then the result of the calculated control = 0



CCur(Switch([KlubbatBeloppSEK]>[FromBel11];IIf([Spec%11]>0;[KlubbatBeloppSEK]*[Spec%11];[SpecBel11]);[KlubbatBeloppSEK]>[FromBel10];IIf([Spec%10]>0;[KlubbatBeloppSEK]*[Spec%10];[SpecBel10]);[KlubbatBeloppSEK]>[FromBel9];IIf([Spec%9]>0;[KlubbatBeloppSEK]*[Spec%9];[SpecBel9]);[KlubbatBeloppSEK]>[FromBel8];IIf([Spec%8]>0;[KlubbatBeloppSEK]*[Spec%8];[SpecBel8]);[KlubbatBeloppSEK]>[FromBel7];IIf([Spec%7]>0;[KlubbatBeloppSEK]*[Spec%7];[SpecBel7]);[KlubbatBeloppSEK]>[FromBel6];IIf([Spec%6]>0;[KlubbatBeloppSEK]*[Spec%6];[SpecBel6]);[KlubbatBeloppSEK]>[FromBel5];IIf([Spec%5]>0;[KlubbatBeloppSEK]*[Spec%5];[SpecBel5]);[KlubbatBeloppSEK]>[FromBel4];IIf([Spec%4]>0;[KlubbatBeloppSEK]*[Spec%4];[SpecBel4]);[KlubbatBeloppSEK]>[FromBel3];IIf([Spec%3]>0;[KlubbatBeloppSEK]*[Spec%3];[SpecBel3]);[KlubbatBeloppSEK]>[FromBel2];IIf([Spec%2]>0;[KlubbatBeloppSEK]*[Spec%2];[SpecBel2]);[KlubbatBeloppSEK]>[FromBel1];IIf([Spec%1]>0;[KlubbatBeloppSEK]*[Spec%1];[SpecBel1])))
 
J

John Vinson

Can you help me to add the functionality below?


If KlubbatBeloppSEK = 0 then the result of the calculated control = 0
Click to expand...

The arguments to Switch() come in pairs - it processes the pairs left
to right; when it finds a pair where the left argument is True, it
returns the other member of that pair and quits.

So just add another pair of arguments in the Switch() function (in an
appropriate place, I don't know where that might be).

John W. Vinson[MVP]
 
G

Guest

Hi John

Unfortunatly it could not be added as the text bacame long!!! Must have
reached the textlimit of the query.

Do you know any other way?

Mattias
 
J

John Vinson

Hi John

Unfortunatly it could not be added as the text bacame long!!! Must have
reached the textlimit of the query.

Do you know any other way?
Click to expand...

Write a custom VBA function with a Select Case statement is all that
comes to mind.

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How "large" can a IIf be in a query? 12

Top