Good Ole DSUM()

[wyvern621]

Hi Everyone...I'm using Access 2003...

I have a table called "Transaction":

Hours (Number)
Qtr (Text)
Date (Date/Time)
Analyst (Text)
Customer (Text)
trid (Autonumber)
Comments (Memo)
Activity (Text)

I have a form called "frmTransactions":

Analyst (Combo Box)
Customer (Combo Box)
Activity (Combo Box)
Date (Text Box)
Hours (Text Box)
Qtr (Combo Box)
Comments (Text Box)
Add Record (Command Button)
Exit Application (Command Button)

The way this works is as Analysts add hours for their customers on the form frmTransactions, it updates the Transaction table. That works fine.

What I would like to do is have a text box that would sum the number of hours for the previous month per Analyst based on the name selected from the drop down box for "Analyst" on the form frmTransactions.

Here's a sample of what I want:

Analyst: Keith Smith
Customer: KMart
Activity: Add
Date: 7/28/2007
Hours: 0.25
Qtr: 2nd
Comments:

Total Hours for the Month of July: 100

I've tried this:

=DSUM("[HOURS]", "TRANSACTION", "[FORMS!frmTRANSACTIONS.ANALYST] AND [DATE] Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)")

and this:

=DSum("[HOURS]","TRANSACTION","[ANALYST]='" & ME.[ANALYST] &"'"AND [DATE] Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)")

and this:

=DSum("[HOURS]","TRANSACTION","[ANALYST]=" & ME.ANALYST & " AND [DATE] Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)")

I even created a temp table where I have just the analyst and the hours and tried just plain this:

=DSum("[HOURS]","Temp Table","[ANALYST]=" & Forms!frmTransactions.ANALYST)

I put the formula as the control source of the Text Box and got the dreaded "#Error" error.

What am I not doing?

Thanks in advance for your help..

ChiTownDivaus

 

[Remou]

You seem to be missing single quote delimiters.

[tt]=DSum("[HOURS]","TRANSACTION","[ANALYST]='" & ME.ANALYST & "' AND [DATE] Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)")[/tt]

 

[djayam]

Hi Wyvern621,

Try this in the AfterUpdate event of your Analyst combo box. I will call your textbox for the hours "ctl_Hours" and your combo box for the Analyst "Combo_Analyst":

[COLOR=green]'set variables [/color]
Dim vCurrentMonth as Long
Dim vCurrentYear as Long
Dim vAnalyst as String
Dim vHours As Long

vCurrentMonth = Month(Now())
vCurrentYear = Year(Now())
vAnalyst = Me.Combo_Analyst

[COLOR=green]'calculate hours [/color]
vHours = DSum("Hours", "Transaction", "Month(Date) = " & vMonth & " AND Year(Date) = " & vCurrentYear & " AND Analyst = " & vAnalyst)

[COLOR=green]'set Hours control [/color]
ctl_Hours = vHours

Make sure the bound column in your combo box is the column with the analyst string in it. (Really it should be AnalystID with a relationship to an Analyst table...)


Good luck,

Jason

 

[wyvern621]

Thanks for responding Remou...

Now I'm getting the dreaded #Name? error.

 

[djayam]

Oops! Just realised you wanted the PREVIOUS month! Update code with:

vCurrentMonth = Month(Now())-1

 

[Remou]

Oops. Get rid of 'Me'

[tt]=DSum("[HOURS]","TRANSACTION","[ANALYST]='" & ANALYST & "' AND [DATE] Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)")[/tt]

Then check the names of everything.

You have used reserved words very freely, it is not a good idea.

 

[wyvern621]

Hi Djayam...

Thanks for the response...yes the Analyst combo box is tied to an Analyst table.

I'm getting a

"Run-Time Error '3075':

Sytax error (missing operator) in query expression 'Month(Date)= AND Year(Date)=2007 AND Analyst='Keith Smith'."

on this line:

vHours = DSum("Hours", "Transaction", "Month(Date) = " & vMonth & " AND Year(Date) = " & vCurrentYear & " AND Analyst = " & vAnalyst)

ChiTownDivaus

 

[wyvern621]

Remou...

Now it's adding all of the hours for all of the Analyst, no matter what Analyst I pick from the combo box. I even removed the date criteria and it's still giving me a total sum.

ChiTownDivaus

 

[djayam]

Oops - sorry my code has a typo!! I have called it vMonth but defined the variable as vCurrentMonth. Replace vMonth with vCurrentMonth in the DSum...

 

[wyvern621]

Remou...

Sorry...I think I looked at this for way too long...

It'w working...

Thanks everybody for your help...


ChiTownDivaus

 

[Remou]

Check the contents of the combobox using, say, a messagebox and test with a name.

[tt]=DSum("[HOURS]","TRANSACTION","[ANALYST]='wyvern621'")[/tt]

 

[djayam]

How did you get this working in the end?

If you used my code be careful of January!

vCurrentMonth = Month(Now())-1 will equal -1!!

If Month(Now()) = 1 THEN vCurrentMonth = 12 Else vCurrentMonth = Month(Now())-1

should do the trick...