Trouble with my SQL

**Gord** · Jan 12 '07, 06:25 PM

Re: Trouble with my SQL

You are already building your SQL dynamically, so why not get rid of
the references to the form controls,

strSelectSubjec t = "(((tblComplain tSubjects.fldCo mplaintSubject) = " &
_
"[Forms]![frmReports]![cmboSelectSubje ct])) "

and just insert the actual value of the control, something like this

strSelectSubjec t = "(((tblComplain tSubjects.fldCo mplaintSubject) = '" &
_
Replace(frm.cmb oSelectSubject. Value, "'", "''", , , vbTextCompare) &
"')) "

Giordano wrote:

I am trying to construct a report menu that will allow the user to
select any combination of 3 variables from 3 combo boxes (Select
Subject/SelectCategory/Date Range). There are 2 possible options for
each combo ie <Allor a specific value . That means there are 8
possible outcomes from the user selection process.
>
The SQL for the report is constructed 'on the fly' when the report is
opened, using a series of "if" calculations in the report's OnOpen
event.
>
The problem is that I am not getting the results I expect. The code
does work for options 1 and 3 (see below), the common feature being
that no specific criteria is required for Select Subject and Select
Category. It is only where the user chooses a value from each of those
combo boxes that the code does not work. The dates selection on both
these options works well.
>
I have tested the code for all options individually using Stop commands
and the Immediate Window - copying the resultant code into the SQL view
of a blank query produces the right result, albeit that I have to
manually enter the parameter values.
>
The problem appears to be that when the code is combined into an SQL
statement for the recordsource of the report, it is not picking up the
values that have been entered in the combo boxes on the report menu
form, except for the entries for the dates. The values are there - if
I put string values for those combo boxes in the code below and test
for their values when using a STOP command, the values I input on the
combo boxes are being carried forward - though not in the definition of
MyRecordSource below.
>
The code for the report is as follows:
>
Private Sub Report_Open(Can cel As Integer)
Dim MyRecordSource As String, strSQL As String, strSelectCatego ry As
String, strSelectSubjec t As String, strDateRange As String
Dim frm As Form, rptReport As String, MyCriteria As String
>
>
Set frm = Forms!frmReport s
'Core
strSQL = "SELECT tblComplaints.f ldComplaintID,
tblReferredTo.f ldFullname, tblComplaints.f ldDateRaised,
tblComplaints.f ldDateClosed, _
(DateDiff('d',[fldDateRaised],[fldDateClosed]))"
strSQL = strSQL & " AS DaysTaken,
tblComplaints.f ldComplainantFo rename,
tblComplaints.f ldComplainantSu rname,
tblComplaints.f ldComplaintDesc ription,"
strSQL = strSQL & " tblComplaintCat egory.fldCompla intCategory,
tblComplaintSub jects.fldCompla intSubject FROM ((tblComplaints LEFT JOIN
tblReferredTo"_
strSQL = strSQL & " ON tblComplaints.f ldComplaintRefe rredTo =
tblReferredTo.f ldStaffID) LEFT JOIN tblComplaintCat egory ON
tblComplaints.f ldComplaintCate goryID = "
strSQL = strSQL & " tblComplaintCat egory.fldCompla intCategoryID)
LEFT JOIN tblComplaintSub jects ON tblComplaints.f ldComplaintSubj ectID =
_
tblComplaintSub jects.fldCompla intSubjectID "
>
'Define the various components of the possible criteria (MyCriteria)
'Select a Subject
strSelectSubjec t = "(((tblComplain tSubjects.fldCo mplaintSubject) =
[Forms]![frmReports]![cmboSelectSubje ct])) "
>
'Select a Category
strSelectCatego ry = "((tblComplaint Category.fldCom plaintCategory) =
[Forms]![frmReports]![cmboSelectCateg ory]) "
>
'Select a date range
strDateRange = "((tblComplaint s.fldDateRaised ) Between
[Forms]![frmReports]![TxtStartDate] And
[Forms]![frmReports]![TxtEndDate]) "
>
'Code structure
'Select Subject / Select Category / Date Range
>
>
'Option 1 <All/ <All/ <All>
If frm!cmboSelectS ubject = 0 And frm!cmboSelectC ategory.Visible =
False And frm!txtStartDat e.Visible = False Then
MyCriteria = ""
>
>
'Option 2 <All/ <Category/ <All>
ElseIf frm!cmboSelectS ubject = 0 And
frm!cmboSelectC ategory.Visible = True And frm!txtStartDat e.Visible =
False Then
MyCriteria = " WHERE
(((tblComplaint Category.fldCom plaintCategory) ='Cancellation of
event'));"
>
>
'Option 3 <All/ <Category/ <Dates>
ElseIf frm!cmboSelectS ubject = 0 And
frm!cmboSelectC ategory.Visible = True And frm!txtStartDat e.Visible =
True Then
MyCriteria = " WHERE
((tblComplaintC ategory.fldComp laintCategory)= 'Dogs') AND
((tblComplaints .fldDateRaised) Between #01/11/2004# And #31/12/2006#);"
>
>
'Option 4 <All/ <All/ <Dates>
ElseIf frm!cmboSelectS ubject = 0 And frm!cmboSelectC ategory.Visible
= False And frm!txtStartDat e.Visible = True Then
MyCriteria = " WHERE (((tblComplaint s.fldDateRaised ) Between
#1/1/2004# And #1/1/2007#));"
>
>
'Option 5 <Subject/ <All/ <All>
ElseIf frm!cmboSelectS ubject 0 And
frm!cmboSelectC ategory.Visible = False And frm!txtStartDat e.Visible =
False Then
MyCriteria = " WHERE " & strSelectSubjec t
>
>
'Option 6 <Subject/ <Category/ <All>
ElseIf frm!cmboSelectS ubject 0 And
frm!cmboSelectC ategory.Visible = True And frm!txtStartDat e.Visible =
False Then
MyCriteria = " WHERE " & strSelectSubjec t & " AND " &
strSelectCatego ry
>
>
'Option 7 <Subject/ <Category/ <Dates>
ElseIf frm!cmboSelectS ubject 0 And
frm!cmboSelectC ategory.Visible = True And frm!txtStartDat e.Visible =
True Then
MyCriteria = " WHERE " & strSelectSubjec t & " AND " &
strSelectCatego ry & " AND " & strDateRange
>
>
'Option 8 <Subject/ <All/ <Dates>
ElseIf frm!cmboSelectS ubject 0 And
frm!cmboSelectC ategory.Visible = False And frm!txtStartDat e.Visible =
True Then
MyCriteria = " WHERE " & strSelectSubjec t
End If
>
'Define the source for the report
MyRecordSource = strSQL & MyCriteria
>
'Open Recordset object.
Reports!rptSumm aryBySubject.Re cordSource = MyRecordSource
>
End Sub
>
>
Can someone please help me on this? Where am I going wrong? Is there
any other way I can test where the problem lies?
>
>
Gordon

Trouble with my SQL

Trouble with my SQL

Comment