need to write query in code, getting error

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

Guest

Thanks in advance to anyone who can help me...
I'm trying to write a query in code and am getting the message that "A
runsql action requires an argument containing an sql statement.

I used the query designer to create the query, then copied the sql statement
into code to the var strqryCanWork, TheseHours is a long that is returned by
another function I have for the number of hours based on a given set of
parameters. CalcHours() works fine. The query works fine when run from the
query designer, the problem with the query designer is that I have thesehours
as a parameter and that's not how I want it.. But here I get an error.

here is my code...

thesehours = CalcHours(Me!txtEnd.Value - Me!txtStart.Value,
Me!chkMonday.Value, Me!chkTuesday.Value, Me!chkWednesday.Value,
Me!chkThursday.Value, Me!chkFriday.Value, Me!chkSaturday.Value,
Me!chkSunday.Value, Me!chkEveryOther.Value)

strqryCanWork = "SELECT Sum(CalcHours([SCH End Time]-[SCH Start
Time],[SCH Monday],[SCH Tuesday]," & _
"[SCH Wednesday],[SCH Thursday],[SCH Friday],[SCH
Saturday],[SCH Sunday]," & _
"Schedule![SCH EveryOtherWeekend])) AS Hours,
Employee.[EMP-PK Emp ID]" & _
"FROM Employee INNER JOIN Schedule ON Employee.[EMP-PK
Emp ID] = Schedule.[SCH-FK Emp ID]" & _
"GROUP BY Employee.[EMP-PK Emp ID], Schedule.[SCH
Calculate Emp Hours]" & _
"HAVING (((Schedule.[SCH Calculate Emp Hours])=Yes) AND"
& _
"((Sum(CalcHours([SCH End Time]-[SCH Start Time],[SCH
Monday]," & _
"[SCH Tuesday],[SCH Wednesday],[SCH Thursday],[SCH
Friday]," & _
"[SCH Saturday],[SCH Sunday],[Schedule]![SCH
EveryOtherWeekend]))+[addhours])<=40));"

DoCmd.RunSQL strqryCanWork
 
SQLStatement Required Variant. A string expression that's a valid SQL
statement for an action query or a data-definition query. It uses an INSERT
INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE,
CREATE INDEX, or DROP INDEX statement. Include an IN clause if you want to
access another database.


Your SQL is not an action query, it is a SELECT.
So you should not use DoCmd.RunSQL.

Maybe you just need to open a recordset instead.
Then your SQL string should work.
 
Back
Top