Dawn-
Although Access JET has technically supported a query embedded in a FROM
clause since Access 97, it generates a crazy "non standard" bracket syntax
when you save the SQL. And the parser has a bug that's never been fixed
that causes it to fail if any brackets are embedded within the query (your
Forms![Device Master]!equip_num in this case). If the Device Master form is
open when you generate this, try doing a direct substitution in code rather
than use the parameter:
strSQL = "SELECT CalHist.equip_num, CalHist.cal_date, CalHist.auto_adjust,
" & _
"CalHist.as_found, CalHist.cal_interval " & _
"FROM (SELECT TOP 3 CH2.equip_num, CH2.cert_num, CH2.cal_date FROM
CalHist AS CH2 " & _
"WHERE (((CH2.equip_num) = " & Forms![Device Master]!equip_num & ")) " &
_
"ORDER BY CH2.cert_num DESC) AS Q3 " & _
"INNER JOIN CalHist ON (Q3.cert_num=CalHist.cert_num) AND " & _
"(Q3.equip_num=CalHist.equip_num) " & _
"WHERE (((CalHist.equip_num)= " & Forms![Device Master]!equip_num & "))"
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf
Of Crosier, Dawn
Sent: Tuesday, November 22, 2005 12:06 PM
To: [email protected]
Subject: [ms_access] Help with SQL statement in Code
I am having trouble taking a working query and converting it to code so that
I can use it in my function.
The following is the working query:
SELECT CalHist.equip_num, CalHist.cal_date, CalHist.auto_adjust,
CalHist.as_found, CalHist.cal_interval FROM [SELECT TOP 3 CH2.equip_num,
CH2.cert_num, CH2.cal_date FROM CalHist AS CH2 WHERE
(((CH2.equip_num)=Forms![Device Master]!equip_num)) ORDER BY CH2.cert_num
DESC]. AS Q3 INNER JOIN CalHist ON
(Q3.cert_num=CalHist.cert_num) AND (Q3.equip_num=CalHist.equip_num) WHERE
(((CalHist.equip_num)=Forms![Device Master]!equip_num));
The following is the code I am trying to use:
Function CalibInt()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Integer
Dim CalibInterval As Integer
strSQL = "SELECT CalHist.equip_num, CalHist.cal_date, CalHist.auto_adjust, "
& _
"CalHist.as_found, CalHist.cal_interval " & _
"FROM [SELECT TOP 3 CH2.equip_num, CH2.cert_num, CH2.cal_date FROM
CalHist AS CH2 " & _
"WHERE (((CH2.equip_num) = Forms![Device Master]!equip_num)) " & _
"ORDER BY CH2.cert_num DESC]. AS Q3 " & _
"INNER JOIN CalHist ON (Q3.cert_num=CalHist.cert_num) AND
(Q3.equip_num=CalHist.equip_num) " & _
"WHERE (((CalHist.equip_num)=Forms![Device Master]!equip_num))"
Debug.Print strSQL
End Function
The error I get when I take the results from the Immediate Window and
attempt to create a new query - for testing purposes is:
"Invalid bracketing of name 'SELECT TOP 3 CH2.equip_num, CH2.cert_num,
CH2.cal_date FROM CalHist as CH2 WHERE (((CH2.equip_num)= Forms![Device
Master' "
Which leads me to believe that it got confused with the bracketing that
Access put on the Select statement, I did not get an further when I
attempted to surround the sub query in parenthesis - so if you can give me
some help, I would greatly appreciate it!
Dawn Crosier
Application Specialist
"Education Lasts a Lifetime"
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor --------------------~--> Get
fast access to your favorite Yahoo! Groups. Make Yahoo! your home page
http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/q7folB/TM
--------------------------------------------------------------------~->
Yahoo! Groups Links
------------------------ Yahoo! Groups Sponsor --------------------~-->
Get fast access to your favorite Yahoo! Groups. Make Yahoo! your home page
http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/q7folB/TM
--------------------------------------------------------------------~->
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
