Creating a Function Problem

I am trying to create a function in MS Access (Module) that assigns a Grade 
for a particular Credit Bureau Score but the functions I created returns the 
same value for all scores.  Must be doing something wrong.  I'm new at this 
so any help would be appreciated.

Here are two Functions I created, both returning the same value:

1st Function
Option Compare Database
Function Grade2() As Integer
    If score >= 720 Then
        Grade2 = 1
    ElseIf score >= 680 And score < 720 Then
        Grade2 = 2
    ElseIf score >= 650 And score < 680 Then
        Grade2 = 3
    ElseIf score >= 630 And score < 650 Then
        Grade2 = 4
    ElseIf score >= 600 And score < 630 Then
        Grade2 = 5
    ElseIf score >= 0 And score < 600 Then
        Grade2 = 6
    End If
End Function


2nd Function
Option Compare Database
Function Grade1() As String
    Grade1 = IIf(score < 600, "E", IIf(score < 630, "D", IIf(score < 650, 
"C", IIf(score < 680, "B", "C"))))
End Function

-- 
EugeneH
0
Utf
4/25/2007 5:26:00 PM
access 16762 articles. 3 followers. Follow

4 Replies
559 Views

Similar Articles

[PageSpeed] 4

On Wed, 25 Apr 2007 10:26:00 -0700, Eugene7899 wrote:

> I am trying to create a function in MS Access (Module) that assigns a Grade 
> for a particular Credit Bureau Score but the functions I created returns the 
> same value for all scores.  Must be doing something wrong.  I'm new at this 
> so any help would be appreciated.
> 
> Here are two Functions I created, both returning the same value:
> 
> 1st Function
> Option Compare Database
> Function Grade2() As Integer
>     If score >= 720 Then
>         Grade2 = 1
>     ElseIf score >= 680 And score < 720 Then
>         Grade2 = 2
>     ElseIf score >= 650 And score < 680 Then
>         Grade2 = 3
>     ElseIf score >= 630 And score < 650 Then
>         Grade2 = 4
>     ElseIf score >= 600 And score < 630 Then
>         Grade2 = 5
>     ElseIf score >= 0 And score < 600 Then
>         Grade2 = 6
>     End If
> End Function
> 
> 2nd Function
> Option Compare Database
> Function Grade1() As String
>     Grade1 = IIf(score < 600, "E", IIf(score < 630, "D", IIf(score < 650, 
> "C", IIf(score < 680, "B", "C"))))
> End Function

How does the function (eitehr one) know what the score value is?
Nowhere in either function are you passing the [Score] value.

Function Grade2(MyScore as Integer) As Integer
  If MyScore >= 720 Then
  	 Grade2 = 1
   ElseIf MyScore >= 680 And MyScore < 720 Then
        Grade2 = 2        
   etc.....
End Function  

You would then pass the score to the function, iform a query:
NewScore:Grade2([ScoreFieldName])

Directly in a report or on a form:
=Grade2([ScoreFieldName])

Note: Look up the Select Case statement in VBA help. It often more
suitable than If then else.

Do the same for the other function as well.

  
  
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
4/25/2007 6:01:31 PM
Thanks for the response.

The value "Score" is a field in the query/form, so I am simply adding user 
defined field in the query/form to utlize the function that I created.

For example: in the query in which the "Score" value exists I added another 
user defined field as follows:  Tier:Grade2()

Unfortunately, even though each record shows varying scores for each line 
record the value "Tier" all equals "6".  Therefore I know it is calling the 
function to extract the value, but don't know why every single record is 
resulting in a "6".




-- 
EugeneH


"fredg" wrote:

> On Wed, 25 Apr 2007 10:26:00 -0700, Eugene7899 wrote:
> 
> > I am trying to create a function in MS Access (Module) that assigns a Grade 
> > for a particular Credit Bureau Score but the functions I created returns the 
> > same value for all scores.  Must be doing something wrong.  I'm new at this 
> > so any help would be appreciated.
> > 
> > Here are two Functions I created, both returning the same value:
> > 
> > 1st Function
> > Option Compare Database
> > Function Grade2() As Integer
> >     If score >= 720 Then
> >         Grade2 = 1
> >     ElseIf score >= 680 And score < 720 Then
> >         Grade2 = 2
> >     ElseIf score >= 650 And score < 680 Then
> >         Grade2 = 3
> >     ElseIf score >= 630 And score < 650 Then
> >         Grade2 = 4
> >     ElseIf score >= 600 And score < 630 Then
> >         Grade2 = 5
> >     ElseIf score >= 0 And score < 600 Then
> >         Grade2 = 6
> >     End If
> > End Function
> > 
> > 2nd Function
> > Option Compare Database
> > Function Grade1() As String
> >     Grade1 = IIf(score < 600, "E", IIf(score < 630, "D", IIf(score < 650, 
> > "C", IIf(score < 680, "B", "C"))))
> > End Function
> 
> How does the function (eitehr one) know what the score value is?
> Nowhere in either function are you passing the [Score] value.
> 
> Function Grade2(MyScore as Integer) As Integer
>   If MyScore >= 720 Then
>   	 Grade2 = 1
>    ElseIf MyScore >= 680 And MyScore < 720 Then
>         Grade2 = 2        
>    etc.....
> End Function  
> 
> You would then pass the score to the function, iform a query:
> NewScore:Grade2([ScoreFieldName])
> 
> Directly in a report or on a form:
> =Grade2([ScoreFieldName])
> 
> Note: Look up the Select Case statement in VBA help. It often more
> suitable than If then else.
> 
> Do the same for the other function as well.
> 
>   
>   
> -- 
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
> 
0
Utf
4/25/2007 7:02:07 PM
On Wed, 25 Apr 2007 12:02:07 -0700, Eugene7899 wrote:

> Thanks for the response.
> 
> The value "Score" is a field in the query/form, so I am simply adding user 
> defined field in the query/form to utlize the function that I created.
> 
> For example: in the query in which the "Score" value exists I added another 
> user defined field as follows:  Tier:Grade2()
> 
> Unfortunately, even though each record shows varying scores for each line 
> record the value "Tier" all equals "6".  Therefore I know it is calling the 
> function to extract the value, but don't know why every single record is 
> resulting in a "6".

Did you even attempt what I suggested?

Regarding  Tier:Grade2()
You haven't passed the score value to the function, and the function
has no way of getting the score unless you name an argument to receive
it.

In the query:
Tier:Grade2([Score])

Make the function a Public Function by placing it in a Module.
Then, in the Function, write:

Function Grade2(MyScore as Integer) as Integer

Then, in the function body, use MyScore instead of Score.
  If MyScore = etc.....
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
4/25/2007 9:01:21 PM
Works like a charm!
Thanks a bunch
-- 
EugeneH


"fredg" wrote:

> On Wed, 25 Apr 2007 12:02:07 -0700, Eugene7899 wrote:
> 
> > Thanks for the response.
> > 
> > The value "Score" is a field in the query/form, so I am simply adding user 
> > defined field in the query/form to utlize the function that I created.
> > 
> > For example: in the query in which the "Score" value exists I added another 
> > user defined field as follows:  Tier:Grade2()
> > 
> > Unfortunately, even though each record shows varying scores for each line 
> > record the value "Tier" all equals "6".  Therefore I know it is calling the 
> > function to extract the value, but don't know why every single record is 
> > resulting in a "6".
> 
> Did you even attempt what I suggested?
> 
> Regarding  Tier:Grade2()
> You haven't passed the score value to the function, and the function
> has no way of getting the score unless you name an argument to receive
> it.
> 
> In the query:
> Tier:Grade2([Score])
> 
> Make the function a Public Function by placing it in a Module.
> Then, in the Function, write:
> 
> Function Grade2(MyScore as Integer) as Integer
> 
> Then, in the function body, use MyScore instead of Score.
>   If MyScore = etc.....
> -- 
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
> 
0
Utf
4/25/2007 9:58:01 PM
Reply:

Similar Artilces:

Nested If Function to be converted into a user-defined custom function
Dear Experts: below formula (nested if formula) WORKS FINE in EXCEL 2007, but it is not working in 2003 for known reasons (number of conditions exceeding the limit) . I now would like to create a user-defined custom function (UDF) in Excel 2003. I know how to operate the VBA Editor but I got no idea how this nested If-Formula translates into a VBA-code. Could somebody please help me. Help is much appreciated. Thank you very much in advance. Regards, Andreas ------------------------------------------------------------------------------ Below formula (nested IF-Function...

Same problem here with Ports not listening from RPCDump ....
I have the same problem David has. Single server setup, https return 402.3 read access denied, RPCDump returns No for IsListening on por 6001, 6002, and 6004. Just wondering if anyone has a solution for th problem. I went through the single server setup article troubleshooting article as well as the deployment guide. But n luck.. http://support.microsoft.com/default.aspx?scid=kb;en-us;833401# http://support.microsoft.com/default.aspx?scid=kb;en-us;82733 If anyone could help, i would greatly appreciate any help ...

Need help in accessing a sharepoint site that I created with the AllTemplates.exe
SBS2008 with WSS 3.0 I downloaded the AllTemplates.exe from Microsoft and installed the Helpdesk template. I can acess the Helpdesk site from SBS2008 and add things to it but I can't access it from a workstation. Can anyone help me out? Thanks You mean you can only access the site from the Server in local , right? maybe we need more information such as error message. how about doing "ping" or "tracert" result. can you access a normal web site (not sharepoint site) from the workstation ? do you get an authentication window ? "john doe" wr...

using if function..please help
i am trying to use the if function in the following manner. A B apple red apple red grapes purple grapes yellow star n/a if(B1:B5="n/a",0,countif(A1:A5, "star") I want to use this formula not only for 'star' but also for 'apple' and grapes' and many more such fruits which might have n/a later. But, everytime I use this formula it returns a 1..should it not return a 0 for when the value in B is n/a? Please help.. Thank you Are you trying to say that if any c...

Disabling F1 key's Windows Help Function
We'll I guess the title says it all :) Just curious if there is an easy way to disable F1 from always trying to bring up Window's help whenever it is hit within the program. I find it more than a little annoying because even in my program where (I thought) I was capturing all keystrokes for a keyDown event (I added UINT MyClass:OnGetDlgCode() { return DLGC_WANTARROWS|DLGC_WANTALLKEYS|DLGC_WANTCHARS; } Hmmm, so can I add something else to this line perhaps (going to check on this now) in order to catch ALL keystrokes (or at least add something so windows help function will be di...

Exchange 2003/ 12 Architecture and Functionality Questions
1. Can we configure intelligent message filter in Exchange 2003/ Ex12 to also act on outgoing mails? Our customer would also like to make sure no unsolicited mails originate from their organization. 2. Does the facility to configure the users’ voice messages (which automatically answer other people’s calls) fall under the PABX, or will it have to be done in Exchange 12? 3. Is it possible for two organizations to make use two Exchange 12 Edge servers, while having Exchange Mail Box servers so they can take advantage of the native encryption and signing of mails to and from the two...

Outlook delegate-mailbox deletion problem
This sounds like an easy one however, haven't found resolution yet. Here's scenario... User B reported to User A and added A to his delegation for being copied on all meeting request replies (done from Outlook 2000). All was well until User A left the company and mailbox was subsequently archived and deleted. Ever since the mailbox deletion, all attendees accepting a meeting request from User B receive an NDR referencing inability to deliver reply to User A's mailbox. The typical answer would be to open User B's Outlook app, goto delegates and remove User A.. The problem is...

Can Function results be calculated?
Hi all I have a sheet with two columns of figures. I show the running totals of each column in cells with basic SUM Functions, at the top of the sheet. I now want to subtract one total from the other. How is this done? As the Function is looking at cells that themselves hold Functions, I can't see how to do this, short of making a longer Function that adds the column contents again and then subtracts. So, in summary: I have two cells with Functions. These are: Cell F4 =SUM(F8:F45) and Cell G4 =SUM(G8:G45) I want to do this: =F4-G4 but it won't play. Any advice most appreciated....

A Real Sticky Problem
I created a query that joins two tables into one form. One table is the active one and the other is archived information. So, for instance a clients most recent data for 2007 is in table one, but the older data is stored in table two. The problem is that the form is not displaying the newest clients entered into table one. It is 8 off. Is this because there is no similar entry in table two? Can this be resolved by assigning a primary key to table one? If you're just doing a simple join (an Inner Join), you'll only get details for those clients that exist in both tables. You...

Problems with re-setting the last active cell in an Excel workshee
I am trying to re-set the last active cell on an Excel 2002 worksheet (in this particular sheet it should be cell DA197). I have used both the methods described in the Knowledge Base article (deleting rows and columns and re-saving; and the Excess Format Cleaner add-in). Deleting the rows and columns does not work; using the Excess Format Cleaner does not work either and it then also hides the rows from 198 to 65536 - but does not do the same for the columns. I have checked that there is no protection on the worksheet. Has anyone else come across this problem and if so can you please ...

Creating a Campaign Response via Follow-up
When you create a new Task/Phone Call or other activity via the Follow-up tab, the new Activity is associated with the entity you are looking at. (so following up a Contact with a Phone Call will create a Phone Call which is associated with the Contact) This is not the case for the Campaign Response activity - when you create a Campaign Response via the Follow-up tab, the Customer field (ie regarding) is left blank and hence not associated with anyone. Presumably this is a bug - anyone know if there is a hotfix for this? Kind Regards, Paul. ...

Install of SP3 problems
Have a Sony VIAO desktop with Windows XP Home SP1 loaded. Intel CPU and 512mb RAM. I tried loading SP3 from the exe file that I downloaded from the Microsoft web site. It complained that it couldn't backup a msscp.dll file from \Windows\system32 folder, but it let me ignor/skip it and go ahead with the install anyway. After the first boot the system hung on the black screen with the cursor for a long time (4 min). This was a fresh instal. When it finished booting (finally!) it was slower than snot. And most of the text in program windows and control panel was missing?? Even the t...

Problems launching process
In the following code I am attempting to launch Internet Explorer and display an html document in the browser. Internet Explorer runs fine but the html document cannot be displayed. No matter if I pass the full path or just the filename to the function the document cannot be displayed. // Begin code --------------- // Html file is viewed by launching Internet Explorer via RunBrowser() // CString sHtmlFile = _T("test.html"); RunBrowser("C:\\Program Files\\Internet Explorer\\Iexplore.exe", "test.html"); // Function that launches process void CMyView::RunBrowser...

OR Function #4
I am trying to make a formula using the if function. However, within this formula I need to nest an OR function. I can not get it to work. Any suggestions? =IF(OR(A1=condition1,A1=condition2),output_for_true,output_for_false) -- Regards, Dave "frustratedwthis" wrote: > I am trying to make a formula using the if function. However, within this > formula I need to nest an OR function. I can not get it to work. Any > suggestions? ...

Replace Cells with Column names in functions?
I have a # of fairly long/complex cell functions that get hard to debug because there are also a lot of rows. Is there anyway to change display so it replaces the column name e.g. If(BT1204="X". BA="Y" to If(CustomerName="X", CustomerCode="Y") ? CustomerName is a defined name range for BT1204 Find & Replace Find what: BT1204 Replace with: CustomerName "msnyc07" wrote: > I have a # of fairly long/complex cell functions that get hard to debug > because there are also a lot of rows. > > Is t...

Creating a summary sheet
I have a spreadsheet that is about 200 rows down and 60 columns accross. I want to create a second summary tab that will pull over only certain columns from the "detail" sheet. As I update the detail sheet I want to also update the summary sheet - so far no problem, but if I add a new row in the detai sheet - how can I also add the same row in the summary sheet and only bring over columns I want on the summary sheet (the columns are not next to each other) Paste link does not insert the row in the summary sheet unless I drag the formulas - which I do not want to do. Is th...

problems downloading UpdateOutlook2003EmailFilter(KB914454)
Wanna share a little more detail? Any particular error? Nikki "Ian" <Ian@discussions.microsoft.com> wrote in message news:5CE49572-FB48-4FCE-8A2F-37A8DABB47E9@microsoft.com... > I couldn't get the update to install on any of my computers. I uninstalled Office, reinstalled it, updated it; no problem. Ended up doing that on all 3 machines. Seems like an MS update problem. While I am on the subject. This last 6 updates that came through last week would not download automatically. I had to get them via microsoft update. Maybe I didn't give them enough...

DTD Entity replacement problems
Hi, I'm trying to use a Entity replacement with a DTD file, and I'm getting this error while processing with the "XmlValidatingReader" component: "The parameter entity replacement text must nest properly within markup declarations. An error occurred at http://localhost/test/dtds/test.dtd, (3, 24)." I've revised the DTD and I think its correct. This is a little example that repro the bug: <!ENTITY % par1 ",test2"> <!ELEMENT test (test1 %par1;)> <!ELEMENT test1 (#PCDATA)> <!ELEMENT test2 (#PCDATA)> And the XML ...

Problem With Opening Attachments
For some reason, when I click on any attachment in received e-mails Microsoft Publisher opens and the attachments cannot be viewed. Does anybody know how to prevent this? It started just a couple of days ago and I haven't been able to find a solution anywhere. Thanks in advance. ...

XL2007
I have a spreadsheet that uses the following sequence to scale the y axis of an X-Y scatter graph with lines drawn between points from VBA and it works perfectly in all previous versions of Excel. It fails with XL2007 rather catastrophically. The main spreadsheet was imported from XL2003. I have looked in the KB and archives here and not found any other reports of this problem so I don't know if it is something peculiar about this particular installation or what. It is reproducible in a toy chart in pure XL2007. The failing lines are; ActiveChart.Axes(xlSecondary).Select ok = Applicati...

Default Value problem 02-22-08
Feel a bit silly. I want to insert a =Now() Date field on every new record created via a certain form. I don't want people seeing this info. So, I added AphaDate Date field to the table the query for this form is based on. I insert the field on the form and mark it hidden. In the properties for this field on the form, I say the default value =Now() I assume then that any new record created will "stamp" with this date. But it doesn't work When I create the table, add the field to the query and try to set this up, the field shows up as blank no matter what I do. Wha...

Problem with Text box and Background
I am using the background as a temple for a form I add test boxes where I want to have text typed in The problem is when I print the page the text I have typed in isn't where it is supposed to be It is below the line of the background form How do I solve this problem? Web page? Best asked in the publisher.webdesign group -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "News" <gmhull@sbcglobal.net> wrote in message news:pOKFf.27487$F_3.19174@newssvr29.news.prodigy.net... >I am using the background as a t...

Problem when adding an appointment in CRM from the SFO ver. 1.2
When the users make an appointment in the SFO, its fails. The SFO makes the activity on the contact, but the Outlook synchronisation fails. The appointment does not appear in the Outlook calendar. Then we reinstall the appointment form in Outlook, clear the forms cache, and the problem is resolved. Then after some time (weeks) the problem comes back. Any ideas? Best regards Jacob Mondrup IT Manager, Cultivator Denmark. have you loaded the latest rollup patch for the outlook client? http://www.microsoft.com/downloads/details.aspx?FamilyID=127a274d-bc20-4e2f-be0d-1247455748...

Doing a VLOOKUP (probably using the INDEX and MATCH function), with both vertical and horizontal values in play.
I am trying to create a function that will pull in data from a 2nd spreadsheet. Typically, I use the index and match function to do so. However, in this case, I am trying to do a lookup based on a value above (i.e., horizontal) and a value to the right (i.e., vertical) of the cell in which the formula will be placed. Additionally, the sheet from which I am pulling is similarly laid out. To Provide an example. Lookup Table Months (horizontal) Jan Feb Mar Apr Etc Names(vertical) Jeff Eric 5 Steve ...

Access2000 automation problem
Hi I have a VBA script, which worked finely for about half a year. The script opens an excel workbook on server share on every night, refreshes some ODBC queries (from Visual FoxPro tables, one query on every sheet, no formulas in adjacent cells, all query properties are same), waits a little, and then closes the file. The script is here: Option Explicit Dim objXLApp, objXLBook, strPath, strBook strPath = "O:\Common\PerData\" strBook = "Personal.xls" Set objXLBook=GetObject(strPath & strBook) Set objXLApp = objXLBook.Parent objXLApp.Visible = True objXL...