'Format' not available for some fields 1

[Mugs321]

Hey all,
I am trying to display the results of a complicated query in a report. Three fields should be of the 'Currency' Format. Two of the three fields don't display anything under the 'Format' pull-down in the Properties box. The third field has all the typical choices.

The odd thing is that the two fields in question are directly out of their respective tables and the field Format is set to 'Currency' in said tables. The one that works is actually a calculation of the other two.

Hope that made sense... Any ideas?

 

[dhookom]

When you view the datasheet of the report's record source, do the fields appear left or right aligned? Are they currency type or some other data type? Are you doing any calculations on the fields?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Mugs321]

dhookom,
Both fields in question are left-aligned in the query's datasheet view. The one that displays properly is right-aligned.

There are no calculations performed on the problem fields.

I think the problem may be in the SELECT portions of my query (I've left out everything but the SELECT and UNION statements):

SELECT [f.docNum] As Doc_Number, FORMAT(MAX([f.amount]),"0.00") AS FMAS_Total, FORMAT(SUM([p.amount]),"0.00") AS DB_Total, ABS(FMAS_Total)-DB_Total AS Difference

UNION ALL
 
SELECT f.docNum As  Doc_Number, Format(Max([f.amount]),"0.00") AS  FMAS_Total, 0 AS DB_Total, Abs(FMAS_Total)-DB_Total AS Difference

UNION ALL 

SELECT p.docNum As  Doc_Number, 0 AS  FMAS_Total, FORMAT(SUM([p.amount]),"0.00") AS DB_Total, Abs(FMAS_Total)-DB_Total AS Difference

 

[Mugs321]

I should have mentioned above... it's the FMAS_Total and DB_Total fields that won't format properly.

 

[Mugs321]

Ok... I changed Format() to FormatCurrency() and all fields are displaying as $0.00...

Now, all three fields are now aligned-left and none of them have any options in the Format pull-down... Why is this? (It works the way it should so you don't really have to answer this question.)

 

[dhookom]

You stated "directly out of their respective tables" but I doubt your table stores the Format(). Don't format them in the query. You are changing them to text strings. Get rid of the Format() function.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Mugs321]

dhookom,
I am displaying this query in a Listbox as well as a Report. I want the fields in the listbox to be formatted to currency also.

I am unaware of any way to format said fields. If I remove the format functions, the values just display as numbers in the listbox... not currency. As well, I get crazy numbers (ie. 10265.19 - 10265.19 = 1.81898940354586E-12)

Can u suggest an alternative?

 

[dhookom]

Why do you need to use the same query in both a list box and a report? There are other methods for rounding numbers such as the Round() function.

You could also set your control source to:
=Val(FMAS_Total)
but this would be like taking a numeric field, converting to text, and then converting back to a number. Why?

If you use the Val() function make sure the name of the control is not the same as the name of the field.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]