Fixed format not working correctly

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report with a query as its data source. The query includes 3 number fields using the Nz function (it is based on several other queries including 2 summary queries). I want to display these 3 fields in text boxes on the report using the Fixed format with 2 decimal places (as currency without the £ sign e.g. 750.00).

If a text box has 1 of the 3 summary fields as its data source, the list of Formats in the Properties Sheet is blank. If the data source is any other field the Formats work as they should. I can select Fixed format with the control unbound, but when it is then bound to a summary field the display is incorrect (e.g. 750 displays as 750 instead of the required 750.00).

The queries seem to work fine but I have pasted the SQL below. Does anyone have any suggestions?

Thank

qryPartsCosts:
SELECT qryProjectRef.lngProjectID, qryProjectRef.strProjectRef, qryNameAndAddress.strAddress,
Nz(qryPartsCostsTotal.curTotaParts,0) AS curTotalParts,
Nz(qryPartsCostsTotal2.curTotalLabourSundries,0) AS curTotalLabourSundries,
Nz(qryPartsCostsTotal.curTotaParts+qryPartsCostsTotal2.curTotalLabourSundries,0) AS curTota
FROM ((qryProjectRef
INNER JOIN qryNameAndAddress ON qryProjectRef.lngProjectID = qryNameAndAddress.lngProjectID)
LEFT JOIN qryPartsCostsTotal ON qryProjectRef.lngProjectID = qryPartsCostsTotal.lngProjectID)
LEFT JOIN qryPartsCostsTotal2 ON qryProjectRef.lngProjectID = qryPartsCostsTotal2.lngProjectID

qryPartsCostsTotal:
SELECT tblCosting.lngProjectID, SUM(tblCosting.sngQty*tblEquipment.curSalePrice*(100-tblCosting.sngDiscount)/100) AS curTotaPart
FROM tblCosting INNER JOIN tblEquipment ON tblCosting.lngEquipmentID = tblEquipment.lngEquipmentI
GROUP BY tblCosting.lngProjectID

qryPartsCostsTotal2:
SELECT tblSundry.lngProjectID, Nz(Sum(tblSundry.sngQty*tblSundry.curSalePrice*(100-tblSundry.sngDiscount)/100),0) AS curTotalLabourSundrie
FROM tblSundr
GROUP BY tblSundry.lngProjectID
 
I have a report with a query as its data source. The query includes 3 number fields using the Nz function (it is based on several other queries including 2 summary queries). I want to display these 3 fields in text boxes on the report using the Fixed format with 2 decimal places (as currency without the £ sign e.g. 750.00).

If a text box has 1 of the 3 summary fields as its data source, the list of Formats in the Properties Sheet is blank. If the data source is any other field the Formats work as they should. I can select Fixed format with the control unbound, but when it is then bound to a summary field the display is incorrect (e.g. 750 displays as 750 instead of the required 750.00).

The queries seem to work fine but I have pasted the SQL below. Does anyone have any suggestions?

Thanks

qryPartsCosts:-
SELECT qryProjectRef.lngProjectID, qryProjectRef.strProjectRef, qryNameAndAddress.strAddress,
Nz(qryPartsCostsTotal.curTotaParts,0) AS curTotalParts,
Nz(qryPartsCostsTotal2.curTotalLabourSundries,0) AS curTotalLabourSundries,
Nz(qryPartsCostsTotal.curTotaParts+qryPartsCostsTotal2.curTotalLabourSundries,0) AS curTotal
FROM ((qryProjectRef
INNER JOIN qryNameAndAddress ON qryProjectRef.lngProjectID = qryNameAndAddress.lngProjectID)
LEFT JOIN qryPartsCostsTotal ON qryProjectRef.lngProjectID = qryPartsCostsTotal.lngProjectID)
LEFT JOIN qryPartsCostsTotal2 ON qryProjectRef.lngProjectID = qryPartsCostsTotal2.lngProjectID;

qryPartsCostsTotal:-
SELECT tblCosting.lngProjectID, SUM(tblCosting.sngQty*tblEquipment.curSalePrice*(100-tblCosting.sngDiscount)/100) AS curTotaParts
FROM tblCosting INNER JOIN tblEquipment ON tblCosting.lngEquipmentID = tblEquipment.lngEquipmentID
GROUP BY tblCosting.lngProjectID;

qryPartsCostsTotal2:-
SELECT tblSundry.lngProjectID, Nz(Sum(tblSundry.sngQty*tblSundry.curSalePrice*(100-tblSundry.sngDiscount)/100),0) AS curTotalLabourSundries
FROM tblSundry
GROUP BY tblSundry.lngProjectID;
Click to expand...

In the report use an unbound text control.
Set it's control source to:
=Format([curTotal],"#,##0.00")

Do the same for each of the other fields.
The above will give comma separated 1000's.
If you do not wish that use "#0.00"
 
----- fredg wrote: ----

On Thu, 8 Apr 2004 03:56:03 -0700, Andrew wrote
I have a report with a query as its data source. The query includes 3 number fields using the Nz function (it is based on several other queries including 2 summary queries). I want to display these 3 fields in text boxes on the report using the Fixed format with 2 decimal places (as currency without the £ sign e.g. 750.00).
SELECT qryProjectRef.lngProjectID, qryProjectRef.strProjectRef, qryNameAndAddress.strAddress,
Nz(qryPartsCostsTotal.curTotaParts,0) AS curTotalParts,
Nz(qryPartsCostsTotal2.curTotalLabourSundries,0) AS curTotalLabourSundries,
Nz(qryPartsCostsTotal.curTotaParts+qryPartsCostsTotal2.curTotalLabourSundries,0) AS curTota
FROM ((qryProjectRef
INNER JOIN qryNameAndAddress ON qryProjectRef.lngProjectID = qryNameAndAddress.lngProjectID)
LEFT JOIN qryPartsCostsTotal ON qryProjectRef.lngProjectID = qryPartsCostsTotal.lngProjectID)
LEFT JOIN qryPartsCostsTotal2 ON qryProjectRef.lngProjectID = qryPartsCostsTotal2.lngProjectID
SELECT tblCosting.lngProjectID, SUM(tblCosting.sngQty*tblEquipment.curSalePrice*(100-tblCosting.sngDiscount)/100) AS curTotaPart
FROM tblCosting INNER JOIN tblEquipment ON tblCosting.lngEquipmentID = tblEquipment.lngEquipmentI
GROUP BY tblCosting.lngProjectID
SELECT tblSundry.lngProjectID, Nz(Sum(tblSundry.sngQty*tblSundry.curSalePrice*(100-tblSundry.sngDiscount)/100),0) AS curTotalLabourSundrie
FROM tblSundr
GROUP BY tblSundry.lngProjectID
Click to expand...

In the report use an unbound text control
Set it's control source to
=Format([curTotal],"#,##0.00"

Do the same for each of the other fields
The above will give comma separated 1000's.
If you do not wish that use "#0.00
--
Fre
Please only reply to this newsgroup
I do not reply to personal email


Thanks Fred, that worked a treat

Does anyone know why setting the number format in the control's preperty sheet wouldn't work? I've never had any trouble with it before

Andre
 
Back
Top