Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Weird Query Problem

    I seem to be getting some unusual results from a query that I've made.

    When I load up the db, I get a main menu form where I select a checkbox, and year from a drop down value list.
    The query looks at various tables from an Oracle DB about students, and only retreives certain information relating to that year.
    When I run the query it seems to work fine, but when it displays the information into the form, it displays incorrect year for some reason.

    So for example, if I select 2005 from the value list, in the form it could return the year as 2002 or 2004. But if I run the query all the records return are for the year 2005.

    The query uses the WHERE [form]![frmMenu]![cmboYear] under the Year field to retrieve the data.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Are you ensuring that the form requreies it's record source when you select an academic year?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Hi pootle,

    Just realised that it's that damn SQL we were working on, and that's the reason it's not working. I forgot that it was running the SQL and then inserting the data, so I need to add that criteria to it.

    Could you check the SQL for me, it's starting to annoy me that I can't simply copy the SQL from a query I have made and paste it into vba.

    Here it is:

    Code:
    sSQL = "SELECT DISTINCTROW QUERCUS_PERSON.ID_NUMBER AS ID," & _
    " QUERCUS_PERSON.FIRST_NAME AS FN, QUERCUS_PERSON.SURNAME AS SN, " & _
    " QUERCUS_PERSON.SURNAME, QUERCUS_COURSE.DESCRIPTION, QUERCUS_COURSE_INSTANCE.ACADEMIC_YEAR, " & _
    " QUERCUS_COURSE_INSTANCE.START_DATE, QUERCUS_COURSE_INSTANCE.END_DATE " & _
    " FROM QUERCUS_PERSON INNER JOIN ((((QUERCUS_STUDENT_COURSE_DETAIL INNER JOIN QUERCUS_COURSE_INSTANCE ON QUERCUS_STUDENT_COURSE_DETAIL.COURSE_INSTANCE = QUERCUS_COURSE_INSTANCE.OBJECT_ID)" & _
    " INNER JOIN QUERCUS_MODE_OF_STUDY ON QUERCUS_COURSE_INSTANCE.MODE_OF_STUDY = QUERCUS_MODE_OF_STUDY.OBJECT_ID)" & _
    " INNER JOIN QUERCUS_COURSE ON QUERCUS_COURSE_INSTANCE.COURSE = QUERCUS_COURSE.OBJECT_ID)" & _
    " INNER JOIN QUERCUS_STATUS ON QUERCUS_STUDENT_COURSE_DETAIL.STATUS = QUERCUS_STATUS.OBJECT_ID) ON QUERCUS_PERSON.OBJECT_ID = QUERCUS_STUDENT_COURSE_DETAIL.PERSON" & _
    " WHERE (((QUERCUS_COURSE_INSTANCE.ACADEMIC_YEAR)=[forms]![frmStudent_Placement]![txtYear]) AND ((QUERCUS_COURSE.COURSE)=[forms]![frmStudent_Placement]![cmboCourse])" & _
    " AND ((QUERCUS_COURSE_INSTANCE.COURSE_YEAR)=[forms]![frmStudent_Placement]![cmboCourse_Year])" & _
    " AND ((QUERCUS_MODE_OF_STUDY.MODE_OF_STUDY) In ("FULL-TIME_FULL_Y","SANDWICH","FULL-TIME_LESS_T","FTS","04"))" & _
    " AND ((QUERCUS_STATUS.STATUS)="R"))" & _
    " AND QUERCUS_PERSON.ID_NUMBER = " & Me.cmboStudent & _
    " GROUP BY QUERCUS_PERSON.FIRST_NAME, QUERCUS_PERSON.SURNAME, QUERCUS_PERSON.TITLE, QUERCUS_PERSON.MIDDLE_NAME, QUERCUS_COURSE.DESCRIPTION, QUERCUS_COURSE_INSTANCE.START_DATE, QUERCUS_COURSE_INSTANCE.END_DATE" & _
    " ORDER BY QUERCUS_PERSON.SURNAME
    I look at the immediate view, but it just comes up with:

    Code:
    SELECT Org_ID, Name FROM tblOrganisation WHERE Org_ID=1
    Which is no where near what I want the SQL to do. Also it keeps highlighting the following saying, expected end of statement:

    Code:
    AND ((QUERCUS_MODE_OF_STUDY.MODE_OF_STUDY) In ("FULL-TIME_FULL_Y","SANDWICH","FULL-TIME_LESS_T","FTS","04"))
    Last edited by KevCB226; 10-03-05 at 10:10.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Kev

    You need to replace all "s (double quotes) in the QBE SQL to 's (single quotes) for VBA (or you can concatenate Chr(34)s instead - I prefer the former). The " are interpreted by VB as the opening\ closing of a string, not the contents of a string.

    Worth making a note of these sorts of things and do something like find and replace in Notepad.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Another thing I would do (though not essential) is to remove all the extraneous parentheses in the WHERE clause - Access inserts loads when it creates SQL. Getting rid helps readability and can help you in the early days of learning to write it - it shouldn't make any difference to the excution though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2005
    Posts
    240
    Great thanks, just changed them to single quotes and it has worked, I'll remember that for future reference.

    Although I'm now getting a different error:

    Run-time error '3122': You tried to execute a query that does not include the specified expression 'ID_NUMBER' as part of an aggregate function.
    What's all that about?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You've either added QUERCUS_PERSON.ID_NUMBER AS ID to the select clause or removed it from the Group By clause. Anything in the select clause must be in the group by clause (unless it is an aggregate e.g. MIN, Max, SUM, FIRST, COUNT etc). Also, Distinctrow is redundant in a Group by Query - I'd knock it out.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2005
    Posts
    240
    Alright, knocked out the Group By and Order By lines, and still isn't working.

    Now it's coming up with one of those 'Expected 3 parameters' mesaages.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol - you are a bit haphazard with your SQL aren't you? What SQL do you have now?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Sep 2005
    Posts
    240
    Lol, I'm still trying to get my head round doing it in vba. It just looks a mess of code to me

    Anyway this is what I have now:

    Code:
    sSQL = "SELECT QUERCUS_PERSON.ID_NUMBER," & _
    " QUERCUS_PERSON.FIRST_NAME, QUERCUS_PERSON.SURNAME," & _
    " QUERCUS_PERSON.SURNAME, QUERCUS_COURSE.DESCRIPTION, QUERCUS_COURSE_INSTANCE.ACADEMIC_YEAR," & _
    " QUERCUS_COURSE_INSTANCE.START_DATE, QUERCUS_COURSE_INSTANCE.END_DATE" & _
    " FROM QUERCUS_PERSON INNER JOIN (((((QUERCUS_STUDENT_COURSE_DETAIL INNER JOIN QUERCUS_COURSE_INSTANCE ON QUERCUS_STUDENT_COURSE_DETAIL.COURSE_INSTANCE = QUERCUS_COURSE_INSTANCE.OBJECT_ID)" & _
    " INNER JOIN QUERCUS_MODE_OF_STUDY ON QUERCUS_COURSE_INSTANCE.MODE_OF_STUDY = QUERCUS_MODE_OF_STUDY.OBJECT_ID)" & _
    " INNER JOIN QUERCUS_COURSE ON QUERCUS_COURSE_INSTANCE.COURSE = QUERCUS_COURSE.OBJECT_ID)" & _
    " INNER JOIN QUERCUS_STATUS ON QUERCUS_STUDENT_COURSE_DETAIL.STATUS = QUERCUS_STATUS.OBJECT_ID)) ON QUERCUS_PERSON.OBJECT_ID = QUERCUS_STUDENT_COURSE_DETAIL.PERSON" & _
    " WHERE (((QUERCUS_COURSE_INSTANCE.ACADEMIC_YEAR)=[forms]![frmStudent_Placement]![txtYear]) AND ((QUERCUS_COURSE.COURSE)=[forms]![frmStudent_Placement]![cmboCourse])" & _
    " AND ((QUERCUS_COURSE_INSTANCE.COURSE_YEAR)=[forms]![frmStudent_Placement]![cmboCourse_Year])" & _
    " AND ((QUERCUS_MODE_OF_STUDY.MODE_OF_STUDY) In ('FULL-TIME_FULL_Y','SANDWICH','FULL-TIME_LESS_T','FTS','04'))" & _
    " AND ((QUERCUS_STATUS.STATUS)='R'))" & _
    " AND QUERCUS_PERSON.ID_NUMBER = " & Me.cmboStudent

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - now you have Surname entered twice. You can have it in twice (though god knows why you would want to) but you need to alias one of the cols e.g.

    Code:
    SELECT Surname, Surname AS Surname2
    FROM MyTable
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Sep 2005
    Posts
    240
    Woops, one was meant to be MIDDLE_NAME. Anyway I have changed that, but still it comes up with the same error.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - VBA SQL - you are building up a string that is to be executed as a SQL statement. Check out your where clause - notice how the combo is handled differently to the other form controls? You need to concatenate these values into the string in the same way.

    Code:
     
    " WHERE (((QUERCUS_COURSE_INSTANCE.ACADEMIC_YEAR)=[forms]![frmStudent_Placement]![txtYear]) AND ((QUERCUS_COURSE.COURSE)=[forms]![frmStudent_Placement]![cmboCourse])" & _
    " AND ((QUERCUS_COURSE_INSTANCE.COURSE_YEAR)=[forms]![frmStudent_Placement]![cmboCourse_Year])" & _
    " AND ((QUERCUS_MODE_OF_STUDY.MODE_OF_STUDY) In ('FULL-TIME_FULL_Y','SANDWICH','FULL-TIME_LESS_T','FTS','04'))" & _
    " AND ((QUERCUS_STATUS.STATUS)='R'))" & _
    " AND QUERCUS_PERSON.ID_NUMBER = " & Me.cmboStudent
    Green = Good
    Red = Bad
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Sep 2005
    Posts
    240
    OK sorted it, now, I've got to look for an extra bracket that shouldn't be there.
    Last edited by KevCB226; 10-03-05 at 12:35.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually I would use:

    Code:
     
    Me.txtYear
    Two reasons -
    Quicker for Access to resulve at runtime and you get use of intellisense.

    However - the issue is more to do with concatentation. Notice the & at the end of the string - you want to emulate this. Perhaps look up copncatenation in Help.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •