CODEWITHSUNDEEP
sqlms-accessms-access-2010
rohrl77
rohrl77

Reputation: 3337

Field in MS Access query can not be formated

One field in my query (DSum("AuM","tbl_Client","Portfolio='" & [Portfolio] & "'") AS PortfolioAuM) can not be formated.

I have tried:

  1. Setting the format of the field in the query by going to properties and setting the desired format there. It does not take. Stranger still, I do not consistently have the option to select the formatting in properties. Sometimes it shows up, other times it gives me a blank and empty dropdown box from which i can not select anything.
  2. Manually setting a format by writing #'##0.0 in the Format field property.
  3. Writing the Format directly into my query using Format(DSum("AuM","tbl_Client","Portfolio='" & [Portfolio] & "'") AS PortfolioAuM,'#'##.0')
  4. Formating the field not in the query, but only in the form that the query was built for.
  5. Reformating the underlying data as a Double

None of these attempts worked. How can I format this field? Why does this happen?

Here is my SQL code:

SELECT tbl_Client.CIF, tbl_Client.Portfolio, 
    tbl_Criteria_Comp.Buyer_Review_StatusID, 
    tbl_Criteria_Comp.Comp_Review_StatusID, 
    tbl_Criteria_Tax.Tax_review_StatusID, 
    tbl_Client.AuM, 
    DSum("AuM","tbl_Client","Portfolio='" & [Portfolio] & "'") AS PortfolioAuM, 
    tbl_Client.BuyersReviewStatus, 
    [AuM]/[PortfolioAuM] AS AuMPoT, 
    tbl_Client.EntryDateReview
FROM 
    (tbl_Client INNER JOIN tbl_Criteria_Comp ON tbl_Client.CIF = tbl_Criteria_Comp.CIF) 
    INNER JOIN tbl_Criteria_Tax ON tbl_Client.CIF = tbl_Criteria_Tax.CIF;

Upvotes: 0

Views: 728

Answers (2)

asdev
asdev

Reputation: 943

The correct format part in SQL Query should be

SELECT 
    [...]
    Format(DSum("AuM","tbl_Client","Portfolio='" & [Portfolio] & "'"),'#,#0.0') AS PortfolioAuM
    [...]

I also think that modifing the SQL Query is the best Idea.

Upvotes: 0

Lee Mac
Lee Mac

Reputation: 16015

I would suggest changing your SQL expression to the following in order to avoid the use of the DSum function:

SELECT 
    tbl_Client.CIF, 
    tbl_Client.Portfolio, 
    tbl_Criteria_Comp.Buyer_Review_StatusID, 
    tbl_Criteria_Comp.Comp_Review_StatusID, 
    tbl_Criteria_Tax.Tax_review_StatusID, 
    tbl_Client.AuM, 
    t.PortfolioAuM,
    tbl_Client.BuyersReviewStatus, 
    [AuM]/[t.PortfolioAuM] AS AuMPoT, 
    tbl_Client.EntryDateReview
FROM 
    (
        (
            tbl_Client INNER JOIN
            (
                SELECT Portfolio, SUM(AuM) AS PortfolioAuM
                FROM tbl_Client
                GROUP BY Portfolio
            ) t
            ON tbl_Client.Portfolio = t.Portfolio
        )
        INNER JOIN tbl_Criteria_Comp ON tbl_Client.CIF = tbl_Criteria_Comp.CIF
    ) 
    INNER JOIN tbl_Criteria_Tax ON tbl_Client.CIF = tbl_Criteria_Tax.CIF;

This should retain the datatype of the AuM field and hence permit you to use the standard formatting options.

Upvotes: 1

Related Questions