change field based on another field and increment by 1

[MadTom]

Been working on this all week and can't get it right! I've had so much help on this forum and have learned a lot, but still learning how to use the expression builder. Using the iif statement, but maybe another way to do what I need.
What I'm trying to do, If Bin_CompNum = 1, Bin_Comp_ID +1 (increment by 1 if Bin_CompNum =1), Bin_Comp_ID = Comp_ID (keep Comp ID the same value if Bin_CompNum not 1). Using an Update Query. Bin_CompNum and Bin_Comp_ID are both Long Integer.

UPDATE tblBinsComps SET tblBinsComps.Comp_ID = IIf([tblBinsComps]![Bin_CompNum]=1,[tblBinsComps]![Comp_ID]+1,[tblBinsComps]![Comp_ID]);

Thanks!
Tom

[accesstos]

Hi Tom!

At first, give a try to this Select query:

SELECT ID, Bin_CompNum, DCount("ID","tblBins","ID<=" & [ID] & " AND Bin_CompNum=1") AS Comp_ID
FROM tblBins;

The field ID is an autonumber field.

I hope that helps.

John

[MadTom]

All works great, now I have to put it all together!
My labels look great.
John, thanks so much for all your help! you are the best!
Tom

[accesstos]

You're welcome Tom!
Happy to help.

Wishing you all the best with your project!
John