CASE Statement in access 2003 on forms

[sichilaba]

Hi

I have created a table known as STUDENTS with the following fields

Name (Text)
ID number (text)
Mark (Number)
Result (Text)

Then created a bound form based on RESULT TABLE.

I need to use CASE Statement on my form in the result text box on form:
When a student
gets less than 50, its a fail
gets 50 to 60, its a pass
gets 61 to 70, its a credit
gets 71 to 80, its a merit
gets 81 to 100, its a distintion

Please help on how l can code this on the form

 

[vbaInet]

Your table doesn't look normalized. What does the Name and ID fields stand for?

 

[Brianwarnock]

VBA isn't it just a simple flat file DB, tho' why ID number should be text is a bit odd.

Write the code in the forms on current event.

Brian

 

[vbaInet]

I think it's relational. Name could either be the name of the module or the Students name. If the latter is the case and ID is the Student's ID then there's a problem.

 

[Brianwarnock]

Ah! I see where you are coming from.

Brian

 

[vbaInet]

It may just be a simple flat file like you mentioned though.

Here's a link for the OP re Select Case:

http://www.techonthenet.com/access/functions/advanced/case.php

 

[missinglinq]

Private Sub Mark_AfterUpdate()
 Select Case Mark.Value
  Case Is < 50
   Me.Result = "Fail"
  Case 50 To 60
   Me.Result = "PassFail"
  Case 61 To 70
   Me.Result = "Credit"
  Case 71 To 80
   Me.Result = "Merit"
  Case 81 To 100
   Me.Result = "Distinction"
 End Select
End Sub

Couple of things.

Being a calculated field, Result really shouldn't be stored in the form, but rather re-calculated when needed. Placing the above code in the FormCurrent event, as Brian suggested, as well as the AfterUpdate event, would do that.

Name is a reserved word in Access and should not be used as a field name. Sooner or later it will jump up and bite you in a place you'd rather not be bitten in!

"why ID number should be text is a bit odd. "

Not at all sure about Brian's comment. Unless math is going to be done with ID number there's no reason for it to not to be defined as Text. There's a difference between a string made up of digits and an actual number.

Lastly, you might want to add a Case Else statement should Mark's Value not meet any of the listed values.

Linq ;0)>

 

[sichilaba]

Re: CASE Statement in access 2003 on forms REPHRASED

Hi

Thanks for all the replies. Let me just just rephrase the whole problem.

Here is the structure of the Table l created as STUDENTS
Field Name Data Type
StudentName (Text)
StudentID Number (Number)
StudentMark (Number
StudentResult (Text)

Created a form aftewards based on Students Table.

I have put the code below for the StudentResult textbox but it is NOT producing the result. Where am l missing it. Please check the code.

Private Sub StudentResult_afterupdate()

Dim StudentMark As Integer
Dim StudentResult As String

Select Case StudentMark.interger
Case Is < 50
Me.StudentResult = "Fail"
Case 51 To 60
Me.StudentResult = "Pass"
Case 61 To 70
Me.StudentResult = "Credit"
Case 71 To 80
Me.StudentResult = "Merit"
Case 81 To 90
Me.StudentResult = "Distinction"
End Select
End Sub

 

[Brianwarnock]

The code is in the wrong place please read the thread again.

Brian

 

[vbaInet]

Re: CASE Statement in access 2003 on forms REPHRASED

Here is the structure of the Table l created as STUDENTS
Field Name Data Type
StudentName (Text)
StudentID Number (Number)
StudentMark (Number
StudentResult (Text)

As suspected Brian.

 

[Brianwarnock]

I refuse to operate over 2 threads but did take a look at your DB, the code has a number of errors perhaps you have corrected them from David's code., but for some reason you have taken no notice of both Linq and I telling you to use the forms on current event and I see that Linq did not make it clear that it is the after update of the Mark control that you may also need to place the code incase you change the mark, The Result is being updated by your code.

Brian