By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,759 Members | 1,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,759 IT Pros & Developers. It's quick & easy.

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

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. Minute: MINUTEOUTCOME([OUTCOME],[SCHOOL TYPE],[SCHOOL],[PRIMARY/SECONDARY],[YEAR APPLIED FOR])

And in a module, I have the following:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public Function MINUTEOUTCOME(OUTCOME, SchoolType, School, PrimarySecondary, YearAppliedFor) As String
  4.  
  5. Dim strMINUTEOUTCOME As String
  6.  
  7. 'Evaluate the outcome type
  8.  
  9. Select Case OUTCOME
  10.  
  11. Case "Grant - Stage 1"
  12. 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."
  13.  
  14.  
  15.  
  16. Case "Grant - Stage 2"
  17. strMINUTEOUTCOME = "RESOLVED  (1) That Panel were satisfied that the admissions procedure had been applied correctly in the circumstances of the case." _
  18. & " " _
  19. & "(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."
  20.  
  21.  
  22. Case "Refused"
  23. strMINUTEOUTCOME = "RESOLVED  (1) That Panel were satisfied that the admissions procedure had been applied correctly in the circumstances of the case. " _
  24. & " " _
  25. & "(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."
  26.  
  27.  
  28. Case "Deferred"
  29. strMINUTEOUTCOME = "RESOLVED  That consideration of the appeal be deferred."
  30.  
  31. Case "Withdrawn"
  32. strMINUTEOUTCOME = "The Clerk informed the Panel that the appeal had been withdrawn."
  33.  
  34.  
  35. Case "Maladministration (Inf. Class Size Appeal)"
  36. 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."
  37.  
  38.  
  39. Case "Grant - Unreasonable (Inf. Class Size Appeal)"
  40. 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."
  41.  
  42.  
  43. Case "Refused (Inf. Class Size Appeal)"
  44. strMINUTEOUTCOME = "RESOLVED  That the appeal be refused on the grounds of class size prejudice."
  45.  
  46.  
  47.  
  48. End Select
  49.  
  50. MINUTEOUTCOME = strOutcome
  51.  
  52. 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?
Nov 5 '09 #1
Share this Question
Share on Google+
7 Replies


Megalog
Expert 100+
P: 378
This line:
Expand|Select|Wrap|Line Numbers
  1. MINUTEOUTCOME = strOutcome 
Should be:
Expand|Select|Wrap|Line Numbers
  1. MINUTEOUTCOME =  strMINUTEOUTCOME
Since "strOutcome" isnt defined in your procedure, I'm not sure why it hasnt errored out at this point.
Nov 5 '09 #2

Expert 100+
P: 1,287
That happens when you don't use
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
Nov 5 '09 #3

P: 3
Megalog: Many thanks! I've made that change, but still no luck.

ChipR: Do I just need to insert 'Option Explicit' within the Public Function?
Assuming the answer to that is yes... I'm now getting Compile error: "Invalid inside procedure", highlighting:
Expand|Select|Wrap|Line Numbers
  1. Public Function MINUTEOUTCOME(OUTCOME, SchoolType, School, PrimarySecondary, YearAppliedFor) As String
Nov 6 '09 #4

P: 3
Problem Solved!
My 'Select Case' should have read 'Outcome' rather than 'MinuteOutcome'
:)
Nov 6 '09 #5

Expert 100+
P: 1,287
It goes at the very top of every module after Option Compare Database. Glad you got it working in the meantime.
Nov 6 '09 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
For some strange reason in recent versions of Access Microsoft doesn't set this by default. ( A very bad thing in my opinion as it would save people a lot of heartache).

Open the VB Editor window and go to Tools - Options. Tick the box beside "Require Variable Declaration". This will ensure that all modules, form modules, classes etc. will automatically have "Option Explicit" put in. All this means is you can't use a variable unless you declare it. Common Sense really.

Mary
Nov 8 '09 #7

NeoPa
Expert Mod 15k+
P: 31,426
Require Variable Declaration may help.

The reason Microsoft have the default set to omit this very important line is that it helps to hook the inexperienced into the game. The first steps are easier. If it causes great problems later on then clearly they are not picking up the pieces ;)
Nov 8 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.