Conditioning Format in a Form (1 Viewer)

sour · Oct 30, 2018

Hello I have 52 fields in a form all of which have the following format based on week number, if it is true the back colour changes colour.
I have used the following expression [WK]="1" And [YEAR]="2018" if both are true then the back colour should change.
Some of them work some don't?
Can you help please.

CJ_London · Oct 30, 2018

if WK and YEAR are numeric then you don't need the " chars

YEAR is a reserved word - using them can cause unexpected errors - this may be one of them

other than that, you will need to explain more - where do these values come from? what are they? field or control names?

you say you have 52 fields - do you mean fields or controls? what are the control sources for the controls?

sour · Oct 30, 2018

Ok thanks, to cut a long story short I have a main form called switchboard, in that form I have two subforms all liked (child/master) by order number.
Each subform is for a year so at present it has 2018 and 2019 each containing 52 fields which are called week numbers.
So if I select an order number from the form (Combo Button) I can see week numbers for both 2018 and 19. I then can add hours to those week numbers based on manufacturing, I then get totals which eventually are exported to excel as a loading graph.
Each order number has a field called WK and YEAR these fields are based on the delivery day.
So what I am after is for the field back colour within the subform to chang colour if WK and YEAR are true.
What is happening is it works from say 1-20 but 20 to 52 don't work really strange.
Do you think I should try an expression rather that Con Format?

CJ_London · Oct 30, 2018

1. as previously advised, change the YEAR to something else, see if that solves the problem

2. I presume you have checked the wks 20-53 do have conditional formatting set

3. do the weeks in your subforms run horizontally or vertically? or some sort of calendar layout? trying to determine if these are single or continuous forms

4. your two fields are in the header - so how are you relating to that from your subforms? - your existing code won't work at all as far as I can see. I'm trying to determine how you are populating the conditional formatting since the code is fixed.

sour · Oct 30, 2018

1. ok I will change the year on a copy
2.yes double checked the weeks all have CF and all correct
3.The two forms are continuous forms and all of the fields run across the sub forms
4.The record source for the sub forms is from a query both of the fields WK and YEAR are in that query.

CJ_London · Oct 30, 2018

I don't understand your answer 3, please post a screenshot of the form - ideally demonstrating what works and what doesn't

re q4 - the implication is you have hardcoded the rules, so please also provide a screenshot of the conditional formatting form

Do you think I should try an expression rather that Con Format?

at the moment I'm still trying to understand what you actually have so can't say yes or no

sour · Oct 30, 2018

Screen shots below

CJ_London · Oct 30, 2018

You are showing the conditional formatting for 1 field - so far as I can see, there is not another field that meets that requirement - either WK <>10 or the year <>2018 (for the second subform)

you have not tried anything I suggested, so little point in continuing until you do - i.e.

if WK and/or YEAR are numeric, then drop the double quotes from your conditional formatting

Year is a reserved word, change the name to something else (both field and control names)

sour · Oct 30, 2018

Ok I will try changing the YEAR field the "" does not make any difference.
The conditioning format is for one field WEEK 10, 52 fields in 2018 and 52 fields in 2019, all of which have there own format for that week number.

CJ_London · Oct 30, 2018

@Sour, come back when you have done what I suggested

sour · Oct 31, 2018

Ok I have changed the field name, taken out the quotes"", double checked all of the code within the con form still only works from week 1-11 and 50-52?

Conditioning Format in a Form (1 Viewer)

sour

Registered User.

CJ_London

Super Moderator

sour

Registered User.

CJ_London

Super Moderator

sour

Registered User.

CJ_London

Super Moderator

sour

Registered User.

Attachments

CJ_London

Super Moderator

sour

Registered User.

CJ_London

Super Moderator

sour

Registered User.

Similar threads

Users who are viewing this thread