Display a value on a main form using a date range & DSum

[rookiedev]

Ok does anyone have their "Smarty Pants" on today? I have a really tough one this time..
I have a Main Form Employees-it has many tabs,,one of the Tabs tracks our Labor for timesheets.So..I have a Sub Form called Time. It displays as a datasheet because we need to see what hours have been put in for the week.

Here's my problem: I need to display a field that will calculate the number of hours worked for the current week as we go along. It looks like this:
Date Job# RateCode:HrsWorked LCode TAG

1/2/02 648 55-100 8 01 REGULAR HOURS
1/3/02 648 55-100 8 01 REGULAR HOURS
1/4/02 648 55-100 8 01 REGULAR HOURS
1/7/02 648 55-100 8 01 REGULAR HOURS
1/8/02 648 55-100 8 01 REGULAR HOURS

Our work week starts on Wednesday and ends on Tuesday.To display these dates on the Employees Main Form I have 2 unbound text boxes that are filled by this code:
Private Sub Form_Open(Cancel As Integer)
Me![Beginning Date] = Date - WeekDay(Date, 4) + 1
Me![Ending Date] = Me![Beginning Date] + 6
End Sub
So.. For this week the Beginning Date=1/1/03 And the Ending Date=1/7/03. I feel like I have tried everything I know to use these dates to display a total from the Time table for the Employee for that date range.

Can anyone help me with this or is it just impossible to do???

Much thanks to anyone willing to tackle this one!!

RookieDev

 

[PWise]

did you try
dsum("HrsWorked ","queryname",date Between #" & Me![Beginning Date] & "# And #" & Me![Ending Date] & "#&quot

 

[rookiedev]

Pwse-
I put in the formula using the Table name "Time" and nothing displays at all. Any idea why? The field is on the Main Form Employees and the data I want to collect is in the subform Time.
RookieDev

 

[PWise]

you mention a table time and a subform time useing the dsum funtion you only use a table or query name
put this in the on update of the time subform
me.parent.txtboxtotalhrsworked=dsum("HrsWorked ","queryname",date Between #" & Me![Beginning Date] & "# And #" & Me![Ending Date] & "#&quot

 

[rookiedev]

OK, I'm a little confused....The time subform is based on the Table-Time. What do I put in as the queryname? I don't have a query. And don't I have to refer to the Form name of Beginning Date/Ending Date since it is on the Employees form?
Ughhhh.......
RookieDev

 

[PWise]

if the hrsworked field is in the time - table you dont have to refer to a query just to the table. This will give all the hours worked if you want just for 1 project or 1 employee you will have to limit it.
me.parent.txtboxtotalhrsworked=dsum("HrsWorked ","queryname",date Between #" & Me![Beginning Date] & "# And #" & Me![Ending Date] & "# and job#=" me.job#)
if the ![Beginning Date] and ![Ending Date] are in the main form you will have to reference the as Me.parent![Beginning Date]
even if the Beginning Date] and ![Ending Date] are in the sub form it is still a good idea to reference them with me

 

[rookiedev]

I added this to the AfterUpdate event of the Time Subform:

Me.Parent.totalhrsworked = DSum("HrsWorked", "Time", Date, "#Between#" & Me.Parent![Beginning Date] & "# And #" & Me.Parent![Ending Date] & "#" & "# And EmployeeID=& Me.Parent![EmployeeID]&quot

Then I get this message:
Compile Error
Wrong number of Arguments or invalid property assignment
then DSum is highlighted
Any idea why?

RookieDev

 

[PWise]

should be
Me.Parent.totalhrsworked = DSum("HrsWorked", "Time", "Date Between #" & Me.Parent![Beginning Date] & "# And #" & Me.Parent![Ending Date] & "# And EmployeeID=& Me.Parent![EmployeeID]&quot

 

[rookiedev]

Now when I enter information into the Time subform I get this message:

Runtime error 3075- Syntax error missing operator in query expression 'Date Between # 1/8/03#And #1/14/03And EmployeeID=&Me.Parent![EmployeeID]'

The whole expression is highlighted on Debug....

We are almost there with this I can feel it I just don't know what is wrong. Does 'Date need to be stated before Ending Date or removed all together?????

I copied and pasted what you said it should be directly into my form so I know I didn't mistype it.

What do you think?????
RookieDev

 

[PWise]

did you paste this line to tek tips
Runtime error 3075- Syntax error missing operator in query expression 'Date Between # 1/8/03#And #1/14/03And EmployeeID=&Me.Parent![EmployeeID]'
in the above line i see these errors missing "#" after todate

Me.Parent.totalhrsworked = DSum("HrsWorked", "Time", "Date Between #" & Me.Parent![Beginning Date] & "# And #" & Me.Parent![Ending Date] & "# And EmployeeID=& Me.Parent![EmployeeID]&quot

 

[rookiedev]

Now it seems to have a problem with the Parent.EmployeeID line here's what I get now that I used your last suggestion.

Runtime error 3075- Syntax error missing operator in query expression 'Date Between # 1/8/03#And #1/14/03#And EmployeeID=&Me.Parent![EmployeeID]'


Boy this Syntax business is tricky isn't it?!

I tried to add #" after the And and everything turned RED so I backed it out again.

RookieDev

 

[PWise]

please paste a copy of your code that i can review your whole code
try putting quote after EmployeeID=
this was my error
Me.Parent.totalhrsworked = DSum("HrsWorked", "Time", "Date Between #" & Me.Parent![Beginning Date] & "# And #" & Me.Parent![Ending Date] & "# And EmployeeID=" & Me.Parent![EmployeeID]&quot

is EmployeeID a text field then you need
'Date Between # 1/8/03#And #1/14/03#And EmployeeID='" = & Me.Parent![EmployeeID] & "'"

 

[rookiedev]

Does the totalhrsworked have to be a field in the Employees "Table"? It is currently an unbound text box with no control or row source on the Employees "Form"
Just thought this may be a factor. I removed the &Me. and then when I placed the pointer over the fields they showed the employee number. I saved it and tested the data entry on the time subform. I did not get any error messages but no value was displayed in the totalhrsworked field even after I did a refresh on the records. Let me know if this is a factor.
Thanks,
RookieDev

 

[rookiedev]

Oh I forgot to past in my code:
Me.Parent.totalhrsworked = DSum("HrsWorked", "Time", "Date Between #" & Me.Parent![Beginning Date] & "# And #" & Me.Parent![Ending Date] & "# And & EmployeeID&=Parent![EmployeeID]&quot

EmployeeID is a number field in Time and the Autonumber field in Employees.

Thanks!
RookieDev

 

[PWise]

i would put this in the on current event of the sub form

 

[rookiedev]

When I add the " in after EmployeeID= I get an error expected list separator or )

Code won't run like this:
Me.Parent.totalhrsworked = DSum("HrsWorked", "Time", "Date Between #" & Me.Parent![Beginning Date] & "# And #" & Me.Parent![Ending Date] & "# And EmployeeID=" & Me.Parent![EmployeeID]&quot

Something really ugly just happened!!!!!!My sub form is blank and Access is saying it can't find the macro name
WHAT DID I DO?????? HOW DO I MAKE IT COME BACK?????

HELP!!!!!!!!
RookieDev

 

[PWise]

no need to add " after Me.Parent![EmployeeID]
Me.Parent.totalhrsworked = DSum("HrsWorked", "Time", "Date Between #" & Me.Parent![Beginning Date] & "# And #" & Me.Parent![Ending Date] & "# And EmployeeID=" & Me.Parent![EmployeeID])

 

[rookiedev]

Okay, I did that and now when I try to open the Employee form I get this error message:
Run-time error '3075':
Syntax error (missing operator in query expression 'Date Between ## And ## And & EmployeeID=65'.
This is exactly how my code looks right now:

Me.Parent.totalhrsworked = DSum("HrsWorked", "Time", "Date Between #" & Me.Parent![Beginning Date] & "# And #" & Me.Parent![Ending Date] & "# And & EmployeeID=" & Me.Parent![EmployeeID])

I don't know about you, but I'm beginning to think that this just isn't possible using DSum.

Thanks for your help on this you are probably loosing patience with this whole thing by now. I appreciate you hanging in there!

RookieDev

 

[PWise]

i am suprised that you still have patience for me.
please hang in there i have done this many times
where did you put this line of code. this much i can see that the date txtboxes dont have values yet might be a tab order question. lets play a bit whit this put a command botton on the main form and in the on click event put

Me.totalhrsworked = DSum("HrsWorked", "Time", "Date Between #" & Me![Beginning Date] & "# And #" & Me.![Ending Date] & "# And & EmployeeID=" & Me.![EmployeeID])
let me know what you get

 

[rookiedev]

pwise-
I found a solution that works...I created a query that would use the values of the Employees Date Fields and containing the hrs worked field. I saved that-Weekly time Query- then created another query-"Weekly Hrs query- using the first query's results that would simply Sum the hrs worked field.
I opened Employees in design view and added a list box using the Weekly Hrs query to be the lookup source. Then I created a command button with this code ON CLICK-

Private Sub cmdacCmdRefresh_Click()
On Error GoTo cmdacCmdRefresh_Click_Error

DoCmd.RunCommand acCmdRefresh

Exit_cmdacCmdRefresh_Click_Error:

Exit Sub

cmdacCmdRefresh_Click_Error:

If Err = 2046 Or Err = 2501 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_cmdacCmdRefresh_Click_Error
End If

End Sub
I labeled the button Weekly Hours
Now when this button is clicked the hour for that employee for the week are displayed in the list box.

I was hoping to not require any user intervention but I think we both were reaching the end of our rope with DSum and looking toward a command button solution at this point anyway.
Beside now the user can input any range they want and get a value. It's not the slickest solution but it works.

Thanks for hanging in there with me!
RookieDev