form to filter upon open

[wm2020!]

Hi,

I hope someone can help.

I have a database with one table which will have duplicates in the database on purpose.

so the reference number will be duplicated and needs to remain in the database. The distinction would be the date. there are 12 other columns of information that need to be displayed, but below is an sample.

Ref Date
Ref-001 20/01/19
Ref-001 02/08/19
Ref-001 04/09/19
Ref-002 08/09/19
Ref-002 10/11/19

So I want to filter upon opening the form so that all the user will see is Ref-001 with date 04/09/19 and Ref-002 10/11/19 and not see the duplicated ref's because there need to be there for historical reasons.

I tried the MAX function in a query, which works, but as soon as I add more fields to the query to see more information relating to it, the max function doesn't work and I am seeing the other duplicates. Also I cannot edit the query linked form, as it is just a query not an editable source.

I was thinking of trying the filter function in form open event - so when the form opens, it will show what the query is meant to do. However I cannot find code to work to use max function or anything to help just filter out the duplicates.

would anyone be able to help with this one.

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "First Issue Received by BH" = Max
Me.FilterOn = True
End Sub

I tried the code, but didn't like it.

 

[Ranman256]

yes Max can only have 2 fields to get the Max.
build your Max fctn in Q1.
then in Q2, join your table to Q1 to get all the data AND the max.

 

[wm2020!]

Thank you for your response. I have created the max query which works fine, but to join it how do I do that?

 

[Ranman256]

use the key field, Q1:
select ID, Max(dateFld) from tData

then Q2:
select Q1, tData.* where tData.ID = Q1.ID

 

[wm2020!]

sorry I am pretty knew to SQL in Queries, which is what I assume you are referring too in your reply.

i basically have

query 1 which has my max function
query 2 which has all the data from my table - (14 fields)

In query 2, I have add query 1 as well and joined them together by drag and drop the max function field with my date field so it is linked and went to join properties and well it doesn't do what it is suppose to do when I run it.

stop me where I am going wrong with this...

so I looked into the SQL for Query2 and not sure where to put that information you wrote in your reply. Because if i replace all the fields with the above text, I get expression pop up in the query design and running it fails.

so to be more precise, this is my SQL for Query2:


SELECT LCFframetracker.Contractor, LCFframetracker.[Conject Reference], LCFframetracker.[Document Type (Register name)], LCFframetracker.Title, LCFframetracker.[Review Type], LCFframetracker.[TranIn Link], LCFframetracker.[Document Revision], LCFframetracker.[First Issue Received by BH], LCFframetracker.[Response due (10 working days)], LCFframetracker.[Conject Response Provided], LCFframetracker.[Conject response days], LCFframetracker.Status, LCFframetracker.[Status A/B/C], LCFframetracker.[Reviewer / Owner], LCFframetracker.[Review Comment]
FROM LCFframetracker INNER JOIN [max] ON LCFframetracker.[First Issue Received by BH] = Query1.[MaxOfFirst Issue Received by BH];

First Issue Received by BH is the date field in my table

Conject Reference is the field with the duplicated numbers