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
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