Results 1 to 14 of 14
  1. #1
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45

    Where clause in SQL Statement of VBA code

    My dilemma to edit the following code to work. I am 100% sure that it is simply a syntax coding issue that I just can't figure out. So my reasoning for coming here for assistance is two fold

    1) Get the code fixed so that it works
    2) If anybody can point me in the right direction as to where I can go and learn this concept - even better

    This part of the code works fine

    SQL = "SELECT Exam.SerialID, Student.ProperLastName, IIf(IsNull([Student]![ProperLastName]),StrConv([Student]![LastName],3),[Student]![ProperLastName]) AS LName, " & _
    "Student.ProperFirstName, IIf(IsNull([Student]![ProperFirstName]),StrConv([Student]![FirstNames],3),[Student]![ProperFirstName]) AS FName, Student.DateofBirth, " & _
    "Student.Address, Student.PostalCode, Student.City," & _
    "Student.Prov, 'Yes' AS [40 hour course completed], Exam.ExamID, Exam.Attempt, IIf([Student].[Prov Exam Language]='2','French','') AS [Language], " & _
    "Exam.ExamDate, Exam.ExamTime " & _
    "FROM Student INNER JOIN (Instructors INNER JOIN Exam ON Instructors.InstructorID = Exam.InstructorID) ON Student.StudentID = Exam.StudentID " & _
    "Where Exam.[SerialID] = " & Me.SerialID

    but where I get into trouble is I want to add this at the end of the where clause and then an order statement and I just can't get it



    "WHERE StudentCourse.CourseStatus In ('Registered','In Progress','Completed','Failed Exam','Failed Course')" & _
    ORDER BY Student.LastName, Student.FirstNames;

    Thanks for any assistance provided

    Rick

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    "Where Exam.[SerialID] = " & Me.SerialID & " AND StudentCourse.CourseStatus IN ('Registered','In Progress','Completed','Failed Exam','Failed Course')" & _
    "ORDER BY Student.LastName, Student.FirstNames;"

    This assumes the field CourseStatus is saving the descriptive text and not an ID value from a lookup table of Status values.


    SQL tutorial at https://www.w3schools.com/sql/
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    OH - June7 you might be onto something

    the field CourseStatus is in fact a combo box but it just gets its values from a value list so now I am receiving an error message (See Below)

    Click image for larger version. 

Name:	image3.jpg 
Views:	23 
Size:	22.8 KB 
ID:	29457
    Click image for larger version. 

Name:	image2.jpg 
Views:	23 
Size:	69.0 KB 
ID:	29456
    Click image for larger version. 

Name:	image1.jpg 
Views:	23 
Size:	133.0 KB 
ID:	29458

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Build the query in Access query builder to see if it will work. Use a static value for the SerialID.

    Instead of IIf and IsNull could use Nz.

    Nz([Student]![ProperLastName],StrConv([Student]![LastName],3))

    Review http://allenbrowne.com/QueryPerfIssue.html

    But why two fields for last name?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    The original SQL statement works flawless for transferring to an excel spreadsheet to an outside agency - it is only when I try to continue with the where clause and then the Order by clause is where I run into troubles.

    The reason for two different Last Name fields is that names with Hypens or (McD or MacD etc) require different capitalization rules - so if these are required then we add them in (There is probably an easier way to accomplish this but in my little pea brain this is how I solved that issue)

    Here is the SQL statement for the underlying query within the Database that appears to work fine

    SELECT StudentCourse.StudentCourseID, StudentCourse.SerialID, StudentCourse.StudentID, StudentCourse.CourseStatus, IIf(IsNull([Student]![ProperFirstName]),StrConv([Student]![FirstNames],3),[Student]![ProperFirstName]) & " " & IIf(IsNull([Student]![ProperLastName]),StrConv([Student]![LastName],3),[Student]![ProperLastName]) AS Name, Exam.ExamDate, Exam.ExamTime, StudentCourse.PrintCertificate, Exam.InstructorID, Student.[Prov Exam Language], IIf(Nz([Prov Exam Language])=1,"","French") AS [Language], StudentCourse.StudentStatus
    FROM Student INNER JOIN (StudentCourse INNER JOIN Exam ON StudentCourse.StudentCourseID = Exam.StudentCourseID) ON Student.StudentID = Exam.StudentID
    WHERE (((StudentCourse.SerialID)=[Forms]![frmExamSerial]![cboSerial]) AND ((StudentCourse.CourseStatus) In ("Registered","In Progress","Completed","Failed Exam","Failed Course")) AND ((Exam.ExamTime)=[Forms]![frmExamSerial]![cboTime]))
    ORDER BY Student.LastName, Student.FirstNames;

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    SerialID is a number type field? If not, use apostrophe delimiters for text parameter. Date/time type use # delimiter.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    Serial ID is a number

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Now try a static SerialID in the VBA.

    I cannot see anything wrong with the SQL and I cannot replicate issue. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    OK - Thanks for trying to assist.

    In my thinking - SerialID is not the issue as the SQL statement works and I get the output into the excel spreadsheet - It is only when I try to add the additional where clause for the CourseStatus and/or the Order clause where the issue arises.
    So it comes down to either something wrong with those values of more probably a syntax problem.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Two things I saw in the first post:
    but where I get into trouble is I want to add this at the end of the where clause and then an order statement and I just can't get it

    "WHERE StudentCourse.CourseStatus In ('Registered','In Progress','Completed','Failed Exam','Failed Course')" & _
    ORDER BY Student.LastName, Student.FirstNames;<snip>
    1)There can be only 1 WHERE clause in a SQL statement
    2) It looks like there was a missing space before the "ORDER BY" clause.


    In code I like to break up the SQL into multiple lines concatenated together; it is easier to read. I add a space at the beginning of each line instead of at the end of the line. For me, it is easier to see when I am missing a space.

    Here is the code
    Code:
        sSQL = "SELECT Exam.SerialID, Student.ProperLastName, Student.ProperFirstName, "
        sSQL = sSQL & " IIf(IsNull([Student]![ProperLastName]),StrConv([Student]![LastName],3),[Student]![ProperLastName]) AS LName,"
        sSQL = sSQL & " IIf(IsNull([Student]![ProperFirstName]),StrConv([Student]![FirstNames],3),[Student]![ProperFirstName]) AS FName,"
        sSQL = sSQL & " Student.DateofBirth, Student.Address, Student.PostalCode, Student.City,"
        sSQL = sSQL & " Student.Prov, 'Yes' AS [40 hour course completed], Exam.ExamID, Exam.Attempt,"
        sSQL = sSQL & " IIf([Student].[Prov Exam Language] = '2', 'French','') AS [Language],"
        sSQL = sSQL & " Exam.ExamDate, Exam.ExamTime"
        sSQL = sSQL & " FROM Student INNER JOIN (Instructors INNER JOIN Exam ON Instructors.InstructorID = Exam.InstructorID)"
        sSQL = sSQL & " ON Student.StudentID = Exam.StudentID"
        sSQL = sSQL & " WHERE Exam.SerialID = " & Me.SerialID
        sSQL = sSQL & " And StudentCourse.CourseStatus In ('Registered','In Progress','Completed','Failed Exam','Failed Course')"
        sSQL = sSQL & " ORDER BY Student.LastName, Student.FirstNames;"
        Debug.Print sSQL
    Note that I use sSQL because SQL is a reserved word. "Name" is also a reserved word. Reserved words shouldn't be used as object names.

    The line "Debug.Print sSQL" will print the SQL to the immediate window.
    Copy the SQL in the immediate window and paste it into a query, then try and to execute it.
    If the query won't execute, you can fix the query. Once the query executes, modify the code.

  11. #11
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    Thanks for the pointers ssanfu - your format is far easier to read for me - I copied and pasted your code directly into the vba module and I get a

    Run-time error '3061';
    Too few parameters 1.

    This occurs on the very next line after the Debug.Print sSQL

    Set rs = db.OpenRecordset(sSQL)

    the debug print out is as follows

    SELECT Exam.SerialID, Student.ProperLastName, Student.ProperFirstName, IIf(IsNull([Student]![ProperLastName]),StrConv([Student]![LastName],3),[Student]![ProperLastName]) AS LName, IIf(IsNull([Student]![ProperFirstName]),StrConv([Student]![FirstNames],3),[Student]![ProperFirstName]) AS FName, Student.DateofBirth, Student.Address, Student.PostalCode, Student.City, Student.Prov, 'Yes' AS [40 hour course completed], Exam.ExamID, Exam.Attempt, IIf([Student].[Prov Exam Language] = '2', 'French','') AS [Language], Exam.ExamDate, Exam.ExamTime FROM Student INNER JOIN (Instructors INNER JOIN Exam ON Instructors.InstructorID = Exam.InstructorID) ON Student.StudentID = Exam.StudentID WHERE Exam.SerialID = 280 And StudentCourse.CourseStatus In ('Registered','In Progress','Completed','Failed Exam','Failed Course') ORDER BY Student.LastName, Student.FirstNames;

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I see it now. This is "The Case of the Disappearing Table"


    The FROM clause is:
    Code:
    FROM Student INNER JOIN (Instructors INNER JOIN Exam ON  Instructors.InstructorID = Exam.InstructorID) ON Student.StudentID =  Exam.StudentID
    There are 3 tables: Student, Instructors and Exam.


    The WHERE clause is:
    Code:
    WHERE Exam.SerialID = 280 And StudentCourse.CourseStatus In  ('Registered','In Progress','Completed','Failed Exam','Failed Course')  ORDER BY Student.LastName, Student.FirstNames;
    Note that the table StudentCourse is NOT in the FROM clause!!
    Hence the error.... there is not a field StudentCourse.CourseStatus.

  13. #13
    Rickochezz is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    45
    Well ssanfu - that cleared up the error - imagine that, when you add clauses you should include the initiating table - thanks for finding that - muchly appreciated

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Slapping forehead, ouch!
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with code - need an if null statement
    By NikoTheBowHunter in forum Access
    Replies: 4
    Last Post: 06-08-2017, 08:07 AM
  2. Using a WHERE clause in an SQL statement
    By john134 in forum Programming
    Replies: 3
    Last Post: 09-13-2016, 05:40 PM
  3. Error FROM clause of your SQL statement
    By Stephanie53 in forum Forms
    Replies: 8
    Last Post: 05-15-2013, 04:04 PM
  4. Switch Statement in WHERE Clause
    By Gray in forum Queries
    Replies: 1
    Last Post: 06-02-2011, 06:50 AM
  5. Use of COLLATE statement in SELECT clause
    By zurek in forum Queries
    Replies: 7
    Last Post: 03-16-2011, 06:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums