Build Report based on 3 conditions

  • Thread starter Thread starter NFL
  • Start date Start date
N

NFL

I have a query using 3 tables that have relationships and it works great with
no criterias (shown below). I also have a report that uses the same query and
works fine.

SELECT Courses.ClassID, [Student And Class ID].CourseDate,
Students.LastName, Students.FirstName, Courses.ClassName, [Student And Class
ID].Time, [Student And Class ID].Instructor
FROM Students INNER JOIN (Courses INNER JOIN [Student And Class ID] ON
Courses.ClassID = [Student And Class ID].ClassID) ON Students.StudentID =
[Student And Class ID].StudentID;

In the query I tried the following criterias and it doesn't seem to work
right.
[forms]![ReportFm]![combo15]
[forms]![ReportFm]![combo17]
[forms]![ReportFm]![combo19]

This is what I did so far:
I created an unbound form with 3 combo boxes and a print command button.
The 1st one searches for ClassID and ClassName - The data will only display
the ClassID (which is combo17)
The 2nd one searches for Course Date (which is combo15)
The 3d one searches for Time (which is combo19)

When I press the command button, nothing shows up.

Thank you!
 
I would modify the control names and the code for the command button like:

Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.cboClassID) Then
'assuming ClassID is numeric
strWhere = strWhere & " AND [ClassID]=" & Me.cboClassID & " "
End If
If Not IsNull(Me.cboCourseDate) Then
'CourseDate is a date field
strWhere = strWhere & " AND [CourseDate]=#" & Me.cboCourseDate & "# "
End If
If Not IsNull(Me.cboCourseTime) Then
'Time is a date field
strWhere = strWhere & " AND [Time]=#" & Me.cboCourseTime & "# "
End If
DoCmd.OpenReport "rptYourReport" , acPreview, , strWhere

BTW: Time is not a good name for a field since Time() is a function.
 
Question: On the unbound form the cboClassID pulls 2 fields (ClassID &
ClassName). The cboClassID only displays the ClassID. The ClassID field is
not a numeric field, it is a text field set to display alphanumeric & numbers.

On the button command I get a syntax error (missing operator) in query
expression...

Here's the query ... I did change the field names as suggested.....

SELECT Courses.ClassID, Courses.ClassName, [Student And Class
ID].CourseDate, Students.LastName, Students.FirstName, ClassTime, [Student
And Class ID].Instructor
FROM Students INNER JOIN (Courses INNER JOIN [Student And Class ID] ON
Courses.ClassID = [Student And Class ID].ClassID) ON Students.StudentID =
[Student And Class ID].StudentID
WHERE (((Courses.ClassID)=[forms]![ReportFm]![cboClassID]) AND (([Student
And Class ID].CourseDate)=[forms]![ReportFm]![CourseDate]) AND (([Student And
Class ID].ClassTime)=[forms]![ReportFm]![cboClassTime]));

Thank you!

Duane Hookom said:
I would modify the control names and the code for the command button like:

Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.cboClassID) Then
'assuming ClassID is numeric
strWhere = strWhere & " AND [ClassID]=" & Me.cboClassID & " "
End If
If Not IsNull(Me.cboCourseDate) Then
'CourseDate is a date field
strWhere = strWhere & " AND [CourseDate]=#" & Me.cboCourseDate & "# "
End If
If Not IsNull(Me.cboCourseTime) Then
'Time is a date field
strWhere = strWhere & " AND [Time]=#" & Me.cboCourseTime & "# "
End If
DoCmd.OpenReport "rptYourReport" , acPreview, , strWhere

BTW: Time is not a good name for a field since Time() is a function.

--
Duane Hookom
Microsoft Access MVP


NFL said:
I have a query using 3 tables that have relationships and it works great with
no criterias (shown below). I also have a report that uses the same query and
works fine.

SELECT Courses.ClassID, [Student And Class ID].CourseDate,
Students.LastName, Students.FirstName, Courses.ClassName, [Student And Class
ID].Time, [Student And Class ID].Instructor
FROM Students INNER JOIN (Courses INNER JOIN [Student And Class ID] ON
Courses.ClassID = [Student And Class ID].ClassID) ON Students.StudentID =
[Student And Class ID].StudentID;

In the query I tried the following criterias and it doesn't seem to work
right.
[forms]![ReportFm]![combo15]
[forms]![ReportFm]![combo17]
[forms]![ReportFm]![combo19]

This is what I did so far:
I created an unbound form with 3 combo boxes and a print command button.
The 1st one searches for ClassID and ClassName - The data will only display
the ClassID (which is combo17)
The 2nd one searches for Course Date (which is combo15)
The 3d one searches for Time (which is combo19)

When I press the command button, nothing shows up.

Thank you!
Click to expand...
Click to expand...
 
I would remove the criteria from the query and use the WHERE CONDITION of the
DoCmd.OpenReport method. Try this code:

Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.cboClassID) Then
'assuming ClassID is numeric
strWhere = strWhere & " AND [ClassID]=""" & Me.cboClassID & """ "
End If
If Not IsNull(Me.cboCourseDate) Then
'CourseDate is a date field
strWhere = strWhere & " AND [CourseDate]=#" & Me.CourseDate & "# "
End If
If Not IsNull(Me.cboCourseTime) Then
'Time is a date field
strWhere = strWhere & " AND [ClassTime]=#" & Me.cboClassTime & "# "
End If
DoCmd.OpenReport "rptYourReport" , acPreview, , strWhere


--
Duane Hookom
Microsoft Access MVP


NFL said:
Question: On the unbound form the cboClassID pulls 2 fields (ClassID &
ClassName). The cboClassID only displays the ClassID. The ClassID field is
not a numeric field, it is a text field set to display alphanumeric & numbers.

On the button command I get a syntax error (missing operator) in query
expression...

Here's the query ... I did change the field names as suggested.....

SELECT Courses.ClassID, Courses.ClassName, [Student And Class
ID].CourseDate, Students.LastName, Students.FirstName, ClassTime, [Student
And Class ID].Instructor
FROM Students INNER JOIN (Courses INNER JOIN [Student And Class ID] ON
Courses.ClassID = [Student And Class ID].ClassID) ON Students.StudentID =
[Student And Class ID].StudentID
WHERE (((Courses.ClassID)=[forms]![ReportFm]![cboClassID]) AND (([Student
And Class ID].CourseDate)=[forms]![ReportFm]![CourseDate]) AND (([Student And
Class ID].ClassTime)=[forms]![ReportFm]![cboClassTime]));

Thank you!

Duane Hookom said:
I would modify the control names and the code for the command button like:

Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.cboClassID) Then
'assuming ClassID is numeric
strWhere = strWhere & " AND [ClassID]=" & Me.cboClassID & " "
End If
If Not IsNull(Me.cboCourseDate) Then
'CourseDate is a date field
strWhere = strWhere & " AND [CourseDate]=#" & Me.cboCourseDate & "# "
End If
If Not IsNull(Me.cboCourseTime) Then
'Time is a date field
strWhere = strWhere & " AND [Time]=#" & Me.cboCourseTime & "# "
End If
DoCmd.OpenReport "rptYourReport" , acPreview, , strWhere

BTW: Time is not a good name for a field since Time() is a function.

--
Duane Hookom
Microsoft Access MVP


NFL said:
I have a query using 3 tables that have relationships and it works great with
no criterias (shown below). I also have a report that uses the same query and
works fine.

SELECT Courses.ClassID, [Student And Class ID].CourseDate,
Students.LastName, Students.FirstName, Courses.ClassName, [Student And Class
ID].Time, [Student And Class ID].Instructor
FROM Students INNER JOIN (Courses INNER JOIN [Student And Class ID] ON
Courses.ClassID = [Student And Class ID].ClassID) ON Students.StudentID =
[Student And Class ID].StudentID;

In the query I tried the following criterias and it doesn't seem to work
right.
[forms]![ReportFm]![combo15]
[forms]![ReportFm]![combo17]
[forms]![ReportFm]![combo19]

This is what I did so far:
I created an unbound form with 3 combo boxes and a print command button.
The 1st one searches for ClassID and ClassName - The data will only display
the ClassID (which is combo17)
The 2nd one searches for Course Date (which is combo15)
The 3d one searches for Time (which is combo19)

When I press the command button, nothing shows up.

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Thank you for your help... it works! :-)

Duane Hookom said:
I would remove the criteria from the query and use the WHERE CONDITION of the
DoCmd.OpenReport method. Try this code:

Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.cboClassID) Then
'assuming ClassID is numeric
strWhere = strWhere & " AND [ClassID]=""" & Me.cboClassID & """ "
End If
If Not IsNull(Me.cboCourseDate) Then
'CourseDate is a date field
strWhere = strWhere & " AND [CourseDate]=#" & Me.CourseDate & "# "
End If
If Not IsNull(Me.cboCourseTime) Then
'Time is a date field
strWhere = strWhere & " AND [ClassTime]=#" & Me.cboClassTime & "# "
End If
DoCmd.OpenReport "rptYourReport" , acPreview, , strWhere


--
Duane Hookom
Microsoft Access MVP


NFL said:
Question: On the unbound form the cboClassID pulls 2 fields (ClassID &
ClassName). The cboClassID only displays the ClassID. The ClassID field is
not a numeric field, it is a text field set to display alphanumeric & numbers.

On the button command I get a syntax error (missing operator) in query
expression...

Here's the query ... I did change the field names as suggested.....

SELECT Courses.ClassID, Courses.ClassName, [Student And Class
ID].CourseDate, Students.LastName, Students.FirstName, ClassTime, [Student
And Class ID].Instructor
FROM Students INNER JOIN (Courses INNER JOIN [Student And Class ID] ON
Courses.ClassID = [Student And Class ID].ClassID) ON Students.StudentID =
[Student And Class ID].StudentID
WHERE (((Courses.ClassID)=[forms]![ReportFm]![cboClassID]) AND (([Student
And Class ID].CourseDate)=[forms]![ReportFm]![CourseDate]) AND (([Student And
Class ID].ClassTime)=[forms]![ReportFm]![cboClassTime]));

Thank you!

Duane Hookom said:
I would modify the control names and the code for the command button like:

Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.cboClassID) Then
'assuming ClassID is numeric
strWhere = strWhere & " AND [ClassID]=" & Me.cboClassID & " "
End If
If Not IsNull(Me.cboCourseDate) Then
'CourseDate is a date field
strWhere = strWhere & " AND [CourseDate]=#" & Me.cboCourseDate & "# "
End If
If Not IsNull(Me.cboCourseTime) Then
'Time is a date field
strWhere = strWhere & " AND [Time]=#" & Me.cboCourseTime & "# "
End If
DoCmd.OpenReport "rptYourReport" , acPreview, , strWhere

BTW: Time is not a good name for a field since Time() is a function.

--
Duane Hookom
Microsoft Access MVP


:

I have a query using 3 tables that have relationships and it works great with
no criterias (shown below). I also have a report that uses the same query and
works fine.

SELECT Courses.ClassID, [Student And Class ID].CourseDate,
Students.LastName, Students.FirstName, Courses.ClassName, [Student And Class
ID].Time, [Student And Class ID].Instructor
FROM Students INNER JOIN (Courses INNER JOIN [Student And Class ID] ON
Courses.ClassID = [Student And Class ID].ClassID) ON Students.StudentID =
[Student And Class ID].StudentID;

In the query I tried the following criterias and it doesn't seem to work
right.
[forms]![ReportFm]![combo15]
[forms]![ReportFm]![combo17]
[forms]![ReportFm]![combo19]

This is what I did so far:
I created an unbound form with 3 combo boxes and a print command button.
The 1st one searches for ClassID and ClassName - The data will only display
the ClassID (which is combo17)
The 2nd one searches for Course Date (which is combo15)
The 3d one searches for Time (which is combo19)

When I press the command button, nothing shows up.

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top