Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select records in form and sum 1

Status
Not open for further replies.
GaryAks

GaryAks

IS-IT--Management
Feb 18, 2002
13
US
In the past I used Paradox for dos with great success, but am switching to access and am having problems with the following. If there are any good access manuals (I have purchased a few) that have examples of code similar to the example below, the title of same would be helpfull.
This is a database I for employee cost tracking. There are usually about 2-5 records for each day for each employee.
As I am entering a new record, and I leave the employee name field, I need access to find all the records in the current table for the same employee, with the same date, and display in a dialogue box the total hours for that employee, entered so far. If I am entering the first record for a given date for a particular employee it would return 0. Any help with this would be greatly appreciated. I have a feeling it's going to take MUCH longer to become proficient with access, than it did Paradox for dos. Thanks for your consideration.
 
Sort by date Sort by votes
In the AfterUpdate Event for your EmployeeName field put this code (you may need to modify the names a little).

Dim myVal as Single
myVal = DSum("AmountField","TableName", "[EmployeeName] = '" & Me.EmployeeName & "' And [DateField] = #" & Date() & "#")
MsgBox "Totals for " & Me.EmployeeName & " for today are " & IIf(IsNull(myVal),0,myVal)

Paul
 
Upvote 0 Downvote
Paul
Thank you for the information I will let you know how it works.
Your input is greatly appreciated
Gary
 
Upvote 0 Downvote
Paul
The modifyed version
Dim myVal as Single
myVal = DSum("EmpHrs", "tblRanchCost","[EmpName] = "& Me.EmpName & " and [Date] = #" & Date() & "#")
MsgBox "Totals for " & Me.EmpName & " for today are " & llf(IsNull(myVal),0,myVal)

When I run it I get the error
Run time error '3075'
Syntax error (missing operator) in query expression
'[EmpName] = <actual employee from record was here> and [Date] = #5/15/2002#'.
I tried numerous syntax changes with no success, any other ideas?
Thanks again for your time
Gary

 
Upvote 0 Downvote
If the name of your field is [Date] then that's probably the problem. Date is a reserved word for Access. You could change this line of code to read

myVal = DSum(&quot;EmpHrs&quot;, &quot;tblRanchCost&quot;,&quot;[EmpName] = &quot;& Me.EmpName & &quot; and [Date] = '&quot; & Format(Now(),'mm/dd/yy') & &quot;'&quot;)

I haven't tested this but it should work. When I have a minute I'll test it out myself.

Paul
 
Upvote 0 Downvote
Paul
I changed the Date field to RecDate field and recieved a compile syntax error at the apostrophe before the mm/, access highlighted that character. Do you have any ideas?

myVal = DSum(&quot;EmpHrs&quot;, &quot;tblRanchCost&quot;,&quot;[EmpName] = &quot;& Me.EmpName & &quot; and [RecDate] = '&quot; & Format(Now(),'mm/dd/yy') & &quot;'&quot;)

Thank you for your help it's is greatly appreciated
Gary
 
Upvote 0 Downvote
You might try putting a &quot; instead of a ' at both ends of mm/dd/yy like this &quot;mm/dd/yy&quot;. It may technically be outside the string so you don't have to use the apostrophe. I've misplaced my original test sample so that would be my guess for a syntax error.

Paul
 
Upvote 0 Downvote
Paul
I change the ' for &quot; and now the error I get is
&quot;Run-time error '3075' Syntax error( missing operator) in query expression '[EmpName]= <actual employee returned here> and [RecDate] = '01/22/03'.
This is the line of code --------------------------
myVal = DSum(&quot;EmpHrs&quot;, &quot;tblRanchCost&quot;, &quot;[EmpName] = &quot; & Me.EmpName & &quot; and [RecDate] = '&quot; & Format(Now(), &quot;mm/dd/yy&quot;) & &quot;'&quot;)

I have tried various changes but no cigar, any other ideas?

Gary
 
Upvote 0 Downvote
Well that tells me more. It doesn't seem to like the date in single quotes. Try this.

myVal = DSum(&quot;EmpHrs&quot;, &quot;tblRanchCost&quot;, &quot;[EmpName] = &quot; & Me.EmpName & &quot; and [RecDate] = #&quot; & Format(Now(), &quot;mm/dd/yy&quot;) & &quot;#&quot;)

The pound sign # delimits dates, but the Format function returns the date as a string. If that doesn't work then try this

myVal = DSum(&quot;EmpHrs&quot;, &quot;tblRanchCost&quot;, &quot;[EmpName] = &quot; & Me.EmpName & &quot; and [RecDate] = #&quot; & CDate(Format(Now(), &quot;mm/dd/yy&quot;)) & &quot;#&quot;)

The CDate Function turns any legal date value into a date datatype.

Paul
 
Upvote 0 Downvote
01-23-2003 09:00 AM
Paul
I tried the changes with the results listed below. The error repeats (missing operator), being new to the access syntax I am not sure what is ment by (missing operator). Any more ideas?
Thank you for your efforts
Gary

Before the changes
myVal = DSum(&quot;EmpHrs&quot;, &quot;tblRanchCost&quot;, &quot;[EmpName] = &quot; & Me.EmpName & &quot; and [RecDate] = '&quot; & Format(Now(), &quot;mm/dd/yy&quot;) & &quot;'&quot;)
---------------------------------------
This is the first suggestion
myVal = DSum(&quot;EmpHrs&quot;, &quot;tblRanchCost&quot;, &quot;[EmpName] = &quot; & Me.EmpName & &quot; and [RecDate] = #&quot; & Format(Now(), &quot;mm/dd/yy&quot;) & &quot;#&quot;)
returned this error
Run-time error '3075':
Syntax error (missing operator) in query expression '[EmpName] = <actual employee name returned here> and [RecDate] = #1/23/2003?#'.
----------------------------------------
The second suggestion
myVal = DSum(&quot;EmpHrs&quot;, &quot;tblRanchCost&quot;, &quot;[EmpName] = &quot; & Me.EmpName & &quot; and [RecDate] = #&quot; & CDate(Format(Now(), &quot;mm/dd/yy&quot;)) & &quot;#&quot;)
returned this error
Run-time error '3075':
Syntax error (missing operator) in query expression '[EmpName] = <actual employee name returned here> and [RecDate] = #1/23/2003?#'.
 
Upvote 0 Downvote
Yikes!!!. My brain must be working at the same speed as the server for TEK-TIPS. I've we've got EmpName looking for a number. This is from my first post.

myVal = DSum(&quot;AmountField&quot;,&quot;TableName&quot;, &quot;[EmployeeName] = ' &quot; & Me.EmployeeName & &quot; ' And [DateField] = #&quot; & Date() & &quot;#&quot;)

Notice the single quote/double quote combination around Me.EmployeeName. I put spaces in so you can see it better but we can't have them in the actual. Sorry, try this.

myVal = DSum(&quot;EmpHrs&quot;, &quot;tblRanchCost&quot;,&quot;[EmpName] = '&quot;& Me.EmpName & &quot;' and [Date] = #&quot; & Date() & &quot;#&quot;)

I was way to focused on the Date field.

Paul

 
Upvote 0 Downvote
Paul
Thank you ever so much, that GOT IT!!!!
Do you know of any manuals with code examples of these types of routines, or any sources on the net. I am just getting into programming access and some examples of similar code would really get me going.
Your time and help is greatly apprecated
Gary
 
Upvote 0 Downvote
Gary, anything by Getz and Litwin is going to be good stuff. They really are the best but it will cost a little extra. Then TEK-TIPS has some great people. There are a lot of FAQ's that will help address this kind of question.
Good luck.
Sorry for the Syntax runaround.

Paul
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Solved
Subform not updating in parent form
Replies
10
Views
681
Kennelbloke
Kennelbloke
caykamanj1966
  • Locked
  • Question
Duplicate Records in DataSheet View
Replies
5
Views
547
Andrzejek
Andrzejek
breezett93
  • Locked
  • Question
Function that find all controls on a form and puts their tab index in numerical order
Replies
19
Views
780
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Form Data Not Updating
Replies
6
Views
551
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Forcing Form Updates
Replies
2
Views
445
TrekBiker
TrekBiker

Part and Inventory Search

Sponsor

Back
Top