sql in code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

terry - 28 Feb 2005 20:09 GMT

Can I write an sql statement directly in code or do I have to use the query
designer?

If I can write it in code, what is the line continuation charater for vb?

If I can't write it in code (even if I can) how can I call a query written
in the query wizard and pass parameters to the query?

Reply to this Message

Dan Artuso - 28 Feb 2005 20:50 GMT

Hi,
Yes you can write a query in code. The line continuation character is _

What kind of query are you talking about? A simple Select query?
Do you want to open a recordset against the query or simply display the results
as if you had opened it from the Queries tab?

If So:

Dim strSql as String

strSql = "Select * From yourTable"
DoCmd.OpenQuery strSql

Signature

HTH
-------
Dan Artuso, MVP

> Can I write an sql statement directly in code or do I have to use the query
> designer?
[quoted text clipped - 3 lines]
> If I can't write it in code (even if I can) how can I call a query written
> in the query wizard and pass parameters to the query?

Reply to this Message

terry - 28 Feb 2005 22:05 GMT

thanks,
I want to write two queries and display the results of the second query.

thesehours is the number of hours that my function returns based on fields
in a dialog box.

I want to use these hours in a query to determine if an employee can add
[thesehours] to their schedule without going over 40 hours. Then I need to
run another query to find which employee (of the set of employees that was
returned by the first query, has the specific hours/days available to work.

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)

SELECT Employee![EMP Last Name] & ", " & Employee![EMP First Name] & " " &
Employee![EMP Middle Name] AS Employee, 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 Last Name] & ", " & Employee![EMP First Name] & " " &
Employee![EMP Middle Name], 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]))+[thesehours])<=40))
ORDER BY Employee![EMP Last Name] & ", " & Employee![EMP First Name] & " " &
Employee![EMP Middle Name];

> Hi,
> Yes you can write a query in code. The line continuation character is _
[quoted text clipped - 17 lines]
> > If I can't write it in code (even if I can) how can I call a query written
> > in the query wizard and pass parameters to the query?

Reply to this Message

terry - 28 Feb 2005 22:07 GMT

Sorry, I didn't finish. I then want to show the results in a query window.

Reply to this Message

Dan Artuso - 01 Mar 2005 21:25 GMT

Hi,
So, put that long SQL statement into a variable and then run the query.
Something like:

strSql = "SELECT Employee![EMP Last Name] & ", " & Employee![EMP First Name] & " " & _
"Employee![EMP Middle Name] AS Employee, 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 Last Name] & ", " & Employee![EMP First Name] & " " & _
"Employee![EMP Middle Name], 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]))+ " & thesehours & ")<=40)) " & _
"ORDER BY Employee![EMP Last Name] & ", " & Employee![EMP First Name] & " " & _
"Employee![EMP Middle Name];"

Then:
Debug.Print strSql

and you can examine the string and make corrections where necessary.

Signature

HTH
-------
Dan Artuso, MVP

> Sorry, I didn't finish. I then want to show the results in a query window.
>
[quoted text clipped - 19 lines]
> > > If I can't write it in code (even if I can) how can I call a query written
> > > in the query wizard and pass parameters to the query?

Reply to this Message