Solved

Generating ranking (ordinal number) field in a query

Posted on 2004-04-01
8
745 Views
Last Modified: 2008-03-17
I need to get a query to generate employee rankings in various categories so I can then add up those rankings to get a total score. I'm using Acess 2000 on Windows 2000. I've tried both solutions provided for the previous question asked like this, but I can't get either of them to work. When I try to use the long solution, I get an "undefined function" error and the VBA debugger highlights the first two lines in the module. The directions say to make sure DAO is ticked in References...there are several "Microsoft DAO *.* Object Library" listings...I've tried them all. When I try to use DAO 3.6, I get a name conflict error. Please help!
0
Comment
Question by:Fotofixer
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 14

Expert Comment

by:bluelizard
ID: 10738389
are the solutions you're mentioning part of an EE question?  if so, please post a link to that question.


--bluelizard
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10738545
For setting the references:

Open some VBA code and select Tools/References.
Each libraryname necessary must be checked and (for you: microsoft DAO version x.xx the used version doesn't matter as long it's selected from the list).

Nic;o)
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10741018
Yes I'd like to see the code and the previous question you reference.
Nico's right in his helping to check the references.
Some other thoughts:
Are the functions ones that should be inherent (like format() for instance or are you actually referencing a custom function.
Are you trying to call myfunction() for instance, and it can't find it?
Are your functions spelled right?
Say, if you meant format() and you put fomat(), VBA will think it needs a custom function called "fomat" and it will go looking for it.

Walt
0
 

Author Comment

by:Fotofixer
ID: 10741516
This is the question that I referenced the solutions for: I'm not sure how to set it up as a link in here.
Solution Title: Ordinal number in a query
Author: ConsultOR
Both solutions define a custom function. As far as I can tell everything is spelled correctly.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 17

Expert Comment

by:walterecook
ID: 10741739
To link, just copy and paste the URL into the message.
This looks like the one you referenced:
http://www.experts-exchange.com/Databases/MS_Access/Q_20589953.html

Maybe you could post your code so we can see ow you implemented it?

Walt
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 250 total points
ID: 10741775
The question link can be found in the address field from your browser is:
http://www.experts-exchange.com/Databases/MS_Access/Q_20589953.html

For ranking check also:
http://support.microsoft.com/support/kb/articles/Q208/9/46.ASP

Nic;o)
0
 

Author Comment

by:Fotofixer
ID: 10751738
That did the trick. But there is a problem with the way they wrote the expression on that site. This is the way they wrote it:

Seniority: (Select Count(*) from Employees Where [HireDate] < _
 [Emp1].[HireDate]+1;)

This is the way it should be:

Seniority: (Select Count(*) from Employees Where [HireDate] < [Emp1].[HireDate])+1

The close parenthesis goes in front of the +1

That page has a link to a file that you can download that gives many query examples.

Thanks again
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10751823
Hmm, I would have used:
Seniority: (Select Count(*) from Employees Where [HireDate] <= [Emp1].[HireDate])

Thus the equal is automatically counted as 1 and no need for the addition :-)

Success with the application !

Nic;o)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now