New field value from large table column to be highlighted in report

[mmart33]

Hello All,

I have a daily report that shows data from previous day for production. When we have new products produced, I would like a field to be highlighted if it is the first time it has come up. I do not want it a unique field just from yesterday, but to analyze the table of all of the production days and highlight if a particular field from a column is unique.



Can this be done?

Thanks!!

[ranman256]

What is there to indicate this product is New? Once an item is in the table, it looks like all other items (new or old)
what defines new?

[mmart33]

I am not sure if I need to declare it as "new". What I mean is if I have several products produced over a period of time and my column field looks like this:
Date-Product
9/1 -A1
9/2 -A1
9/2 -A2
9/2 -A2
9/3 -A2
9/3 -A1
9/3 -A3

When I run my report on 9/3, I would like A3 in the report to be highlighted since it was the first time it was entered in the table. Make sense? Maybe somehow create a query to analyze the table for "FIRST" and if that record is called, any "FIRST" items from a field is highlighted?

Thanks

[June7]

But you don't want the first A1 and A2 to be highlighted? You want an Ax value highlighted if it is the only one?

A subquery that does a count of the Ax values might provide the info needed in the report RecordSource. A DCount() domain aggregate function could also. Then Conditional Formatting could use the count as criteria for highlighting textbox.

[mmart33]

Well if I pulled the record on just 9/1, I would want the A1 to be highlighted. If I pull the report on 9/3, I would only want the A3 to be highlighted since we had never produced it before and no field has had that value before that date.

Make sense? Does someone have example coding I could use?

Thanks again!

[June7]

This is a very unique requirement and I expect will require some creative coding. You say this data is all in one field - the date and Ax values should be in separate fields. If the report has the Ax as a grouping criteria, could maybe to a Count() calc in textbox and Conditional Formatting could use that.

What if you run the report on 9/3 and there is only one A1 record and one A3 record - should both be highlighted?

[Rawb]

June7's first suggestion of using a subquery or the DCount() function should work for what you want. If you want to be able to reprint a report from a previous date and have it highlight items correctly though, you will need to be sure and include the report date as part of the subquery or DCount() criteria.

Using DCount():

DCount("[Product]", "MyTable", "[Date]<=" & Me!ReportDate)

Then you can just use Conditional Formatting to highlight any rows where this returns exactly 1.