Help with dlookup

[david.paton]

Hi,

I have a problem with a form I am making called frmSearch. Whenever I try and run it, I get an error message "#Error" that keeps flashing in the text field next to Supervision hours and I have looked and looked but I can't find where it is coming from. The field has dlookup in the control source.

Can someone help me please? My database is attached.

Thanks,
Dave

 

[david.paton]

I have been fiddling around with it and now can get that part to work, but I have found different problems. On the right hand side where it says "Total hours by TC location", dlookup doesn't appear to place the summed figured in the text boxes.

The queries behind them do not appear to be working which is strange as I thought I copied the same logic as the ones on the left.

The dates I have some data entered is from 01/05/2011 to 06/05/2011 and there is some data entered in for heather.


I have attached my working file.

 

[JHB]

By me it works okay, if I choose the only location which has data in, ("Orange")

 

[david.paton]

Just got back from several weeks holidays and I am trying to work out where I left off. I made a few minor changes but then came to the search form (frmSearch). In the start date I put 1/05/2011 and in the finish date I put 5/05/2011. The total hours by worker (on the left side) appears to be working.

The problem is with the total hours by TC location (on the right). I enter the same dates and choose Orange and it gives me 5 supervision hours but if I look in the activity table, all of the Crisis support workers from Orange that have a ActivityTypeID of 3 (which refers the the activity of supervision hours), there are 2 entries for the date range (5 and 3 hours), giving a total of 8, not 5 as the dlookup says. Why hasn't it picked this up?

I have attached the DB below.

Thanks,
Dave

 

[david.paton]

I found the problem. I had the query to group results by the crisis support worker ID, so that is why it was returning the two separate figures, all I did was remove that column from the query and now it works.

 

[david.paton]

Another issue, if I leave the Start date and Finish date fields blank on frmSearch, it won't show anything. I want it to not apply a date filter if I leave the fields blank. How do I get it to not filter the dates and display unfiltered records when I leave the date fields blank? See the above attachment (TCS Database v3)