Stuck Again....Restrict summed values

[matt beamish]

I have a written a time recording database. Users enter job start and end times within days, creating a total from which is subtracted contracted hours (which can vary). Users are allowed to accrue a flexi-balance (hours worked more or less than contracted hours) within any calender month. This balance can either be taken as flexi-leave or transfered to the next month, although the transfer cannot exceed 7.4 hours.

Within a form I want to show the summed monthly balance (which comprises the total of daily balances within the current month and the transferred balances of previous months).

I have created a txt month and year field for each record by using the month and year functions on dates, and then concatenating the month and year to create for example "5 2008"

I have a query that calculates the transferable balance for each month (using Iif to cap the value at 7.4). How do I restrict the choice of months so that only the months prior to that shown on the current form are summed.

I think this must be very simple, but I cant see how to do it! Apologies if none/little of this, makes sense.

Matt

 

[matt beamish]

Mmmm, I dont think I have made sense here thinking about it a bit more! My problem is how, having translated Dates into text fields, I can then do any sorting/selection on them.

I have tried to restrict my record select (using a subform in the main form) to summing values where monthyear is less than current record but I am returning no values - but I guess this is because I am trying to use a mathmatical operator on a text field!

Is using a subform in this way the right way to do it?

I was pleased that I had manipulated the core dates into these text monthyear values but now I cant use them as I would wish.

One solution would be to have an indexed table that listed possible monthyear text values with an ordered number so that comparisons could be made?

Matt

 

[matt beamish]

Think Ive sussed it now - DSUM is the function I needed.