Query Problem

[nicktred]

I have a query based on a set of constraints entered by user in text/combo boxes. However when i run the query i get the following error message

Runtime error '3065'
cannot execute Query

I have run the sql threw the query wizard and it works there but not in the module

code
**************

Private Sub Search_Click()

'Define Variables
Dim strSQL As String
Dim antpost As Double
Dim body As String
Dim Machine As String
Dim energy As String
Dim andOr1 As String
Dim andOr2 As String
Dim srtConditions As String
Dim strSQLFields As String
Dim strSQLFields2 As String
'If Statements to see if value is null or not
If (IsNull(Me.bodySite)) Then
Else
body = ("((patient.patient_description) ='" + Me.bodySite + "')")

End If
If (IsNull(Me.Machine)) Then
Else
Machine = ("((Session.session_machine) = '" + Me.Machine + "')")
End If
If (IsNull(Me.energyMode)) Then
Else
energy = ("((Session.session_energyMode) ='" + Me.energyMode + "')")
End If
If (IsNull(Me.andOr1)) Then
Else
andOr1 = Me.andOr1
End If
If (IsNull(Me.andOr2)) Then
Else
andOr2 = Me.andOr2
End If

strConditions = (body + andOr1 + Machine + andOr2 + energy)

'Debug.Print (strConditions)

strSQLFields = ("SELECT shiftcalculations.[ant/post], shiftcalculations.[sup/inf], shiftcalculations.[right/left] FROM patient INNER JOIN (shiftcalculations INNER JOIN [session] ON shiftcalculations.patient_id = session.patient_id) ON (shiftcalculations.patient_id = patient.patient_id) AND (patient.patient_id = session.patient_id)")
'Debug.Print (strSQLFields)

strSQLFields2 = ("GROUP BY shiftcalculations.[ant/post], shiftcalculations.[sup/inf], shiftcalculations.[right/left], shiftcalculations.patient_id;")
'Debug.Print (strSQLFields2)

strSQL = (strSQLFields + "WHERE (" + body + andOr1 + Machine + andOr2 + energy + ")" + strSQLFields2)
Debug.Print (strSQL)

CurrentDb.Execute strSQL

End Sub

*********
end code

Does anybody have any ideas

Cheers

Nick

 

[RoyVidar]

Hi!

The full errormsg is probably "Cannot execute a Select Query". The execute method (as with the docmd.runsql) are used on action queries, not select queries.

So if this is some kind of search, try to assign the sql to the form/reports recordsource:

[tt]me.recordsource=strSQL[/tt]

Roy-Vidar

 

[nicktred]

Hi

cheers

Dont suppose you could point me in the direction of a tutorial or similar

cheers

Nick

 

[nicktred]

Thanks guys i got it working

Thanks again

Nick