Text Box wont allow me to change formatting

[Chelcone]

Hi everyone



I have a subform that is pulling data from a query, all are pulled down in exactly the same way using an SQL query. However only the 1st and last of the values are allowing me to change the formatting options on the text box. The others are not showing any options on the format drop down in the property sheet.

Any ideas?

The text boxes are not locked.

thanks

Chelcone

[RuralGuy]

What are the differences in the field types bound to the controls?

[Chelcone]

None, they are all generated with a "feldname: <<expresson>>" in a query. all identical.

[TG_W]

Are you trying to change the formatting in Design View? I only ask because because you say "values" in your first post.

Also, have you tried Conditional Formatting if you need multiple formats for different parameters?

[Chelcone]

Sorry i may have worded it funny, here is the screenshot of the form fields.



Im looking at it in design view and then choosing the formatting option from the property sheet but the drop down is blank, its only on the middle three boxes.

I havent tried conditional formatting if im honest but they all need to be displayed as a currency format would that work in conditional formatting?

[TG_W]

Thanks for the clarification.

It goes back to RuralGuy's original question of what is the formatting in the control, i.e. how is the field formatted in the table you are getting your information? If it is Text, you will not have the option to format to Currency.

[Chelcone]

all the fields are based off of one control. Basicly they are percentages of a pay grade that is conditional on a tick box being true.

for example the field TheShiftAlowance is from this formula Allowance money: ((((([hourly rate]*[contracted hours])/100)*4.98)*52.178)/12)

[hourly rate] is currency
[contracted hours] is number

All of them are variations on this formula.

[Chelcone]

Also its not just that i cant format to currency, I cant format to anything. The dropdown is empty :S

[TG_W]

Ok. If you are doing the calculation in a query, check the Property Sheet in the query and set the Currency property there.

[Chelcone]

I tried that as well, no effect. Even did that, deleted the field in the form and re added it to the form. Nothing.

[TG_W]

What about changing it to a General Number in the query and formatting to Currency in the form?

[Chelcone]

I tried that as well, no effect. it doesnt matter what i change the format in the query to it just wont show anything in the format dropdown. Hopefully its nothing to do with the SQL on the final qurey that pulls the values together which is this:

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
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;

[TG_W]

Is it possible to post your database? SQL looks fine, and if it is returning results, it shouldn't be the problem.

If you cannot, I would try/check the following:

1. Property Sheet in all queries leading to the final query.
2. The final query as sitting in the data source for the form.
3. Scrap the entire form and rebuild it. If I were to do this, I would rename the current form giving troubles to copy over some of the formatting or use as a guide when rebuilding the form. This has seemed to work for me on rare occassions, though.

[Chelcone]

Unfortunately because the database contains t pay structure i cant post it.

I Checked the property sheets on the final query and it also wouldn't let me change the format of the middle three values on the query. I just don't get why. I have removed all formatting from the query's below the main one, i have also tried formatting them to general number. On the root query i can change the formatting to currency and it works fine. The issue seems to be with this query:



may need to go to the query section of the forum as it may not be a fault with the form?

[TG_W]

Before going to the query side, try copying the 2 boxes you can format and changing the data source since they are already formatted to Currency. I'm not sure if that will work or why I didn't think of that before. That's probably the last attempt to fix this problem I can think of without seeing the database.

If that doesn't work, and you don't find help in the query forums, it might be worth it to make a copy of the database and strip all the real data from it and dump some filler in so myself and others might take a look to see if we can help you more.