ElseIf Problem

[Steve_God]

Hi,

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

"Grant - Stage 1";
="RESOLVED – That the " & [SCHOOL TYPE] & " has failed to demonstrate that the admission of an additional pupil to " & [SCHOOL] & " " & [PRIMARY/SECONDARY] & " School in " & [YEAR APPLIED FOR] & " would prejudice the provision of efficient education and the efficient use of resources and that accordingly the " & [SCHOOL TYPE] & " be requested to make a place available."

"Grant - Stage 2";
="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 " & [SCHOOL TYPE] & " to refuse a place at " & [SCHOOL] & " " & [PRIMARY/SECONDARY] & " School in " & [YEAR APPLIED FOR] & " be upheld on the grounds that the case put forward by the parents outweighed the prejudice established by the " & [SCHOOL TYPE] & ", and that a place be made available at " & [SCHOOL] & " " & [PRIMARY/SECONDARY] & " School."


"Refused";
="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 " & [SCHOOL TYPE] & " to refuse a place at " & [SCHOOL] & " " & [PRIMARY/SECONDARY] & " School in " & [YEAR APPLIED FOR] & " be dismissed on the grounds that the Panel was satisfied that to allow the appeal and to allocate a place at " & [SCHOOL] & " " & [PRIMARY/SECONDARY] & " 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] & " " & [PRIMARY/SECONDARY] & " School."


"Deferred";
="RESOLVED – That consideration of the appeal be deferred."

"Withdrawn";
="The Clerk informed the Panel that the appeal had been withdrawn."

"Grant - Maladministration (Infant Class Size Appeal)";
="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."

"Grant - Unreasonable (Infant Class Size Appeal)";
="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."

"Refused (Infant Class Size Appeal)";
="RESOLVED – That the appeal be refused on the grounds of class size prejudice."

The issue I have is trying to find a way to get these outcomes to display properly in the report.
I have already tried having these text outcomes in a table, but when the report picks them up, it doesn't process the other referenced items, such as [SCHOOL].

My current thought on the best way of doing this is to use nested ElseIf statements, using the following code, (assuming that I would have a new field called [MinuteText] in the query.)

If [Cases.OUTCOME] = "Grant - Stage 1" Then
[MinuteText] = "RESOLVED – That the " & [SCHOOL TYPE] & " has failed to demonstrate that the admission of an additional pupil to " & [SCHOOL] & " " & [PRIMARY/SECONDARY] & " School in " & [YEAR APPLIED FOR] & " would prejudice the provision of efficient education and the efficient use of resources and that accordingly the " & [SCHOOL TYPE] & " be requested to make a place available."

ElseIf [Cases.OUTCOME] = "Grant - Stage 2" Then
[MinuteText] = "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 " & [SCHOOL TYPE] & " to refuse a place at " & [SCHOOL] & " " & [PRIMARY/SECONDARY] & " School in " & [YEAR APPLIED FOR] & " be upheld on the grounds that the case put forward by the parents outweighed the prejudice established by the " & [SCHOOL TYPE] & ", and that a place be made available at " & [SCHOOL] & " " & [PRIMARY/SECONDARY] & " School."

ElseIf [Cases.OUTCOME] = "Refused" Then
[MinuteText] = "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 " & [SCHOOL TYPE] & " to refuse a place at " & [SCHOOL] & " " & [PRIMARY/SECONDARY] & " School in " & [YEAR APPLIED FOR] & " be dismissed on the grounds that the Panel was satisfied that to allow the appeal and to allocate a place at " & [SCHOOL] & " " & [PRIMARY/SECONDARY] & " 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] & " " & [PRIMARY/SECONDARY] & " School."

ElseIf [Cases.OUTCOME] = "Deferred" Then
[MinuteText] = "RESOLVED – That consideration of the appeal be deferred."

ElseIf [Cases.OUTCOME] = "Withdrawn" Then
[MinuteText] = "The Clerk informed the Panel that the appeal had been withdrawn."

ElseIf [Cases.OUTCOME] = "Grant - Maladministration (Infant Class Size Appeal)" Then
[MinuteText] = "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."

ElseIf [Cases.OUTCOME] = "Grant - Unreasonable (Infant Class Size Appeal)" Then
[MinuteText] = "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."

ElseIf [Cases.OUTCOME] = "Refused (Infant Class Size Appeal)" Then
[MinuteText] = "RESOLVED – That the appeal be refused on the grounds of class size prejudice."

Else
[MinuteText] = "Error!"

End If

However... I'm not sure:
(a) what I need to do to get the query to reference this code; what type of VBA function it needs to be, and; where I then put this code.
(b) If it will actually work as hoped when it's in place!

Thanks in advance!
- Steve

 

[DCrake]

If this was my problem I would do it this way.

First I would create a public function called Outcome()

Public Function Outcome(OutcomeType,SchoolType, School,PrimarySecondary,AppliedYear) As String

Dim strOutcome As String

'Evaluate the outcome type

Select Case OutcomeType

    Case "Grant - Stage 1"


          strOutcome ="RESOLVED – That the " & SchoolType & " has failed to demonstrate that the admission of an additional pupil to " & School & " " & PrimarySecondary & " School in " & AppliedYear & " 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"
          strOutcome = "RESOLVED – (1) That Panel were satisfied that the admissions procedure had been applied correctly in the circumstances of the case.

     Case Etc

End Select

Outcome = strOutcome

End Function

Then in my query that contained the necessary fields I would have a column

TxtOutCome:Outcome([SCHOOL TYPE],[SCHOOL],[ETC])

This would then evaluate the outcome type and return the correct text block. This would then be used in the report.

David

 

[Steve_God]

Cheers!

Just two quick questions to clarify it as I'm not getting it right for some reason:
- Am I right in assuming the code is just added in a new module? or is there another place that the code should be put?
- In the query field for 'TxtOutCome:Outcome([SCHOOL TYPE],[SCHOOL],[ETC])', should '[Outcome Type]' be included or excluded?

 

[DCrake]

The function that has been created handles serveral pieces of information that is passed to it. You can break them down into to categories.

1. The outcome type for that record
2. The other fields you need toparse into your paragraphs.

So yes you do need to pass it the Outcome type as it willnot have anything to evaluate with the select case statement.

David

 

[Steve_God]

To update: I'm currently getting the following error when trying to run the query:

Undefined function 'MINUTEOUTCOME' in expression.

I'll paste what I've got so far.
Query field for it to get the data into:

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

Full SQL:

SELECT Appeal.Date, Appeal.[Panel Member 1], Appeal.[Panel Member 2], Appeal.[Panel Member 3], Appeal.Chair, Cases.[CASE ID], Cases.[CHILDS CHRISTIAN NAME], Cases.[CHILDS SURNAME], Cases.[DATE OF BIRTH], Cases.[SCHOOL REQUESTED], Schools.SCHOOL, Schools.[PRIMARY/SECONDARY], Schools.[SCHOOL TYPE], Cases.[YEAR APPLIED FOR], Cases.[APPEAL DATE], Cases.TIME, Cases.[APPEAL TYPE], Cases.ATTENDING, Cases.OUTCOME, MINUTEOUTCOME([OUTCOME],[SCHOOL TYPE],[SCHOOL],[PRIMARY/SECONDARY],[YEAR APPLIED FOR]) AS [Minute]
FROM Schools INNER JOIN (Appeal INNER JOIN Cases ON Appeal.Date = Cases.[APPEAL DATE]) ON Schools.ID = Cases.[SCHOOL REQUESTED];

The module, titled 'MINUTEOUTCOME':

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 " & [SCHOOL TYPE] & " has failed to demonstrate that the admission of an additional pupil to " & [School] & " " & [PRIMARY/SECONDARY] & " School in " & [YEAR APPLIED FOR] & " would prejudice the provision of efficient education and the efficient use of resources and that accordingly the " & [SCHOOL TYPE] & " 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 " & [SCHOOL TYPE] & " to refuse a place at " & [School] & " " & [PRIMARY/SECONDARY] & " School in " & [YEAR APPLIED FOR] & " be upheld on the grounds that the case put forward by the parents outweighed the prejudice established by the " & [SCHOOL TYPE] & ", and that a place be made available at " & [School] & " " & [PRIMARY/SECONDARY] & " 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 " & [SCHOOL TYPE] & " to refuse a place at " & [School] & " " & [PRIMARY/SECONDARY] & " School in " & [YEAR APPLIED FOR] & " be dismissed on the grounds that the Panel was satisfied that to allow the appeal and to allocate a place at " & [School] & " " & [PRIMARY/SECONDARY] & " 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] & " " & [PRIMARY/SECONDARY] & " 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

 

[namliam]

Naming conventions, you cannot name a module the same as a function or sub...
Access allows it, but doesnt support it ....

mdlSomeName shoudl be your module name

 

[Steve_God]

Cheers for that namliam!
That removed the 'Undefined function' error by renaming it

I then came across a few errors where I'd got '[YEAR APPLIED FOR]' instead of 'YearAppliedFor' - I've since changed them, and now get no errors when loading the query... but instead get no data in the 'Minute' field.

Code for the query remains unchanged to that above, the code for the mdlMINUTEOUTCOME module is:

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

 

[DCrake]

In your query how are you calling the function?

Should be

Alias:MINUTEOUTCOME([OUTCOME], [School Type], [School], [PrimarySecondary], [YearAppliedFor])

The red items should be the Actual names of the fields in the table.

Just to test the function press Ctrl+G to go to the immediate window and enter

?MINUTEOUTCOME("Grant - Stage 1","School type","School","Prim Sec","Year")

do the items get merged correctly into the correct paragraph?

David

 

[Steve_God]

I'm calling the function using:

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

As they (in the square brackets) are the actual names of the fields in the table.

I've tried testing the function with the immediate window, but with no luck

 

[DCrake]

Can you post a snippet from your mdb to look at?

David

 

[Steve_God]

Can you post a snippet from your mdb to look at?

Linky to DB: http://garratt.googlepages.com/EducationAppealsDatabase.mdb
(was too big to attach)

 

[DCrake]

Issue solved:

1 In your module your select case statement is not evaluating the Outcome Field.

2. One of the case options was incorrectly worded.

3. Need to change change query to read

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


4. Not wise to include a message box in a query, simply report the outcome.

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

Dim strMINUTEOUTCOME As String

'Evaluate the outcome type

Select Case OUTCOME [COLOR="seagreen"]' See point 1[/COLOR]

    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 "[COLOR="Red"]Grant - Maladministration (Inf. Class Size Appeal)"[/COLOR] [COLOR="SeaGreen"]' See point 2[/COLOR]          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
 
    strMINUTEOUTCOME = "Value of OUTCOME is (" & OUTCOME & ")" [COLOR="seagreen"]' See point 4[/COLOR]

End Select

MINUTEOUTCOME = strMINUTEOUTCOME

End Function

Tip!
If you want to produce line breaks in your text include vbCrLf at the relevant points in script.


Eg:

StrText = "This will appear on line 1" & vbcrlf
strtext = strtext & "This will appear on line 2" & vbcrlf
strtext = strtext & "This will appear on line 3" & vbcrlf

David

 

[Steve_God]

David, you are a legend! After those amendments, all is working perfectly!

 

[DCrake]

What you may try and do is to put the outcomes in a table and set up a relationship up. Then in your outcome field you only record a code instead of the full description.

David