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:

Expand|Select|Wrap|Line Numbers

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

And in a module, I have the following:

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

MINUTEOUTCOME = strOutcome

Should be:

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

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:

Expand|Select|Wrap|Line Numbers

 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.

msquared

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

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

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

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

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

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

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

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

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

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about