Condition formatting expression when a shopnumber appears with an specific remark (1 Viewer)

killerflappy

Registered User.
Local time
Today, 22:51
Joined
Aug 23, 2017
Messages
50
Hi guys,
I’m struggeling with a condition formatting expression.
I have a table with the colums shopnumber, product, quantity, remark. Like:
1000 A 2 stock
1001 A 2
345 B 1 stock
1000 C 1
1001 A 1
1000 C 1
Then I have a crosstable query that sums up the quantity of the same products per shopnumber. Colums shopnumber, product A, product B and product C Result (no remark):
1000 2 0 2
1001 3 0 0
345 0 1 0
Now I want to make a conditional format on a column (for example the shopnumer) where it gets a colour if there is the remark “stock” for the shopnumber in the filst table. So 1000 and 345 should get a colour.
I searched the internet and tried to work with DCOUNT and DLOOKUP, but no success.
Can someone put me in the right direction?
 

killerflappy

Registered User.
Local time
Today, 22:51
Joined
Aug 23, 2017
Messages
50
Try the InStr() function.

Hi RuralGuy. I'm a NOOB in Expressions. I'm not sure how to implement this in an expression. Can you explain?

Also I made a query with all the shopnummers that has "stock" in the remark. So is there a way: check if shopnumber is in this query. If so make a colour.

It would be nices without a query.
 

RuralGuy

AWF VIP
Local time
Today, 15:51
Joined
Jul 2, 2005
Messages
13,826
You want to conditionally color a field if another field in a different table has the word "stock" in it? Sounds confusing to me.
 

killerflappy

Registered User.
Local time
Today, 22:51
Joined
Aug 23, 2017
Messages
50
You want to conditionally color a field if another field in a different table has the word "stock" in it? Sounds confusing to me.

See the 2 examples. The table and the query (in a form).

I want to conditionally color the shopnmumber in the query (in the form) if the same shopnumber has one row with the remark "stock" in the different table.

Maybe it is easier to use a seperate query that lists the shops with "stock" in the remarks. Than use the conditaionally color if the shopnumber in the query exists in the seperate query.
 

isladogs

MVP / VIP
Local time
Today, 21:51
Joined
Jan 14, 2017
Messages
18,186
It also sounds very confusing to me.
The nearest equivalent I've ever done is the attached image



Sorry its low resolution so not very clear

In this example, the CF on each student's subject grades depends not on the grade shown but on a separate field called the residual value (how grade compares to target) which isn't shown

So any of the grades can be formatted in different ways

To do this required a bit of trickery
1. In the report recordset, append the residual value e.g. -2 to the grade e.g. C with a colon separator => C:-2
2. Apply traffic light style conditional formatting to the section after the formatting (residual)
3. Display the section before the colon (grade)

You may be able to do a simplified version of this for your own purposes as follows:
a) Add e.g. ":S" to the shop numbers with stock
b) Add conditional formatting if the expression contains an S
c) Display the section before the ":" i.e. the shop number
 

Attachments

  • GradeGridFomatting.jpg
    GradeGridFomatting.jpg
    105 KB · Views: 188

RuralGuy

AWF VIP
Local time
Today, 15:51
Joined
Jul 2, 2005
Messages
13,826
Rather that look in another table, why not include the "stock" value in this query and simply not display that field on your form. You could then use the field in the conditional format statement without trying to examine another table/query.
 

killerflappy

Registered User.
Local time
Today, 22:51
Joined
Aug 23, 2017
Messages
50
Rather that look in another table, why not include the "stock" value in this query and simply not display that field on your form. You could then use the field in the conditional format statement without trying to examine another table/query.

!? I think you are right! That is an out-of-the-box solution for me.

I'm going to test
 

Users who are viewing this thread

Top Bottom