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