VBA SQL syntax help 1

[tigersden]

Hi,
I am trying to get this query to run from VBA but keep getting "Expected end of statement error".
Within in this I am trying to add the string months to a value using "as" & I think I have got my & " messed up.

strSQL = "SELECT tblRisk.RiskRef, tblRisk.RiskDesc, tblrisk.ReviewFreq" & " Months" & " AS Review, tblReview.ReviewDT AS LastReviewed," _
& " tblAssessor.AssessorName, tblRisk.Process, tblRisk.Task, tblPersAtRisk.PersDesc, tblRisk.Comments," _
& " tblRisk.Method, tblRisk.ControlMeasure, tblRisk.Impact, tblRisk.Likelihood, tblSite.SiteDesc, tblDept.DeptDesc" _
& " FROM tblSite" _
& " INNER JOIN (tblDept INNER JOIN (((tblRisk INNER JOIN tblReview ON tblRisk.RiskRef = tblReview.RiskRef)" _
& " INNER JOIN tblAssessor ON tblReview.AssessorRef = tblAssessor.AssessorRef)" _
& " INNER JOIN tblPersAtRisk ON tblRisk.PersRef = tblPersAtRisk.PersRef) ON tblDept.DeptRef = tblRisk.DeptRef)" _
& " ON tblSite.SiteRef = tblRisk.SiteRef" _
& " WHERE "tblRisk.SiteRef =" & me.txtSiteRef & " AND tblRisk.RiskRef =" & Me.txtRiskRef


Thanks in advance.

 

[PHV]

Replace this:
strSQL = "SELECT tblRisk.RiskRef, tblRisk.RiskDesc, tblrisk.ReviewFreq" & " Months" & " AS Review, tblReview.ReviewDT AS LastReviewed," _
By this:
strSQL = "SELECT tblRisk.RiskRef, tblRisk.RiskDesc, tblrisk.ReviewFreq & ' Months' AS Review, tblReview.ReviewDT AS LastReviewed," _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tigersden]

Hi PHV,
Thanks for the info (yet again!
I am now getting the error "Expected end of statement" on the very last line in bold below is the part highlighted by the debugger.
strSQL = "SELECT tblRisk.RiskRef, tblRisk.RiskDesc, tblrisk.ReviewFreq & ' Months' AS Review, tblReview.ReviewDT AS LastReviewed," _
& " tblAssessor.AssessorName, tblRisk.Process, tblRisk.Task, tblPersAtRisk.PersDesc, tblRisk.Comments," _
& " tblRisk.Method, tblRisk.ControlMeasure, tblRisk.Impact, tblRisk.Likelihood, tblSite.SiteDesc, tblDept.DeptDesc" _
& " FROM tblSite" _
& " INNER JOIN (tblDept INNER JOIN (((tblRisk INNER JOIN tblReview ON tblRisk.RiskRef = tblReview.RiskRef)" _
& " INNER JOIN tblAssessor ON tblReview.AssessorRef = tblAssessor.AssessorRef)" _
& " INNER JOIN tblPersAtRisk ON tblRisk.PersRef = tblPersAtRisk.PersRef) ON tblDept.DeptRef = tblRisk.DeptRef)" _
& " ON tblSite.SiteRef = tblRisk.SiteRef" _
& " WHERE "tblRisk.SiteRef =" & me.txtSiteRef & " AND tblRisk.RiskRef =" & Me.txtRiskRef

Tim

 

[PHV]

Replace this:
& " WHERE "tblRisk.SiteRef =" & me.txtSiteRef & " AND tblRisk.RiskRef =" & Me.txtRiskRef
By this:
& " WHERE tblRisk.SiteRef=" & Me.txtSiteRef & " AND tblRisk.RiskRef=" & Me.txtRiskRef

Hopefully SiteRef and RiskRef are defined as numeric in tblRisk ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tigersden]

Hi PHV,
Nearly there now, to check the sytax I passes strSQL to msgbox. For some reason it is prompting for criteria siteref! But tne sql statement is being passed the 2 values for the where clause.
Do you have an idea why it would be asking for SiteRef yet again?
Thanks
Tim

 

[PHV]

What about replacing this:
& " FROM tblSite" _
& " INNER JOIN (tblDept INNER JOIN (((tblRisk INNER JOIN tblReview ON tblRisk.RiskRef = tblReview.RiskRef)" _
& " INNER JOIN tblAssessor ON tblReview.AssessorRef = tblAssessor.AssessorRef)" _
& " INNER JOIN tblPersAtRisk ON tblRisk.PersRef = tblPersAtRisk.PersRef) ON tblDept.DeptRef = tblRisk.DeptRef)" _
& " ON tblSite.SiteRef = tblRisk.SiteRef" _
By this ?
& " FROM ((((tblSite" _
& " INNER JOIN tblRisk ON tblSite.SiteRef = tblRisk.SiteRef)" _
& " INNER JOIN tblDept ON tblRisk.DeptRef = tblDept.DeptRef)" _
& " INNER JOIN tblReview ON tblRisk.RiskRef = tblReview.RiskRef)" _
& " INNER JOIN tblAssessor ON tblReview.AssessorRef = tblAssessor.AssessorRef)" _
& " INNER JOIN tblPersAtRisk ON tblRisk.PersRef = tblPersAtRisk.PersRef" _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tigersden]

Hi PHV,
Thanks so much for your help.
I will have another play in the morning.
I cannot see why the query needs or is asking for the criteria again.
Thanks once again
Tim