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

[SteveGod]

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:

Minute: MINUTEOUTCOME([OUTCOME],[SCHOOL TYPE],[SCHOOL],[PRIMARY/SECONDARY],[YEAR APPLIED FOR])

And in a module, I have the following:

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?

[Megalog]

This line:

MINUTEOUTCOME = strOutcome

Should be:

MINUTEOUTCOME =  strMINUTEOUTCOME

Since "strOutcome " isnt defined in your procedure, I'm not sure why it hasnt errored out at this point.

[ChipR]

That happens when you don't use

Option Explicit

[SteveGod]

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:

Public Function MINUTEOUTCOME(OUTCOME, SchoolType, School, PrimarySecondary, YearAppliedFor) As String

[SteveGod]

Problem Solved!
My 'Select Case' should have read 'Outcome' rather than 'MinuteOutcome'
:)

[ChipR]

It goes at the very top of every module after Option Compare Database. Glad you got it working in the meantime.

[MMcCarthy]

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

[NeoPa]

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 ;)