Solved Getting a currency to display in a form (1 Viewer)

[Eric the Viking]

I have a form to show debits, receipts and a balance for members of my club. The form is based on a query (qry3 which calculates balance from the output of qry1 and qry2) that is itself based on two queries (qry1 and qry2 which calculate the sum of debits and receipts) to produce the required calculated results by member. All the queries when run on their own will display currency properly with a leading £ sign. However, when the form opens only the balance value calculated by qry3 displays with the leading £ sign. The fields on the forms are all have the currency format. Is there a way to overcome this issue?

 

[CJ_London]

The fields on the forms are all have the currency format.

forms have controls, not fields, so not sure which you are referring to - set the format property of the control to 'currency'.

Other possibility is that somewhere in your queries you have changed the field values to text datatype? This can usually be determined if the values are left justified (text) or right justified (number), providing you have not specifically set the justification

 

[Eric the Viking]

Hi, all the formats on the form are set to currency and this is also true of the queries themselves which when run individually display the values in currency format. The relevant table fields are all formatted as currency as well. The values from qry1 and qry2 are then used in an Nz function in qry3 as not all members will have entries for debits and receipts or both and I wish to display all members accounts in the form.

 

[Eric the Viking]

And then I had a brainwave, well sort of......, FormatCurrency(Nz(......,0)) sorts the problem out. Doh Thank you for your response though it got me thinking a tad more logically!

 

[CJ_London]

your nz function may well be the problem

?vartype(nz(null,0.00))
5
?vartype(nz(null,0))
2

5 is a double type, 2 an integer

I'm guessing that formatcurrency returns a string datatype

You can just use the format property (not function) to show null values - for numbers there are 4 states - positive, negative, zero and null separated by semi colons - can even show text and/or colour. e.g.

0.00;[red]0.00;0.00;"this is null"

 

[Eric the Viking]

your nz function may well be the problem

?vartype(nz(null,0.00))
5
?vartype(nz(null,0))
2

5 is a double type, 2 an integer

I'm guessing that formatcurrency returns a string datatype

You can just use the format property (not function) to show null values - for numbers there are 4 states - positive, negative, zero and null separated by semi colons - can even show text and/or colour. e.g.

0.00;[red]0.00;0.00;"this is null"

 

[Eric the Viking]

Thank you.