Solved conditional formatting for the last record per group (1 Viewer)

faty

New member
Local time
Today, 15:40
Joined
Apr 3, 2020
Messages
10
Hi.

i have problem to set conditional formatting to my subform for the last record for each group.

1 1
1 2
1 3
2 1
2 2
3 1

Can you help me plz with this thanks
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:40
Joined
May 21, 2018
Messages
4,090
Groups.jpg

See demo.
 

Attachments

  • Groups.zip
    24.2 KB · Views: 17

June7

AWF VIP
Local time
Today, 05:40
Joined
Mar 9, 2014
Messages
3,375
Yes, can be done - with limitations. The result is a non-editable dataset - cannot edit data on form. Can't have more than 50 groups because that is the limit for number of rules.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:40
Joined
May 21, 2018
Messages
4,090
Yes, can be done - with limitations. The result is a non-editable dataset - cannot edit data on form
That is only a limitation of the solution I presented. If you want it editable then choose an editable query. I Could have simply made a function MaxOfGroup and used that in the query instead of an aggregate query.
 

arnelgp

error reading drive A:
Local time
Today, 22:40
Joined
May 7, 2009
Messages
11,130
I also made an example without using extra Query
and only using Condititional Format.
 

Attachments

  • lastInGroup.zip
    21.2 KB · Views: 21

faty

New member
Local time
Today, 15:40
Joined
Apr 3, 2020
Messages
10
A million thanks
an another point : what if i have more than 3 groups ? and i have access 2007
thank you
 

strive4peace

AWF VIP
Local time
Today, 08:40
Joined
Apr 3, 2020
Messages
571
hi faty,

you can put a calculated field in the record source to figure this out:
IsLast: IIf([ItemNumber_fieldname]=nz(DMax("ItemNumber_fieldname","MyTable","GroupNumber_fieldname =" & [GroupNumber_fieldname]),1),True,False)

the advantage is that you can then just set up one conditional format using Expression Is:
[IsLast]=True
 
Last edited:

faty

New member
Local time
Today, 15:40
Joined
Apr 3, 2020
Messages
10
Hi strive4peace
it's very genius but i don't know how to make it, it's difficult for me.

if you make me a pleasure and attach Access file with your exemples. thanks
 

strive4peace

AWF VIP
Local time
Today, 08:40
Joined
Apr 3, 2020
Messages
571
hi faty,

Here is how to add the calculated field indicating the last item for each group:
  1. From the Navigation Pane, open the subform in Design View
  2. Turn on the Property Sheet -- if it isn't showing, choose Property Sheet from the Design ribbon or press Alt-Enter
  3. Click on the Data tab of the Property Sheet
  4. Click in the Record Source property
  5. Press Ctrl-F2 or click [...] at end to go to the builder
  6. in a blank column, in the Field cell at the top, enter this expression:
  7. IsLast: IIf( [ItemNumber_fieldname]=nz(DMax("[ItemNumber_fieldname]","[MyTable]","[GroupNumber_fieldname]=" & [GroupNumber_fieldname]),1),True,False)
    change:
    MyTable to the name of your table
    GroupNumber_fieldname to the fieldname for your group number
    ItemNumber_fieldname to the fieldname for your item number
  8. Click on the Datasheet view icon to look at the data
    0 means False, -1 means True
    The value will be -1 for the last item number in each group
  9. Click the Save icon
  10. Close the builder
Now you will have a calculated field called IsLast that you can use for conditional formatting :)
 

strive4peace

AWF VIP
Local time
Today, 08:40
Joined
Apr 3, 2020
Messages
571
hi faty,

aah! I just realized this question is in the "Introduce Yourself" forum! This is the place where you tell people about yourself, not ask questions. You'll find the button in other forums that are meant for asking questions ;)

Perhaps ask a moderator to move your question?
 

strive4peace

AWF VIP
Local time
Today, 08:40
Joined
Apr 3, 2020
Messages
571
I'm new here too, but just sent a Report. Maybe you want to do that too? click Report at the bottom of your first post. I guess that's how to get their attention!
 

Users who are viewing this thread

Top Bottom