[IowaGuy]

I have a form in Access2003 called Daily_Log

The form is tied to the Daily_Log table.

On the form, I have a text box called Log_Date.
I also have a text box called Count_Logged with a button next to it called cmdGetLog

What I am trying to do is this:

When a user enters in a date in the Log_Date field, then clicks the cmdGetLog button, the program connects to the Job table and sums up the documents, Addl_pages and Wasted_paper columns based on what the user put in for the Log_Date.

Thanks!! :)

[IowaGuy]

Private Sub cmdGetLog_Click()
On Error Go to Err_cmdGetLog_Click

Job.RecordSource = "Select document from job"

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, acMenuVer70

Exit_cmdGetLog_Click:
Exit Sub

Err_cmdGetLog_Click:
MsgBox Err.Description
Resume Exit_cmdGetLog_Click

End Sub

[ScottGem]

VB and VBA are different things. I'm assuming you are working within Access so I moved this to the Access forum.

What I think you want is the DSUM function. For example you could set the Controlsource of a text ox to:

=DSUM("[Addl_Pages]","tblJobs","[LogDate] = #" & [txtLogDate] & "#")

[IowaGuy]

A dsum might be a better solution.

I wrote this based off your suggestion.
=DSum("[Addl_Pages]+[documents]+[Wasted_Paper]","Jobs","[Print_Date] = #" & [Log_Date] & "#")

Now, this will sum the Addl_Pages, documents and Wasted_Paper from the table called job if the print date = the log_date.
Right?

I get the dreaded #Error.

The form this is on is pointed to a table called Daily_Log and the dsum is pulled from the job table. Am I missing anything like the table!job!

[ScottGem]

Make sure your object names are correct. I would also use three DSums and add the results together rather than try to add the three fields in the one DSum. An alternative would be to have a query like:

SELECT [Addl_Pages]+[documents]+[Wasted_Paper] AS TotalPages
FROM Jobs
WHERE [Print_Date] = Forms!formname![Log_date];

then use a DSum on the totalPages column of that query.

[IowaGuy]

Hi Scott,

Ok.. I went the route of a button on this. What happens when the button is clicked, is that a query pops up. I would rather have the results of that query (1 column with 1 sum) show up in the text box next to the button. How does this work?

Here is my vb6 code.
-------------------------------------------
Private Sub cmdDiff_Click()
On Error Go to Err_cmdDiff_Click

Dim stDocName As String

stDocName = "qryClicks_from_Job"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdDiff_Click:
Exit Sub

Err_cmdDiff_Click:
MsgBox Err.Description
Resume Exit_cmdDiff_Click

End Sub

--------------------------------------

I need the sum to qryClicks_from_Job to populate my text box called Count_Logged

:)
Thanks

[ScottGem]

What does the SQL of the query look like? How many records and columns does it return?

[IowaGuy]

Got it. Thanks again!