format in query column no formats in dropdown

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

Guest

When I try to format a number column in a query, the format dropdown contains
no options - it is blank
 
Is there a good reason to format in the query as opposed to in the form or
report that displays the query?
Is the column a calculation?
 
I have the same problem in the report. Yes it is a calculation using the NZ
function to fill in any blank cells with 0. The query uses 2 crosstab
queries. I needed to fill in the blanks because I want to apply conditional
formatting to a column if the column value is greater than another column.
The conditional format runs fine if I just use the fields in the query
without the NZ function. When I use the NZ expression the zeros are filled in
but the conditional format does not work correctly. It is as if the one
column is not being recognised as a number. Therefore I was trying to get a
number format in the query or report.
 
You can force a numeric value in the crosstab and replace the nulls with
zeros. Do you mind sharing your crosstab SQL?
 
SELECT qryAccCategoryxCostCentreTotals_Crosstab.CostCentre,
Budgets_Crosstab.Nursery,
qryAccCategoryxCostCentreTotals_Crosstab.AccCategory,
Budgets_Crosstab.ProductCode, qryAccCategoryxCostCentreTotals_Crosstab.[Total
Of GrandTotal], Budgets_Crosstab.[Total Of BudgetValue],
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Jan],0) AS JanPurchase,
Nz([Budgets_Crosstab].[Jan],0) AS JanBudget, Nz([Budgets_Crosstab].[Feb],0)
AS FebBudget, Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Feb],0) AS
FebPurchase, Nz([Budgets_Crosstab].[Mar],0) AS MarBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Mar],0) AS MarPurchase,
Nz([Budgets_Crosstab].[Apr],0) AS AprBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Apr],0) AS AprPurchase,
Nz([Budgets_Crosstab].[May],0) AS MayBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[May],0) AS MayPurchase,
Nz([Budgets_Crosstab].[Jun],0) AS JunBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Jun],0) AS JunPurchase,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Jul],0) AS JulPurchase,
Nz([Budgets_Crosstab].[Jul],0) AS JulBudget, Nz([Budgets_Crosstab].[Aug],0)
AS AugBudget, Nz([Budgets_Crosstab].[Sep],0) AS SepBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Aug],0) AS AugPurchase,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Sep],0) AS SepPurchase,
Nz([Budgets_Crosstab].[Oct],0) AS OctBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Oct],0) AS OctPurchase,
Nz([Budgets_Crosstab].[Nov],0) AS NovBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Nov],0) AS NovPurchase,
Nz([Budgets_Crosstab].[Dec],0) AS DecBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Dec],0) AS DecPurchase,
tlkpAccCodes.AccountGroup, tlkpAccCodes.[Product Type]
FROM tlkpAccCodes INNER JOIN (qryAccCategoryxCostCentreTotals_Crosstab LEFT
JOIN Budgets_Crosstab ON
(qryAccCategoryxCostCentreTotals_Crosstab.AccCategory =
Budgets_Crosstab.ProductCode) AND
(qryAccCategoryxCostCentreTotals_Crosstab.CostCentre =
Budgets_Crosstab.Nursery)) ON tlkpAccCodes.[Product Code] =
qryAccCategoryxCostCentreTotals_Crosstab.AccCategory;
 
It would be many times easier if you posted your crosstab SQL View.

--
Duane Hookom
MS Access MVP


Laudan said:
SELECT qryAccCategoryxCostCentreTotals_Crosstab.CostCentre,
Budgets_Crosstab.Nursery,
qryAccCategoryxCostCentreTotals_Crosstab.AccCategory,
Budgets_Crosstab.ProductCode,
qryAccCategoryxCostCentreTotals_Crosstab.[Total
Of GrandTotal], Budgets_Crosstab.[Total Of BudgetValue],
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Jan],0) AS JanPurchase,
Nz([Budgets_Crosstab].[Jan],0) AS JanBudget,
Nz([Budgets_Crosstab].[Feb],0)
AS FebBudget, Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Feb],0) AS
FebPurchase, Nz([Budgets_Crosstab].[Mar],0) AS MarBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Mar],0) AS MarPurchase,
Nz([Budgets_Crosstab].[Apr],0) AS AprBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Apr],0) AS AprPurchase,
Nz([Budgets_Crosstab].[May],0) AS MayBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[May],0) AS MayPurchase,
Nz([Budgets_Crosstab].[Jun],0) AS JunBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Jun],0) AS JunPurchase,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Jul],0) AS JulPurchase,
Nz([Budgets_Crosstab].[Jul],0) AS JulBudget,
Nz([Budgets_Crosstab].[Aug],0)
AS AugBudget, Nz([Budgets_Crosstab].[Sep],0) AS SepBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Aug],0) AS AugPurchase,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Sep],0) AS SepPurchase,
Nz([Budgets_Crosstab].[Oct],0) AS OctBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Oct],0) AS OctPurchase,
Nz([Budgets_Crosstab].[Nov],0) AS NovBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Nov],0) AS NovPurchase,
Nz([Budgets_Crosstab].[Dec],0) AS DecBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Dec],0) AS DecPurchase,
tlkpAccCodes.AccountGroup, tlkpAccCodes.[Product Type]
FROM tlkpAccCodes INNER JOIN (qryAccCategoryxCostCentreTotals_Crosstab
LEFT
JOIN Budgets_Crosstab ON
(qryAccCategoryxCostCentreTotals_Crosstab.AccCategory =
Budgets_Crosstab.ProductCode) AND
(qryAccCategoryxCostCentreTotals_Crosstab.CostCentre =
Budgets_Crosstab.Nursery)) ON tlkpAccCodes.[Product Code] =
qryAccCategoryxCostCentreTotals_Crosstab.AccCategory;


Duane Hookom said:
You can force a numeric value in the crosstab and replace the nulls with
zeros. Do you mind sharing your crosstab SQL?
Click to expand...
Click to expand...
 
Hello
I have now worked out what to do, I put Val( before the NZ statement and it
forced it to be read as a number. Thanks for your help.

Duane Hookom said:
It would be many times easier if you posted your crosstab SQL View.

--
Duane Hookom
MS Access MVP


Laudan said:
SELECT qryAccCategoryxCostCentreTotals_Crosstab.CostCentre,
Budgets_Crosstab.Nursery,
qryAccCategoryxCostCentreTotals_Crosstab.AccCategory,
Budgets_Crosstab.ProductCode,
qryAccCategoryxCostCentreTotals_Crosstab.[Total
Of GrandTotal], Budgets_Crosstab.[Total Of BudgetValue],
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Jan],0) AS JanPurchase,
Nz([Budgets_Crosstab].[Jan],0) AS JanBudget,
Nz([Budgets_Crosstab].[Feb],0)
AS FebBudget, Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Feb],0) AS
FebPurchase, Nz([Budgets_Crosstab].[Mar],0) AS MarBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Mar],0) AS MarPurchase,
Nz([Budgets_Crosstab].[Apr],0) AS AprBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Apr],0) AS AprPurchase,
Nz([Budgets_Crosstab].[May],0) AS MayBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[May],0) AS MayPurchase,
Nz([Budgets_Crosstab].[Jun],0) AS JunBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Jun],0) AS JunPurchase,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Jul],0) AS JulPurchase,
Nz([Budgets_Crosstab].[Jul],0) AS JulBudget,
Nz([Budgets_Crosstab].[Aug],0)
AS AugBudget, Nz([Budgets_Crosstab].[Sep],0) AS SepBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Aug],0) AS AugPurchase,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Sep],0) AS SepPurchase,
Nz([Budgets_Crosstab].[Oct],0) AS OctBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Oct],0) AS OctPurchase,
Nz([Budgets_Crosstab].[Nov],0) AS NovBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Nov],0) AS NovPurchase,
Nz([Budgets_Crosstab].[Dec],0) AS DecBudget,
Nz([qryaccCategoryxCostCentreTotals_Crosstab].[Dec],0) AS DecPurchase,
tlkpAccCodes.AccountGroup, tlkpAccCodes.[Product Type]
FROM tlkpAccCodes INNER JOIN (qryAccCategoryxCostCentreTotals_Crosstab
LEFT
JOIN Budgets_Crosstab ON
(qryAccCategoryxCostCentreTotals_Crosstab.AccCategory =
Budgets_Crosstab.ProductCode) AND
(qryAccCategoryxCostCentreTotals_Crosstab.CostCentre =
Budgets_Crosstab.Nursery)) ON tlkpAccCodes.[Product Code] =
qryAccCategoryxCostCentreTotals_Crosstab.AccCategory;


Duane Hookom said:
You can force a numeric value in the crosstab and replace the nulls with
zeros. Do you mind sharing your crosstab SQL?

--
Duane Hookom
MS Access MVP


I have the same problem in the report. Yes it is a calculation using the
NZ
function to fill in any blank cells with 0. The query uses 2 crosstab
queries. I needed to fill in the blanks because I want to apply
conditional
formatting to a column if the column value is greater than another
column.
The conditional format runs fine if I just use the fields in the query
without the NZ function. When I use the NZ expression the zeros are
filled
in
but the conditional format does not work correctly. It is as if the one
column is not being recognised as a number. Therefore I was trying to
get
a
number format in the query or report.

:

Is there a good reason to format in the query as opposed to in the
form
or
report that displays the query?
Is the column a calculation?

--
Duane Hookom
MS Access MVP


When I try to format a number column in a query, the format dropdown
contains
no options - it is blank
Click to expand...
Click to expand...
Click to expand...
 
Back
Top