Select sum() query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

the following statement

strSQL = "select sum(leavehours) as retval from tblempleave where empid = " & Me.boxEmpID & " and leavetype = " & Me.boxLeaveType & "

returns a strSQL value of "select sum(leavehours) as retval from tblempleave where empid = 000001111 and leavetype = s

DoCmd.RunSQL strSQ

returns a runtime error '2342': A RunSQL action requires an argument consisting of an SQL statement

what am I missing here

tia
JMorrell
 
Well, the DoCmd.RunSQL is meant to execute action queries,
INSERT, DELETE,UPDATE queries.

What do you want to do with the sum?
Also, your SQL statement is wrong. Is EmpID a numeric?
It looks like a text with the leading zeros. If it's
numeric, leave that part alone, otherwise:

strSQL = "select sum(leavehours) as retval from
tblempleave where empid = '" & Me.boxEmpID & "' and
leavetype = '" & Me.boxLeaveType & "'"

Eitherway, LeaveType is text and needs quotes around the
value.

Have checked into the DSUM function?

dim sngLeave as Single
sngLeave = DSum("LeaveHours","tblEmpLeave","empid = '" &
Me.boxEmpID & "' and leavetype = '" & Me.boxLeaveType
& "'")


Chris


-----Original Message-----
the following statement:

strSQL = "select sum(leavehours) as retval from
Click to expand...
tblempleave where empid = " & Me.boxEmpID & " and
leavetype = " & Me.boxLeaveType & ""
returns a strSQL value of "select sum(leavehours) as
Click to expand...
retval from tblempleave where empid = 000001111 and
leavetype = s"
DoCmd.RunSQL strSQL

returns a runtime error '2342': A RunSQL action requires
Click to expand...
an argument consisting of an SQL statement.
 
----- Chris wrote: ----

Well, the DoCmd.RunSQL is meant to execute action queries,
INSERT, DELETE,UPDATE queries

What do you want to do with the sum
Also, your SQL statement is wrong. Is EmpID a numeric?
It looks like a text with the leading zeros. If it's
numeric, leave that part alone, otherwise

strSQL = "select sum(leavehours) as retval from
tblempleave where empid = '" & Me.boxEmpID & "' and
leavetype = '" & Me.boxLeaveType & "'

Eitherway, LeaveType is text and needs quotes around the
value

Have checked into the DSUM function

dim sngLeave as Singl
sngLeave = DSum("LeaveHours","tblEmpLeave","empid = '" &
Me.boxEmpID & "' and leavetype = '" & Me.boxLeaveType
& "'"


Chri


-----Original Message----
the following statement
Click to expand...
tblempleave where empid = " & Me.boxEmpID & " and
leavetype = " & Me.boxLeaveType & "retval from tblempleave where empid = 000001111 and
leavetype = s
JMorrel
Click to expand...

I'll be checking the retval against other values. empid is a text field (SSN)
Can't the DoCmd.RunSQL just return a value based on the query

the dsum() works for what I want. Thank you. But I'm still curious...
 
JMorrell said:
I'll be checking the retval against other values. empid is a text field (SSN).
Can't the DoCmd.RunSQL just return a value based on the query?

the dsum() works for what I want. Thank you. But I'm still curious...
Click to expand...

No, it can't. As Chris mentioned, RunSQL is only for Action queries.
 
Back
Top