Select sum() query

[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

 

[Chris]

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

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 strSQL

returns a runtime error '2342': A RunSQL action requires

an argument consisting of an SQL statement.

 

[Guest]

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

tblempleave where empid = " & Me.boxEmpID & " and
leavetype = " & Me.boxLeaveType & "retval from tblempleave where empid = 000001111 and
leavetype = s

JMorrel

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

 

[Douglas J. Steele]

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

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