Public Function Compile Error - Microsoft Access / VBA

Penfold

I appreciated the help I received through this newsgroup some time ago to
build a module to change a students score into a curriculum level eg score
of 24 gives a grade of "6a". However, I am now struggling to build a module
that does the opposite! That is to take a grade and allocate it a score. I
looked at the first module (listed below) and changed it around to give what
I thought was a logical solution. But it don't work! Error says something
about a missing object. I don't know enough to fix it, could anyone please
point me in the right direction? It would also be useful to know if I can
use the same module for a range of conversions in the same query or does
each conversion need it's own query?

Original Module was...

Public Function GetGrade(AvgSco re As Double) As String

AvgScore = Nz(AvgScore, 0)

Select Case AvgScore

Case Is >= 36

GetGrade = "8a"

Case Is >= 34

GetGrade = "8b"

Case Is >= 32

GetGrade = "8c"

Case Is >= 30

GetGrade = "7a"

Case Is >= 28

GetGrade = "7b"

Case Is >= 26

GetGrade = "7c"

Case Is >= 24

GetGrade = "6a"

Case Is >= 22

GetGrade = "6b"

Case Is >= 20

GetGrade = "6c"

Case Is >= 18

GetGrade = "5a"

Case Is >= 16

GetGrade = "5b"

Case Is >= 14

GetGrade = "5c"

Case Is >= 12

GetGrade = "4a"

Case Is >= 10

GetGrade = "4b"

Case Is >= 8

GetGrade = "4c"

Case Is >= 6

GetGrade = "3a"

Case Is >= 4

GetGrade = "3b"

Case Is >= 2

GetGrade = "3c"

Case Is 0

GetGrade = "W"

Case Else

GetGrade = "U"

End Select

End Function

My attempt at reversing it was...

Public Function GetScore(NCLGym As Text) As String
NCLGym = Nz(NCLGym, 0)
Select Case NCLGym
Case Is = "8a"
GetScore = 36
Case Is = "8b"
GetScore = 34
Case Is = "8c"
GetScore = 32
Case Is = "7a"
GetScore = 30
Case Is = "7b"
GetScore = 28
Case Is = "7c"
GetScore = 26
Case Is = "6a"
GetScore = 24
Case Is = "6b"
GetScore = 22
Case Is = "6c"
GetScore = 20
Case Is = "5a"
GetScore = 18
Case Is = "5b"
GetScore = 16
Case Is = "5c"
GetScore = 14
Case Is = "4a"
GetScore = 12
Case Is = "4b"
GetScore = 10
Case Is = "4c"
GetScore = 8
Case Is = "3a"
GetScore = 6
Case Is = "3b"
GetScore = 4
Case Is = "3c"
GetScore = 2
Case Is = "W"
GetScore = 0
Case Else
GetScore = "U"
End Select
End Function

Thanks in anticipation from a Virtual (Basic) Virgin!.

Jul 17 '06 #1

Subscribe Reply

1332

pietlinden

My attempt at reversing it was...

>
Public Function GetScore(NCLGym As Text) As String
NCLGym = Nz(NCLGym, 0)
Select Case NCLGym
Case Is = "8a"
GetScore = 36
Case Is = "8b"
GetScore = 34
Case Is = "8c"
GetScore = 32
Case Is = "7a"
GetScore = 30
Case Is = "7b"
GetScore = 28
Case Is = "7c"
GetScore = 26
Case Is = "6a"
GetScore = 24
Case Is = "6b"
GetScore = 22
Case Is = "6c"
GetScore = 20
Case Is = "5a"
GetScore = 18
Case Is = "5b"
GetScore = 16
Case Is = "5c"
GetScore = 14
Case Is = "4a"
GetScore = 12
Case Is = "4b"
GetScore = 10
Case Is = "4c"
GetScore = 8
Case Is = "3a"
GetScore = 6
Case Is = "3b"
GetScore = 4
Case Is = "3c"
GetScore = 2
Case Is = "W"
GetScore = 0
Case Else
GetScore = "U"
End Select
End Function

I wouldn't do this with VB at all. I'd use SQL. As long as you have a
fixed number of possible values, just put all the (textscore, numeric
score) combinations in a table, and then just use a simple SQL
statement.

Something along the lines of:

Option Compare Database

Public Function ConvertScore(By Val strScore As String) As Integer
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT NumericScore FROM tblScores WHERE TextScore = '" &
strScore & "';"

Set rs = DBEngine(0)(0). OpenRecordset(s trSQL, dbOpenSnapshot)

If rs.RecordCount = 0 Then
ConvertScore = 0
Else
ConvertScore = rs.Fields(0)
End If

rs.Close
Set rs = Nothing

End Function

Jul 18 '06 #2

Penfold

Thank you for your response.
It worked OK once I changed (NCLGym As Text) to (NCLGym As String).

Regards
Penfold
<pi********@hot mail.comwrote in message
news:11******** **************@ 75g2000cwc.goog legroups.com...

My attempt at reversing it was...

Public Function GetScore(NCLGym As Text) As String
NCLGym = Nz(NCLGym, 0)
Select Case NCLGym
Case Is = "8a"
GetScore = 36
Case Is = "8b"
GetScore = 34
Case Is = "8c"
GetScore = 32
Case Is = "7a"
GetScore = 30
Case Is = "7b"
GetScore = 28
Case Is = "7c"
GetScore = 26
Case Is = "6a"
GetScore = 24
Case Is = "6b"
GetScore = 22
Case Is = "6c"
GetScore = 20
Case Is = "5a"
GetScore = 18
Case Is = "5b"
GetScore = 16
Case Is = "5c"
GetScore = 14
Case Is = "4a"
GetScore = 12
Case Is = "4b"
GetScore = 10
Case Is = "4c"
GetScore = 8
Case Is = "3a"
GetScore = 6
Case Is = "3b"
GetScore = 4
Case Is = "3c"
GetScore = 2
Case Is = "W"
GetScore = 0
Case Else
GetScore = "U"
End Select
End Function

I wouldn't do this with VB at all. I'd use SQL. As long as you have a
fixed number of possible values, just put all the (textscore, numeric
score) combinations in a table, and then just use a simple SQL
statement.

Something along the lines of:

Option Compare Database

Public Function ConvertScore(By Val strScore As String) As Integer
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT NumericScore FROM tblScores WHERE TextScore = '" &
strScore & "';"

Set rs = DBEngine(0)(0). OpenRecordset(s trSQL, dbOpenSnapshot)

If rs.RecordCount = 0 Then
ConvertScore = 0
Else
ConvertScore = rs.Fields(0)
End If

rs.Close
Set rs = Nothing

End Function

Jul 18 '06 #3

Similar topics

1887

function error

by: muser | last post by:

In the following function there is an access violation error, some memory can't be read. A week ago this code did compile. Can anyone possibly tell me why my compiler is unable to read part of this function. bool processdrecord( ofstream& prnfile, ofstream& validdata, char* record ) { drecord Newdrecord; char customercode;

C / C++

8765

typedef private public

by: Aman | last post by:

does it matter if a typedef is declared under public or private ? does any one (public or private ) constitute better design ? -- Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

C / C++

2332

Should 'public virtual' always become 'private virtual'? & using private inheritance

by: qazmlp | last post by:

class base { // other members public: virtual ~base() { } virtual void virtualMethod1()=0 ; virtual void virtualMethod2()=0 ; virtual void virtualMethod3()=0 ;

C / C++

2420

Visual C++ 7.1 INTERNAL COMPILER ERROR -- crossposted clc++ and microsoft.public.vstudio.general

by: Alf P. Steinbach | last post by:

// As usual the error message directs one to the report the bug. // // And as usual there is absolutely no way to do so without paying for // the privilege... // // Or using three or four hours to find the _current_ reporting page... // // Since I'm now using much time on reporting this compiler bug, please // do also fix the __LINE__ macro. //

C / C++

3627

typedef, member function and const meat different compilers

by: joe | last post by:

hi, after reading some articles and faq, i want to clarify myself what's correct(conform to standard) and what's not? or what should be correct but it isn't simply because compilers don't support. (first i compiled them with g++3.x. ERR means compiler will bark, otherwise it does accept it. Then the Comeau C/C++ 4.3.3 comes)

C / C++

2411

public function from class in other namespace inaccessible

by: Peter Speybrouck | last post by:

I have a little problem with a webservice. I reproduced the problem in the following simplified example. I just create a new C# ASP.NET webservice and a c# console application. I added a new class test to the namespace of the webservice which I try to access from the console application. //Service1.asmx.cs

C# / C Sharp

2672

7.1 C++ compiler doesn't catch base class reference without public copy constructor

by: Andrew Schepler | last post by:

When compiled with Visual C++ .NET 2003 (only), the program below aborts as though no matching catch clause is present. If the copy constructor of A is made public, it successfully catches the exception instead. If I step into __CxxThrowException in debug/assembly mode, it looks like the pThrowInfo parameter created by the compiler excluded typeid(A) from pCatchableTypeArray. It would be reasonable to fail to catch A by value because of the...

.NET Framework

1266

Public Function Error

by: Penfold | last post by:

Hi, I benefited from this newsgroups help to compile a public function to convert numeric scores to alphanumeric grades. Now I need to reverse the process to allocate a numeric score to each alphanumeric grade. I tried this below but it just spits out #error for each record. I tried changing "(NCLGym As Double)" to "As Text" but error message said "User defined type not defined". This is my limit and any help would be appreciated.

Microsoft Access / VBA

2929

Why moving a function from protected to public area in class causes error C2556

by: Angus | last post by:

Why moving a function from protected to public area in class causes error C2556 - overloaded function differs only by return type I have a protected function called GetState like this: enum EState GetState(void) const throw(); If I move this function from protected: to public: I get this compile error:

C / C++

8427

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8332

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8851

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8746

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8525

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

7356

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

5649

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4175

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

1975

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP