Pull date that is less than or equal to another date field

[Dan G]

this function needs to review the entry date of a particular task ID and match it to table which houses task IDs, their value and the "go live" date for the particular values

TblTimings houses the data regarding the go live dates and task values.

tblData has the entry date, and it is currently set up to be pulled into the query that this function would be plugged into.

i.e. taskID 2 had a value of 2.0 beginning on 1/1/10, the value was updated on 6/1/10 to be 30.0

Now the people using the database enter their info for entry date on 4/1/10.

I want the function to match 4/1/10 to see that taskID 2 had a value of 2.0 on 4/1/10.

I was trying to get it to review DateDiff that was the smallest value without being less than zero.

I am pretty new to coding, so I was wondering if someone could take a look and let me know what they think...

Thanks
D

Function GetTiming(EntryDate, TaskID) As Double
        Dim sqlstr As String
        Dim db As Database
        Dim rs As Recordset
 
 
 
 
sqlstr = "SELECT tblTimes.TimingID, tblTimes.TaskID, tblTimes.LiveDate, tblTimes.Timing FROM tblTimes WHERE (((tblTimes.TaskID) = " & TaskID & "))ORDER BY tblTimes.LiveDate DESC;"
 
        Set db = CurrentDb
 
        Set rs = db.OpenRecordset(sqlstr)
 
 
        If rs.RecordCount = 0 Then
            GetTiming = 0
            'MsgBox "No records found"
        Exit Function
        End If
        If rs.RecordCount = 1 Then
            GetTiming = rs!Timing.Value
        Exit Function
        End If
        Do Until Not rs.EOF
        Debug.Print TaskID & ": " & rs!Timing.Value
            If DateDiff("d", EntryDate, rs!LiveDate.Value) >= 0 Then GetTiming = rs!Timing.Value: Exit Do
        rs.MoveNext
        Loop
        rs.Close
 
 
 
 
End Function
 

Update:
Access 2003, sorry I forgot that. This is my first question on the boards. Newbie. :)

[NeoPa]

I read this through a couple of times and I have no better idea what you're asking now than I had before I started.

Perhaps outlining the table structures and providing some example data and what's required in the context of that data might be a better way to phrase your question Dan.

[Dan G]

Sorry. This is a system for tracking productivity

tblData captures the person's input

NetID - identifies the person entering the data
Count - their count of each work unit completed
TaskID (id for type of task - links to table of tasks
EntryDate (for date they are filling in data for)
InputDate (time stamp)

tblTimes
TimingID - key
TaskID
Timing - the timing assigned to the particular task
LiveDate - the date at which this particular timing went into use

Times tables holds this info:

task timing livedate
1 5minutes Dec 31, 2010
1 10 minutes Jan 10, 2011

Data table holds this info:
John Doe Jan 1, 2011 - task#1 - 45 units

John Doe Jan 15, 2011 - task#1 - 30 units


I want to be able to run function that checks which livedate/timing was active for the dates.

So it would return the 5 minute value for the first line of data because the live date was effective 12/31/10, while the second one would get the 10 minute value because it was after the 1/10/11 live date.

Hopefully this makes a little more sense.

Thanks

[NeoPa]

Frankly Dan, though this is a much better stab at it, trying to follow your meaning is severely hampered by the numerous inconsistencies in your description. Use of undefined terms (They mean something to you but not anyone else.), displaying example data in an order which doesn't even match the order in the table layout provided. I don't want to put you down, but you need to understand that asking a question takes care and application to the task if anyone is to read it and make enough sense of it to help. I'm afraid I gave up after trying a few times to guess what the question probably should have said (in all the gaps where what you posted didn't add up).