Results 1 to 5 of 5
  1. #1
    steve_god is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    3

    Question Issues with 'Select' function - Trying to call up data into a query


    Hi,

    I'm trying to create a report that will produce automated sets of Committee Minutes for School Appeals, where there are a set range of different outcomes.

    The field involved are all from the same table, and called:
    [OUTCOME], [SCHOOL TYPE], [SCHOOL], [PRIMARY/SECONDARY] and [YEAR APPLIED FOR]

    In the query where the result of the outcome in needed, it has the following in a field:
    Code:
    Minute: MINUTEOUTCOME([OUTCOME],[SCHOOL TYPE],[SCHOOL],[PRIMARY/SECONDARY],[YEAR APPLIED FOR])

    And in a module, I have the following:
    Code:
    Option Compare Database
    
    Public Function MINUTEOUTCOME(OUTCOME, SchoolType, School, PrimarySecondary, YearAppliedFor) As String
    
    Dim strMINUTEOUTCOME As String
    
    'Evaluate the outcome type
    
    Select Case OUTCOME
    
    Case "Grant - Stage 1"
    strMINUTEOUTCOME = "RESOLVED – That the " & SchoolType & " has failed to demonstrate that the admission of an additional pupil to " & School & " " & PrimarySecondary & " School in " & YearAppliedFor & " would prejudice the provision of efficient education and the efficient use of resources and that accordingly the " & SchoolType & " be requested to make a place available."
    
    
    
    Case "Grant - Stage 2"
    strMINUTEOUTCOME = "RESOLVED – (1) That Panel were satisfied that the admissions procedure had been applied correctly in the circumstances of the case." _
    & " " _
    & "(2) That the appeal against the decision of the " & SchoolType & " to refuse a place at " & School & " " & PrimarySecondary & " School in " & YearAppliedFor & " be upheld on the grounds that the case put forward by the parents outweighed the prejudice established by the " & SchoolType & ", and that a place be made available at " & School & " " & PrimarySecondary & " School."
    
    
    Case "Refused"
    strMINUTEOUTCOME = "RESOLVED – (1) That Panel were satisfied that the admissions procedure had been applied correctly in the circumstances of the case. " _
    & " " _
    & "(2) That the appeal against the decision of the " & SchoolType & " to refuse a place at " & School & " " & PrimarySecondary & " School in " & YearAppliedFor & " be dismissed on the grounds that the Panel was satisfied that to allow the appeal and to allocate a place at " & School & " " & PrimarySecondary & " School would be prejudicial to the provision of efficient education and the efficient use of resources to such an extent as to override the reasons put forward by the parent in preferring " & School & " " & PrimarySecondary & " School."
    
    
    Case "Deferred"
    strMINUTEOUTCOME = "RESOLVED – That consideration of the appeal be deferred."
    
    Case "Withdrawn"
    strMINUTEOUTCOME = "The Clerk informed the Panel that the appeal had been withdrawn."
    
    
    Case "Maladministration (Inf. Class Size Appeal)"
    strMINUTEOUTCOME = "RESOLVED – That the appeal be granted on the basis that the child would have been offered a place if the admission arrangements had been properly implemented."
    
    
    Case "Grant - Unreasonable (Inf. Class Size Appeal)"
    strMINUTEOUTCOME = "RESOLVED – That the appeal be granted on the basis that the decision to refuse a place was not one a reasonable authority would have made in the circumstances of the case."
    
    
    Case "Refused (Inf. Class Size Appeal)"
    strMINUTEOUTCOME = "RESOLVED – That the appeal be refused on the grounds of class size prejudice."
    
    
    
    End Select
    
    MINUTEOUTCOME = strOutcome
    
    End Function

    Although I've eradicated a number of error messages from my original attempts, when the query runs, it bring up no data.

    Can anyone spot where I may have done wrong?

  2. #2
    CGM3 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Atlanta, GA
    Posts
    49
    I suggest this as a debug step:

    Code:
    Option Compare Database
     
    Public Function MINUTEOUTCOME(OUTCOME, SchoolType, School, PrimarySecondary, YearAppliedFor) As String
     
    Dim strMINUTEOUTCOME As String
     
    'Evaluate the outcome type
     
    Select Case OUTCOME
     
    Case "Grant - Stage 1"
    strMINUTEOUTCOME = "RESOLVED – That the " & SchoolType & " has failed to demonstrate that the admission of an additional pupil to " & School & " " & PrimarySecondary & " School in " & YearAppliedFor & " would prejudice the provision of efficient education and the efficient use of resources and that accordingly the " & SchoolType & " be requested to make a place available."
     
     
     
    Case "Grant - Stage 2"
    strMINUTEOUTCOME = "RESOLVED – (1) That Panel were satisfied that the admissions procedure had been applied correctly in the circumstances of the case." _
    & " " _
    & "(2) That the appeal against the decision of the " & SchoolType & " to refuse a place at " & School & " " & PrimarySecondary & " School in " & YearAppliedFor & " be upheld on the grounds that the case put forward by the parents outweighed the prejudice established by the " & SchoolType & ", and that a place be made available at " & School & " " & PrimarySecondary & " School."
     
     
    Case "Refused"
    strMINUTEOUTCOME = "RESOLVED – (1) That Panel were satisfied that the admissions procedure had been applied correctly in the circumstances of the case. " _
    & " " _
    & "(2) That the appeal against the decision of the " & SchoolType & " to refuse a place at " & School & " " & PrimarySecondary & " School in " & YearAppliedFor & " be dismissed on the grounds that the Panel was satisfied that to allow the appeal and to allocate a place at " & School & " " & PrimarySecondary & " School would be prejudicial to the provision of efficient education and the efficient use of resources to such an extent as to override the reasons put forward by the parent in preferring " & School & " " & PrimarySecondary & " School."
     
     
    Case "Deferred"
    strMINUTEOUTCOME = "RESOLVED – That consideration of the appeal be deferred."
     
    Case "Withdrawn"
    strMINUTEOUTCOME = "The Clerk informed the Panel that the appeal had been withdrawn."
     
     
    Case "Maladministration (Inf. Class Size Appeal)"
    strMINUTEOUTCOME = "RESOLVED – That the appeal be granted on the basis that the child would have been offered a place if the admission arrangements had been properly implemented."
     
     
    Case "Grant - Unreasonable (Inf. Class Size Appeal)"
    strMINUTEOUTCOME = "RESOLVED – That the appeal be granted on the basis that the decision to refuse a place was not one a reasonable authority would have made in the circumstances of the case."
     
     
    Case "Refused (Inf. Class Size Appeal)"
    strMINUTEOUTCOME = "RESOLVED – That the appeal be refused on the grounds of class size prejudice."
     
     
    Case Else
     
      Msgbox "Value of OUTCOME is (" & OUTCOME & ")", vbOKOnly, "Bad Value"
     
    End Select
     
    MINUTEOUTCOME = strMINUTEOUTCOME
     
    End Function
    This should show you the value(s) of OUTCOME that's "slipping through the cracks".

  3. #3
    steve_god is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    3
    I've made those changes, and several message boxes came up such as:
    Bad Value
    Value of OUTCOME is (Grant - Stage 1)
    It brought up one for each record that the query, each giving the relevant Outcome result for that record.

  4. #4
    steve_god is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    3
    Problem Solved!
    My 'Select Case' should have read 'Outcome' rather than 'MinuteOutcome'

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks for sharing your solution. Are you ready to follow the link in my sig and mark this thread solved?

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

Similar Threads

  1. how to call a sub procedure?
    By dollygg in forum Access
    Replies: 1
    Last Post: 08-18-2009, 05:10 AM
  2. Shared DB data retention issues
    By tnmz6i in forum Access
    Replies: 0
    Last Post: 02-03-2009, 11:08 AM
  3. Select query with two criteria
    By corni in forum Queries
    Replies: 1
    Last Post: 01-22-2009, 05:23 AM
  4. Call Excel Data into Access table
    By jiguvaidya in forum Import/Export Data
    Replies: 0
    Last Post: 09-15-2008, 04:58 AM
  5. SELECT FUNCTION help please
    By scott munkirs in forum Queries
    Replies: 0
    Last Post: 10-17-2006, 07:44 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