Dlookup newest record by date

patrickglass

Engineering Student
Local time
Today, 12:17
Joined
May 10, 2006
Messages
10
Hello, i have an equipment table and an equipmentHours table. linked by an id on the two. basically i have an hour reading on the machines that gets entered every once and a while. no set times or dates. i have an equipment for where you can view the current equipment data. i was now would like to be able to just display the latest hour reading and the date it was taken, i dont really want to use a subform for just one record but i dont know what else i can do.

two textboxes txtEquipmentHours and txtHoursDate

i would like both of the populated with teh latest record with reference to the EquipmentID

I know how to use dlookup for returnnin a specific record but it cant sort so i dont know how i could have it link to the correct record. I hope someone gets what im trying to acheive. Thanks for the time. Patrick Glass
 
Try using the DLast function instead of DLookUp.
 
Ok well i attempted it and was unable to get it to work. i still do not know how to get a value when there is going to be no "Where" clause


Code:
Private Sub Form_Current()
On Error Resume Next
Me.txtHours = DLast("[HourReading]", EquipmentHours) 'not working - error
Me.txtDateHours = "i dont know"
End Sub

i cant see what im doing wrong other than the fact that i dont know how to use it. i need something like

SELECT hourreading, datehours FROM equipmentHours WHERE date = MAXDATE AND equipmentID = form!EquipmentID

this is psuedo but i hope it give you an idea of what i want. i just need to know how to retrive the largest date value for the selected machine
 
It's simple to get your function to look at the form field for a criteria:

http://www.mvps.org/access/general/gen0018.htm

By the way, you I suspect you want DMax not DLast, which may give unexpected results. Further, You'll need to use DLookup with an embedded DMax to get the reading from the last date.
 
Hello, thanks for the quick reply. i seem to not be thinking today as i cannot seem to wrap my head around this. heres is the code i am running. i dont know what is the problem with is. i am suspect of the 3rd argument in the function and how they were all referenced. if someone could help i would be more than thankfull Patrick

Code:
Private Sub Form_Current()

Dim strdate As String
Date = DMax("Date", "EquipmentHours", "EquipmentID=[Forms]![EditEquipment]![EquipmentID]")
If strdate <> "" Then
Me.txtHours = DLookup("HourReading", "EquipmentHours", "Date=" & strdate & " AND (EquipmentHours.EquipmentID)=[Forms]![EditEquipment]![EquipmentID]")
Me.txtDateHours = Date
End If

End Sub
 

Attachments

  • screenshot window.JPG
    screenshot window.JPG
    81.6 KB · Views: 305
Last edited:
You are correct that the third argument is wrong. The correct syntax is in the link I posted.

Also, you should not use "Date" as a field name, as it conflicts with the built in Date() function. Another also, you declare your variable as one name but then set a different name. And why would you declare a variable as a string that you want to be a date?
 

Users who are viewing this thread

Back
Top Bottom