Query Prevents formatting of field

[Chelcone]

Hi Eveyone



This is a little complicated so i'll try explain it the best i can.

Im creating a system for payroll, people get a bonus depending on if they are able to do things like flexable shifts, this bonus is a percentage of the standard hourly rate that they get paid. to work this out I created a query for each calculation and then used the following method for each one, only changing the percentage.

X = The value name
Y = the percentage

X: ((((([hourly rate]*[contracted hours])/100)*Y)*52.178)/12)

based on if the tick box for this allowance is true on the persons record.

they all follow this exact formula.

There is then a query that pulls these together:

SELECT qry_core_earnings.[core Earnings] AS TheCoreEarnings, Nz([allowance Money],0) AS TheAllowanceAmount, Nz([rotation Ammount],0) AS TheRotationAmount, Nz([flex Ammount],0) AS TheFlexAmount, Nz([core Earnings],0)+Nz([allowance Money],0)+Nz([rotation Ammount],0)+Nz([flex Ammount],0) AS TheTotalAmount, qry_core_earnings.ID
FROM ((qry_core_earnings LEFT JOIN qry_shift_allowance ON qry_core_earnings.ID = qry_shift_allowance.ID) LEFT JOIN qry_flex_ammount ON qry_core_earnings.ID = qry_flex_ammount.ID) LEFT JOIN qry_rotation_ammount ON qry_core_earnings.ID = qry_rotation_ammount.ID;

Now when it gets to this part things get a bit strange with the formatting. These are supposed to be currency values, however only TheCoreEarnings and TheTotalAmount actually let me format the fields, ether in the query or in the form, the others all have a blank drop down.

The root field [core earnings] is a currency field, none of the other query's have formatting.

Any Ideas?

Thanks

[Robeen]

You lost me a little on whether your values are currency or percentages.

Having said that . . .
I have had this problem with some of my reports where the data I import into Access is not a Currency data type.
I have used a couple of different methods to overcome this:
1. For each of the currency fields, I have put the value into a CCur() function in the query.
2. Instead of forcing the values into currency type with CCur - I click on the field in query design view and then use Property Sheet -> Format -> Currency.
. . . but you're saying you can't do this - right - because Access is not giving you the option?
3. I format the value as Currency in the Form or Report that it ends up in.

Hope this helps!!

[Chelcone]

Sorry about the confusion haha, they are a currency value. They are a percentage of another currency field so one of them is 1.45% of £6.50 for example.

All apart from your CCur() suggestion i have already tried unfortunately. The CCur one i hadn't even thought of to be honest. I just tried it with the following syntax in the query is this correct?


CCur(Nz([allowance Money],0)) AS TheAllowanceAmount

[Robeen]

CCur(Nz([allowance Money],0)) AS TheAllowanceAmount

Yes - that looks correct!
All the Best!
Let us know if you still have problems.