If/Then expression within a query?

[Alc]

That looks okay, but you'll only know for sure when you run a test.
Don't forget to add in the -1 parts, once it's all working.

 

[dan231]

I got an error:

undefined function 'WHERE' in expression.

Here is the SQL:

SELECT tblBaClassSchedule.ClassDescription, tblBaClassSchedule.ClassScheduleID, tblBaClassRoster.FK_StudentID, [FirstName] & " " & [LastName] AS StudentName, tblBaStudents.LastName, tblBaClassSchedule.StartDate, tblBaClassSchedule.EndDate, DatePart("yyyy",[tblBaClassSchedule.EndDate]) AS ClassYear, forms!frmBaReportsMenu!txtClassMonth AS ClassMonth, forms!frmBaReportsMenu!txtClassDay AS ClassDay, 0 AS EmployeeID, tblBaStudent_mm_ProfessionalAffiliation.MembershipNumber, tblBaCourses.CourseAIACourseNumber, tblBaCourses.CourseContEdUnits, tblBaCourses.CourseContactHours, forms!frmBaReportsMenu!txtClassDates AS ClassDates, tblBaCourses.CourseLearningUnits, tblBaClassSchedule.ClassDescription AS CourseDescription, tblBaClassSchedule.ClassName, tblBaClassLocation.City & ", " & [tblBaClassLocation.State] AS Location, forms!frmBaReportsMenu!txtInstructorName AS EmployeeFullName, IIf([FK_ProfessionalAssociationID]=1,"AIA: " & [MembershipNumber],"") AS AIANumber, [CertificateType] & [LicenseCertificateNumber] AS LicenseOrCertification, IIf([FK_StudentCertificateTypeID]=3 Or [FK_StudentCertificateTypeID]=4,"Plumbing License: ","") AS CertificateType, qryBaStudentNameAddress.StudentAddress, qryBaStudentNameAddress.StudentCityStateZIP, tblBaStudents.CompanyName, tblBaClassSchedule.Status, qryBaStudentNameAddress.StudentlNameWIthMI AS SN, IIf([StateCourseApprovaNumber]>"","State Course Approval Number: " & [StateCourseApprovaNumber],"") AS StateCourseApprovalNumber, tblBaClassSchedule.FK_CourseID, IIf(([SignInMorning]=-1 And [SignInAfternoon]=-1),-1,0) AS Expr1, tblBaClassRoster.EnrollmentStatus, WHERE(((tblBaClassSchedule.ClassScheduleID)=forms!frmBaReportsMenu!cboGetClasses) And ((tblBaClassRoster.EnrollmentStatus)=900) And (tblBaClassSchedule.StartDate<>tblBaClassSchedule.EndDate)) AS Expr2
FROM tblBaClassAttendance INNER JOIN (((qryBaStudentNameAddress INNER JOIN (((((((tblBaClassSchedule INNER JOIN tblBaClassRoster ON tblBaClassSchedule.ClassScheduleID=tblBaClassRoster.FK_ClassScheduleID) INNER JOIN tblBaCourses ON tblBaClassSchedule.FK_CourseID=tblBaCourses.CourseID) LEFT JOIN tblBaInstructor_mm_ClassSchedule ON tblBaClassSchedule.ClassScheduleID=tblBaInstructor_mm_ClassSchedule.Instructor_mm_ClassScheduleID) LEFT JOIN tblEmployee ON tblBaInstructor_mm_ClassSchedule.FK_EmployeeID=tblEmployee.EmployeeID) INNER JOIN tblBaStudents ON tblBaClassRoster.FK_StudentID=tblBaStudents.StudentID) LEFT JOIN tblBaStudentCertificate ON tblBaStudents.StudentID=tblBaStudentCertificate.FK_StudentID) LEFT JOIN tblBaClassLocation ON tblBaClassSchedule.FK_LocationID=tblBaClassLocation.LocationID) ON qryBaStudentNameAddress.StudentID=tblBaStudents.StudentID) LEFT JOIN tblBaStudent_mm_ProfessionalAffiliation ON tblBaStudents.StudentID=tblBaStudent_mm_ProfessionalAffiliation.FK_StudentID) LEFT JOIN tblBaStateCourseApproval ON tblBaClassSchedule.FK_CourseID=tblBaStateCourseApproval.CourseID_FK) ON tblBaClassAttendance.FK_StudentID=tblBaStudents.StudentID
WHERE (((tblBaClassSchedule.ClassScheduleID)=forms!frmBaReportsMenu!cboGetStudentClasses) And ((tblBaClassRoster.FK_StudentID)=forms!frmBaReportsMenu!txtStudentID) And ((tblBaClassSchedule.Status)=900) And ((IIf(([SignInMorning]=-1 And [SignInAfternoon]=-1),-1,0))=-1) And ((tblBaClassRoster.EnrollmentStatus)=900)) Or (((tblBaClassSchedule.ClassScheduleID)=forms!frmBaReportsMenu!cboGetStudentClasses) And ((tblBaClassRoster.FK_StudentID)=forms!frmBaReportsMenu!txtStudentID) And ((tblBaClassSchedule.StartDate)<>tblBaClassSchedule.EndDate) And ((tblBaClassSchedule.Status)=900) And ((IIf(([SignInMorning]=-1 And [SignInAfternoon]=-1),-1,0))=-1) And ((tblBaClassRoster.EnrollmentStatus)=900));

 

[Alc]

If you take out the second 'where' statement, does it run without errors?

 

[dan231]

I removed after the OR and it said there was no data found.

 

[Alc]

So if the where clause is just

(((tblBaClassSchedule.ClassScheduleID)=forms!frmBaReportsMenu!cboGetStudentClasses) And ((tblBaClassRoster.FK_StudentID)=forms!frmBaReportsMenu!txtStudentID) And ((tblBaClassSchedule.Status)=900) And ((IIf(([SignInMorning]=-1 And [SignInAfternoon]=-1),-1,0))=-1) And ((tblBaClassRoster.EnrollmentStatus)=900))

there is no data. It may be a stupid question, but would you expect there to be some matching this?

Do you think you could post the database itself, or is the data all confidential?

 

[dan231]

The front end is 70 MB and the SQL backend is pushing 23GB. I can't post that.

Bottom line is I am trying to fix a report that doesn't work right. when this report is run, you select the class and it generates a class completion certificate for all students (word merge file) who registered for the class. I am trying to get it to filter only the students who attended each day. The problem is that some classes are 1-day some are multi-day. When I added the ClassAttendance SignIns of -1 and the ClassRoster Status = 900, it then provides the data I need for 1-day classes only.

And yes I should have some data. I have 2 classes that I have manually verified all data, which I am using as my tests.

 

[Alc]

Okay, I see the problem with posting it.

Each query works when run in isolation? If so, the problem is the join, somehow. If not, we're trying to join two that don't work in the first place.

This is the WHERE...OR.... statement, with the extra brackets removed (I just find it easier to read). I've added in the date part to the first clause, as a reminder that these dates should be equal for one day courses.
Can you see any contradictry parts? It's difficult to tell without seeing the tables.

WHERE 
(
tblBaClassSchedule.ClassScheduleID = forms!frmBaReportsMenu!cboGetStudentClasses
And tblBaClassRoster.FK_StudentID = forms!frmBaReportsMenu!txtStudentID 
And tblBaClassSchedule.Status = 900 
And tblBaClassSchedule.StartDate = tblBaClassSchedule.EndDate
And IIf(([SignInMorning]=-1 And [SignInAfternoon]=-1),-1,0) = -1 
And tblBaClassRoster.EnrollmentStatus = 900
) 
Or 
(
tblBaClassSchedule.ClassScheduleID = forms!frmBaReportsMenu!cboGetStudentClasses
And tblBaClassRoster.FK_StudentID = forms!frmBaReportsMenu!txtStudentID
And tblBaClassSchedule.Status)=900
And tblBaClassSchedule.StartDate <> tblBaClassSchedule.EndDate
And IIf(([SignInMorning]=-1 And [SignInAfternoon]=-1),-1,0) =-1
And tblBaClassRoster.EnrollmentStatus = 900
)

If you can't see anything wrong with the above, could you post the SQL for both, separate, working queries?

 

[dan231]

I'm sorry, I am a novice at all this (could you tell)

Do you want me to try both of your last post WHERE STMTS and post the individual SQL STMTS?

 

[Alc]

I'm taking a step back, to try to see if the probem is with the combination of the two queries or with one of the queries itself.

Going back to an earlier post, we discussed creating two separate queries and them 'merging' them. Try testing each of the queries on it's own, one for multiple day and one for single day classes. Once we have both of those working in isolation, it'll make the merge much simpler than trying to fix them and merge them as we go.

Once they're both working, post the full SQL statement for each.

 

[dan231]

ok, stand by

 

[dan231]

1-DAY:

I added this to the Qry design and I get no records found. Without this, I get the records I want.

DatesEqual: [StartDate]=[EndDate]

This code is what works for the 1-day:

SELECT DISTINCT tblBaClassSchedule.ClassDescription, tblBaClassSchedule.ClassScheduleID, [FirstName] & " " & [LastName] AS StudentName, tblBaStudents.LastName, tblBaClassSchedule.StartDate, tblBaClassSchedule.EndDate, DatePart("yyyy",[tblBaClassSchedule.EndDate]) AS ClassYear, forms!frmBaReportsMenu!txtClassMonth AS ClassMonth, forms!frmBaReportsMenu!txtClassDay AS ClassDay, tblBaStudents.StudentID, tblBaStudent_mm_ProfessionalAffiliation.MembershipNumber, tblBaCourses.CourseAIACourseNumber, tblBaCourses.CourseContEdUnits, tblBaCourses.CourseContactHours, forms!frmBaReportsMenu!txtClassDates AS ClassDates, tblBaCourses.CourseLearningUnits, tblBaClassSchedule.ClassDescription AS CourseDescription, tblBaClassSchedule.ClassName, tblBaClassLocation.City & ", " & [StateName] AS Location, forms!frmBaReportsMenu!txtInstructorName AS EmployeeFullName, IIf([FK_ProfessionalAssociationID]=1,"AIA: " & [MembershipNumber],"") AS AIANumber, [CertificateType] & [LicenseCertificateNumber] AS LicenseOrCertification, IIf([FK_StudentCertificateTypeID]=3 Or [FK_StudentCertificateTypeID]=4,"Plumbing License: ","") AS CertificateType, 0 AS EmployeeID, qryBaStudentNameAddress.StudentAddress, qryBaStudentNameAddress.StudentCity, qryBaStudentNameAddress.StudentCity, qryBaStudentNameAddress.StudentState, qryBaStudentNameAddress.StudentState, qryBaStudentNameAddress.StudentZIP, qryBaStudentNameAddress.StudentCityStateZIP, qryBaStudentNameAddress.StudentAddressCityStateZIP, qryBaStudentNameAddress.CompanyName, qryBaStudentNameAddress.StudentlNameWIthMI AS SN, IIf([StateCourseApprovaNumber]>"","State Course Approval Number: " & [StateCourseApprovaNumber],"") AS StateCourseApprovalNumber, tblBaClassSchedule.Status, IIf(([SignInMorning]=-1 And [SignInAfternoon]=-1),-1,0) AS Expr1, tblBaClassRoster.EnrollmentStatus
FROM (((tblBaStudentCertificate RIGHT JOIN (tblStateName RIGHT JOIN (tblEmployee RIGHT JOIN ((((((tblBaClassSchedule INNER JOIN tblBaClassRoster ON tblBaClassSchedule.ClassScheduleID = tblBaClassRoster.FK_ClassScheduleID) INNER JOIN tblBaStudents ON tblBaClassRoster.FK_StudentID = tblBaStudents.StudentID) LEFT JOIN tblBaStudent_mm_ProfessionalAffiliation ON tblBaStudents.StudentID = tblBaStudent_mm_ProfessionalAffiliation.FK_StudentID) INNER JOIN tblBaCourses ON tblBaClassSchedule.FK_CourseID = tblBaCourses.CourseID) LEFT JOIN tblBaClassLocation ON tblBaClassSchedule.FK_LocationID = tblBaClassLocation.LocationID) LEFT JOIN tblBaInstructor_mm_ClassSchedule ON tblBaClassSchedule.ClassScheduleID = tblBaInstructor_mm_ClassSchedule.FK_ClassScheduleID) ON tblEmployee.EmployeeID = tblBaInstructor_mm_ClassSchedule.FK_EmployeeID) ON tblStateName.StateAbbreviation = tblBaClassLocation.State) ON tblBaStudentCertificate.FK_StudentID = tblBaStudents.StudentID) INNER JOIN qryBaStudentNameAddress ON tblBaStudents.StudentID = qryBaStudentNameAddress.StudentID) LEFT JOIN tblBaStateCourseApproval ON tblBaClassSchedule.FK_CourseID = tblBaStateCourseApproval.CourseID_FK) INNER JOIN tblBaClassAttendance ON tblBaStudents.StudentID = tblBaClassAttendance.FK_StudentID
WHERE (((tblBaClassSchedule.ClassScheduleID)=[forms]![frmBaReportsMenu]![cboGetClasses]) AND ((IIf(([SignInMorning]=-1 And [SignInAfternoon]=-1),-1,0))=-1) AND ((tblBaClassRoster.EnrollmentStatus)=900))
ORDER BY tblBaStudents.LastName;

 

[Alc]

Okay, so far so good.
How about the multi day?

 

[dan231]

I added [StartDate]<>[EndDate] and also get no records. I specifically ran a multi-day class.

 

[MSAccessRookie]

The query as posted in #22 is formatted incorrectly, and will provide the error that you encountered (See Below). If this is still the format that you are using, please post back.

NOTE: it looks like you got this part fixed while I was posting.


The proper form of a SELECT statement as you are using it is:

SELECT {Something}
FROM {Some Table or Tables}
WHERE {Conditions(s) to test}
​

Your Query appears to have the following format:

SELECT {Something}, WHERE {Condition(s) to test #1}
FROM {Some Table or Tables}
WHERE {Conditions(s) to test #2}
​

You will need to merge the two WHERE statements, and remove the , that appears at the end of the SELECTs.

 

[dan231]

MSAccesRookie:

No the current qry is NOT the same as post 22, the current qry is post #31

 

[bnk]

Hello,
I need some help with this one...
I'm trying to convert an if, then else statement from sql to use in ms access.
Any help is appreciated Thank you
------------------------
if IsNull ({MASTERINVENTORY.STRENGTH}) then
({ItemInventory.GENERICNAME})+" "+({MASTERINVENTORY.FORM})
else
({ItemInventory.GENERICNAME})+" "+({MASTERINVENTORY.STRENGTH})+" "+({MASTERINVENTORY.FORM});
if IsNull ({MASTERINVENTORY.FORM}) then
({ItemInventory.GENERICNAME})+" "+({MASTERINVENTORY.STRENGTH})
else
({ItemInventory.GENERICNAME})+" "+({MASTERINVENTORY.STRENGTH})+" "+({MASTERINVENTORY.FORM});