Creating function for use in query

[HeathRamos]

I have a table that contains the following info:

Employee number, date and a number field (containing a value between 0.25 and 1).

I need a query that returns the sum of the past 12 months (current date minus one year) but also need the following:

If there is a gap of more than 90 days, subtract one from the total. In other words, if the date field were sorted, check for how many days inbetween entries. after which for the ones above 90 days, subtract one from the total, if any of those are about 120, subtract 2 from the total and if any are above 270, subtract 3 (without going below 0, of course).

I hope this makes sense.

 

[drkhelmt]

I don't totally get the prose, but this might be a start:

First get a recordset of the necessary data, and start a loop. This is in DAO code, so modify if necessary:

dim sqlst as string, date1 as string, date2 as string
dim total as double
dim rst as dao.recordset
dim db as dao.database

sqlst  = "SELECT employee.number, employee.dateEntered, employee.numberField " _
   & "FROM employee " _
   & "WHERE (employee.date > #" & (date - 90) & "#) AND (employee.number = me.empNo) " _
   & "SORT BY employee.number;"
set db = currentdb
rst = db.openrecordset(sqlst)

do while not rst.eof 'loop until all records have been read
   'get the first record, then move to the next record and compare
   date1 = rst.dateEntered
   rst.movenext
   date2 = rst.dateEntered
   'compare the dates, do the math and ouput the total of the number field in a message box
   total = dlookup("sum(numberField)","rst") 'I THINK you can do this on recordsets
   if datediff(date1, date2) > 270 then
      total = total - 3
   elseif datediff(date1, date2) > 180 then
      total = total - 2
   elseif datediff(date1, date2) > 90 then
      total = total - 1
   else
      'do no math since there is no gap > 90 days
   end if
   msgbox total
loop

This needs to be run from a form that has a field empNo that contains the employee you want to work with. I suppose you could modify this code with a loop that would do all employees, but I didn't code it that way.

Also, I'm coding from memory and have not tested this out, so use at your own risk.

Hope this gives you something to start with.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[HeathRamos]

Couldn't I create a function that takes an employee number and returns the correct value?

For example (not actual code):

Function AdjTotal (emp) as double
Declare a bunch of stuff
while not EOF and emp=employee.empno
date1 = rst.dateEntered
rst.movenext
'if rst.movenext is EOF or emp<>employee.empno then 'date2=currentdate
date2 = rst.dateEntered
if datediff(date1, date2) > 270 then
returnval = returnval + 3
elseif datediff(date1, date2) > 180 then
returnval = returnval + 2
elseif datediff(date1, date2) > 90 then
returnval = returnval + 1
else
end if
msgbox returnval
loop

Something like this may give you what you had to subtract from the total. Can do this later in Access.

Would this work?

What would the code look like?

How can I sort it first? Should the recordset be based off a sorted query?

 

[HeathRamos]

well...it's official...I have no idea what I am doing.

Here is the code so far that I can't get to work:

Function AbLate(Emp As String) As Integer

Dim db as Database
Dim rec as Recordset
Dim AdjTot As Integer
Dim Date1 As Date
Dim Date2 As Date

Set db = CurrentDb()
Set rec = db.OpenRecordset("qrysortedmaster", dbOpenDynaset)
AdjTot = 0

rec.FindFirst "empno= " & Emp

Do While Not rec.EOF And rec.empno = Emp

Date1 = rec.Date
rec.MoveNext
If Emp <> rec.empno Then
Date2 = Date()
ElseIf rec.EOF Then Date2 = Date()
Else
Date2 = rec.Date
End If

If DateDiff("d", Date1, Date2) >= 270 Then
AdjTot = AdjTot + 3
ElseIf DateDiff("d", Date1, Date2) >= 180 Then
AdjTot = AdjTot + 2
ElseIf DateDiff("d", Date1, Date2) >= 90 Then
AdjTot = AdjTot + 1
Else
End If
Loop
AbLate = AdjTot
End Function

 

[PHV]

Instead of MoveNext and EOF you may try the FindNext method and the NoMatch property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HeathRamos]

I think I am making some progress (after selecting the right reference libraries) but I am still getting an error (Run-time error 3021 No Current record). FYI...I made a list of employee numbers from the query qrysortedmaster and created another query that called the function AbLate referecing that list.

Here is the code:

Function AbLate(Emp As String) As Integer

Dim db As Database
Dim rec As Recordset
Dim AdjTot As Integer
Dim Date1 As Date
Dim Date2 As Date

Set db = CurrentDb()
Set rec = db.OpenRecordset("qrysortedmaster", dbOpenDynaset)

rec.MoveFirst
Do While Not rec.EOF And rec("Empno") <> Emp
rec.MoveNext
Loop

Do While Not rec.EOF And rec("Empno") = Emp
Date1 = rec("Date")
rec.MoveNext
If rec.EOF Then
Date2 = Date
ElseIf rec("Empno") <> Emp Then
Date2 = Date
Else
Date2 = rec("Date")
End If

If DateDiff("d", Date1, Date2) >= 270 Then
AdjTot = AdjTot + 3
ElseIf DateDiff("d", Date1, Date2) >= 180 Then
AdjTot = AdjTot + 2
ElseIf DateDiff("d", Date1, Date2) >= 90 Then
AdjTot = AdjTot + 1
Else
End If
Loop
rec.Close
AbLate = AdjTot
End Function

 

[drkhelmt]

I think the error is caused by trying to go past the last record. PHV is on the right track with FindNext and NoMatch.

Hope this gets you closer!

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[HeathRamos]

I agree the error is being caused by trying to go past the last record, just not sure how to fix it.

If you use an immediate window and call the function with specific employee numbers, they all work except for the last employee number on the list.

Originally instead of using the first loop, I used rec.FindFirst but it errored out so I changed it.