Criteria don't work on a field calculated with a self-made function 1

[Anborn]

I have this problem with my query. There's a field that, based on order's needed to manufacture hours and delivery date, calculates the first possible week in which the order can be manufactured. It uses a function I wrote to specify the week.

Here's the code. Explanation below.

Function ReturnWeek(TotalHrs As Integer) As Date

Dim Cond As Boolean
Dim HrsLeft As Integer
Dim Week As Date
Dim dbCurr As Database
Dim rsWeeks As Recordset

Set dbCurr = CurrentDb
Set rsWeeks = dbCurr.OpenRecordset("Weeks")

Cond = False

Do Until Cond = True

If TotalHrs <= rsWeeks!HrsLeft Then
    ReturnWeek = rsWeeks!Week
    rsWeeks.Edit
    rsWeeks!HrsLeft = rsWeeks!HrsLeft - TotalHrs
    rsWeeks.Update
    Cond = True
Else
    rsWeeks.MoveNext
End If

Loop

End Function

Weeks table stores week data, which consists of: week's first day's date, hours availible during this week and hours currently left. If hours needed by an order to be manufactered are equal or less than hours left for the first week, this week is the result of the function. Else, it moves to next week.

After running the query I run a code or an update query to update the weeks table to its former state. Basically, everything works. Apart from criteria. Whenever I set some criteria, the query asks me for TotalHrs' value.

I have already tried lots of things to make it work. None were successful yet. I need the criteria for my reports. The closest I get it to working is using filter instead of criteria. The report shows what is supposed to be the correct record, but the function is performed twice on the record and makes it all wrong again.

Currently I'm stuck, having no more ideas, so I would appreciate any help you could provide and I'd be really grateful if you could find any solution to my problem.

Regards,
Anborn

 

[lespaul]

Please post the SQL query or code where you are calling this function.

Thanks,
Leslie

 

[Anborn]

Thanks for showing interest. Here's the SQL:

SELECT Orders.ID AS OrderID, Orders.Deadline AS [Date], [BasicTimes1]![Time]+[BasicTimes2]![Time] AS TotalHrs, ReturnWeek([TotalHrs]) AS WeekNo
FROM BasicTimes2 INNER JOIN (BasicTimes1 INNER JOIN Orders ON (BasicTimes1.Diameter = Orders.Diameter) AND (BasicTimes1.Route = Orders.End1)) ON (Orders.Diameter = BasicTimes2.Diameter) AND (BasicTimes2.Route = Orders.End2)
ORDER BY Orders.Deadline;

Regards,
Anborn

 

[lespaul]

have you tried:

SELECT Orders.ID AS OrderID, Orders.Deadline AS [Date], [BasicTimes1]![Time]+[BasicTimes2]![Time] AS TotalHrs, ReturnWeek([BasicTimes1]![Time]+[BasicTimes2]![Time]) AS WeekNo
FROM BasicTimes2 INNER JOIN (BasicTimes1 INNER JOIN Orders ON (BasicTimes1.Diameter = Orders.Diameter) AND (BasicTimes1.Route = Orders.End1)) ON (Orders.Diameter = BasicTimes2.Diameter) AND (BasicTimes2.Route = Orders.End2)
ORDER BY Orders.Deadline;

Leslie

 

[Anborn]

Thank you so much. It worked. I'm amazed at the simplicity of the solution. Thanks again.

Regards,
Anborn

 

[lespaul]

no problem! thanks for the star!