Dlookup multiple criteria

[matt beamish]

I wish to calculate a running balance on a form.
The sum is the sum of hours worked over contracted hours within the same month as the current record, and with date values previous to the current record.

I have a query "Q_running_Balance" that holds the balances

I am using this in the text box control

=DLookUp("RunningBalance","Q_Running_Balance","txtYearMonth=Forms!F_Daily_overall_Time_Sheet!txtYearMonth" & "ConHoursDate<Forms!F_Daily_overall_Time_Sheet!ConHoursDate")

I am using DLookup to give an overall month balance but this does not adust for dates earlier in the month, and I do need to calculate a running total for the month.

I am getting no value returned.

Thanks in advance

Matt

 

[KenHigg]

'&' is used to combine text strings. Use the 'and' operator to test for multiple conditions.

Instead of:

condition_one & condition_two

Use:

condition_one and condition_two

 

[matt beamish]

Thanks Ken. Doh - will give it a try when I am back at my desk tomorrow.

I was going to add that my control "txtyearmonth" is a value that I create by using "Year" and "Month" functions on the date field, and then combining those using "&" to make "txtyearmonth" so that 20/09/2008 becomes Sept 2008 becomes 200809 and October 200810. Access seems to be able to evaluate these even though I think that they must be numeric strings and not integers....can anyone put me right? Is this going to remain stable?

Thanks again,

Matt

 

[matt beamish]

I now think that I need to use DSUM to create my running balance.

I have used

=DSum("RunningBalance","Q_Running_Balance","txtYearMonth=Forms!F_Daily_overall_Time_Sheet!txtYearMonth" And "ConHoursDate<Forms!F_Daily_overall_Time_Sheet!ConHoursDate")

but this is giving me an overall total from the query rather than selecting the values by my criteria.

PS On adding a control to test txtyearmonth

= txtyearmonth * 2

I get a correct result so I guess my txtyearmonth is sufficiently numeric.

Any suggestions?

Thanks

Matt

 

[matt beamish]

I have got round the problem by setting up two dsums on my form, running them through Nz functions, and then deducting one from the other in another control - this gives me the running sum that I was looking for. There is a little lag as it is calc.d, but it works.

Matt