M
MacLean
I’m trying to set the conditional formatting for a control in a repor
but I can’t get it to work in the Conditional Formatting dialog box fro
the Format menu. I keep getting a syntax error message saying tha
I may have entered a comma without a preceding value or identifier
This report is from a database that I built from information that used to b
kept in spreadsheets. When the report was made on a spreadsheet th
conditional formatting was as follows
Formula Is =AND (C1>0, CEILING($B1,$A1)<Sum(C1:L1)
C1 was in the “Bin1†column, B1 was in the “On hand Qty†column
A1 was in the “Bin Qty†column and C1 through L1 were "Bin1" through "Bin10"
When the condition is TRUE, the A10 cell or Bin1 would be filled in yellow
I know that in access CEILING doesn’t work and that SUM only works in column
and not across fields so I used –Int (-expression) for CEILING an
added up Bin1 through Bin10 one by one
Here’s what I have tried so far
Expression Is =AND([Bin1])>0,-Int(-[OnHandQty])/[BinQty])*[BinQty]
([Bin1]+[Bin2]+[Bin3]+[Bin4]+[Bin5]+[Bin6]+[Bin7]+[Bin8]+[Bin9]+[Bin10])
Obviously, didn’t work.
I also tried spelling out the full identifier for each control
Reports![Kanban Report]![Bin1] instead of just [Bin1]
Reports![Kanban Report]![OnHandQty] instead of [OnHandQty] and so on
but the expression was so long it wouldn’t fit in the dialog box
So I don’t know what else to try. If this is something that has to be done via VB
what would the code look like
Can anyone help, please
Thanks
but I can’t get it to work in the Conditional Formatting dialog box fro
the Format menu. I keep getting a syntax error message saying tha
I may have entered a comma without a preceding value or identifier
This report is from a database that I built from information that used to b
kept in spreadsheets. When the report was made on a spreadsheet th
conditional formatting was as follows
Formula Is =AND (C1>0, CEILING($B1,$A1)<Sum(C1:L1)
C1 was in the “Bin1†column, B1 was in the “On hand Qty†column
A1 was in the “Bin Qty†column and C1 through L1 were "Bin1" through "Bin10"
When the condition is TRUE, the A10 cell or Bin1 would be filled in yellow
I know that in access CEILING doesn’t work and that SUM only works in column
and not across fields so I used –Int (-expression) for CEILING an
added up Bin1 through Bin10 one by one
Here’s what I have tried so far
Expression Is =AND([Bin1])>0,-Int(-[OnHandQty])/[BinQty])*[BinQty]
([Bin1]+[Bin2]+[Bin3]+[Bin4]+[Bin5]+[Bin6]+[Bin7]+[Bin8]+[Bin9]+[Bin10])
Obviously, didn’t work.
I also tried spelling out the full identifier for each control
Reports![Kanban Report]![Bin1] instead of just [Bin1]
Reports![Kanban Report]![OnHandQty] instead of [OnHandQty] and so on
but the expression was so long it wouldn’t fit in the dialog box
So I don’t know what else to try. If this is something that has to be done via VB
what would the code look like
Can anyone help, please
Thanks