First and Last Dates

[pbuethe]

I am trying to use a field on a form to select records in a table, then find the first and last dates in a field in the table and display them on the form. It is a continuous form. (The table holding the dates is a different table than the one underlying the query the form is based on.) Right now I have the following code in the form's Load event:

Dim curX As Date
Dim curY As Date

curX = DMin("[datServiceDate]", "tblAdjMaster", "[txtAdjTapeNbr] = '" _
& Me!txtTapeNbr & "'")
Me!txtFirstDate = curX

curY = DMax("[datServiceDate]", "tblAdjMaster", "[txtAdjTapeNbr] = '" _
& Me!txtTapeNbr & "'")
Me!txtLastDate = curY

This works except that it displays the values for the first record on every record. How can I get this to work? Let me know if you need more explanation.

Thanks in advance.

 

[FoFa]

You need to include the dates on the query underlying the form since it is a continuous form. This seems to be one of the limitations of continuous forms.

 

[pbuethe]

Hi FoFa,

Thanks for your response. How do I include the date in the query? When I added the date to the query it still listed only the first and last dates for the first value of the tape number, on every tape number, and repeats the records over and over.

 

[llkhoutx]

If your date field is indexed, you can use the following

Dim db As Database
Dim rs As Recordset
Dim str As String
Dim dtMin as Date
dim dtMax as Date

Set db = CurrentDb
Set rs = db.OpenRecordset("tblAdjMaster", dbOpenDynaset)
rs.MoveFirst
dtMin = rs!datServiceDate
rs.MoveLast
dtMax = rs!datServiceDate

 

[pbuethe]

llkhoutx,

Can you explain this code? Where should it go?


When I replaced my Load event code with this code it gave me a type mismatch message on the OpenRecordset line.

Also, is the Dim str as String needed? It is not used in the code.

 

[llkhoutx]

Yes, dim str is a carry over from when I tested the code. Delete it.

Add the code to whatever event you want. why not in place of the code you posted.

 

[pbuethe]

I decided to give up and make it a single form. It works that way, and I added a subform with the detail from tblAdjMaster.

Thanks for your responses.

 

[llkhoutx]

Simple is always best. Elegant code is not always easy to understand.