Find Duplicate Query in VB

[dodgyone]

I'm trying to use a sql query string in VB and then create a find duplicate query on that. I've tested the first query (MyQuery1) and that works fine but it's the find duplicates query (MyQuery2) that brings up the error. I've tested the find duplicates query (MyQuery2) with a simple SELECT statement and that works fine. Does any one have a solution up their sleeve? Thanks...

'originating query to work by
MyQuery1 = "SELECT tbl_Question1.Quest_ID, tbl_Questionnaire.Reference_ID, tbl_Question3.Description, tbl_Question3.AreaOwned, tbl_Question3.AreaLeased, tbl_Question1.Description, tbl_Question1.ResultClosure, tbl_Question1.InhibitDevelopment, tbl_Question1.MinorProblem, tbl_Question1.NotProblem, tbl_Question1.Invalid " & _
"FROM (tbl_Questionnaire INNER JOIN tbl_Question1 ON tbl_Questionnaire.Quest_ID = tbl_Question1.Quest_ID) INNER JOIN tbl_Question3 ON tbl_Questionnaire.Quest_ID = tbl_Question3.Quest_ID " & _
"WHERE ((" & MyQueryAddition & ") AND ((tbl_Question3.AreaOwned)>0) AND ((tbl_Question1.Description)='" & Me.cbo_Criteria.Value & "') AND ((tbl_Question1.ResultClosure)=True)) " & _
"OR ((" & MyQueryAddition & ") AND ((tbl_Question3.AreaLeased)>0) AND ((tbl_Question1.Description)='" & Me.cbo_Criteria.Value & "') AND ((tbl_Question1.ResultClosure)=True)) " & _
"OR ((" & MyQueryAddition & ") AND ((tbl_Question3.AreaOwned)>0) AND ((tbl_Question1.Description)='" & Me.cbo_Criteria.Value & "') AND ((tbl_Question1.InhibitDevelopment)=True)) " & _
"OR ((" & MyQueryAddition & ") AND ((tbl_Question3.AreaLeased)>0) AND ((tbl_Question1.Description)='" & Me.cbo_Criteria.Value & "') AND ((tbl_Question1.InhibitDevelopment)=True)) " & _
"OR ((" & MyQueryAddition & ") AND ((tbl_Question3.AreaOwned)>0) AND ((tbl_Question1.Description)='" & Me.cbo_Section.Value & "') AND ((tbl_Question1.ResultClosure)=" & Me.chk_ResultClosure.Value & ") AND ((tbl_Question1.InhibitDevelopment)=" & Me.chk_InhibitDevelopment.Value & ") AND ((tbl_Question1.MinorProblem)=" & Me.chk_MinorProblem.Value & ") AND ((tbl_Question1.NotProblem)=" & Me.chk_NotProblem.Value & ") AND ((tbl_Question1.Invalid)=" & Me.chk_Invalid.Value & ")) " & _
"OR ((" & MyQueryAddition & ") AND ((tbl_Question3.AreaLeased)>0) AND ((tbl_Question1.Description)='" & Me.cbo_Section.Value & "') AND ((tbl_Question1.ResultClosure)=" & Me.chk_ResultClosure.Value & ") AND ((tbl_Question1.InhibitDevelopment)=" & Me.chk_InhibitDevelopment.Value & ") AND ((tbl_Question1.MinorProblem)=" & Me.chk_MinorProblem.Value & ") AND ((tbl_Question1.NotProblem)=" & Me.chk_NotProblem.Value & ") AND ((tbl_Question1.Invalid)=" & Me.chk_Invalid.Value & ")) " & _
"ORDER BY tbl_Question1.Quest_ID"

'find duplicate query
MyQuery2 = "SELECT Reference_ID, AreaOwned, AreaLeased " & _
"FROM (" & MyQuery1 & ") WHERE (((Reference_ID) In (SELECT [Reference_ID] FROM & (" & MyQuery1 & ") As Tmp GROUP BY Reference_ID HAVING Count(*)>1 ))) " & _
"ORDER BY Reference_ID"
  
Set rst = db.OpenRecordset(MyQuery2)
rst.MoveFirst
Do Until rst.EOF
  MsgBox rst("Reference_ID")
  rst.MoveNext
Loop

 

[dodgyone]

Does anyone have any ideas about this? I'm pretty stuck until this bit is solved!

 

[dodgyone]

Solved it. Double checking the query I stupidly messed up the formatting. It should of been:

MyQuery2 = "SELECT Reference_ID, AreaOwned, AreaLeased " & _
FROM (" & MyQuery1 & ") WHERE (((Reference_ID) In (SELECT [Reference_ID] FROM (" & MyQuery1 & ") As Tmp GROUP BY [Reference_ID] HAVING Count(*)>1 ))) " & _
"ORDER BY Reference_ID"