Using DCount to rank with additional criterion (1 Viewer)

J1D2A3

New member
Local time
Today, 05:16
Joined
Apr 27, 2020
Messages
2
Hello all,

I'm currently producing a database to store details about candidates going through a selection process. Once all of the data has been entered into the db, I would like to run a query that shows a rank for each candidate based on their overall score in both their syndicate and the overall process. I have used the following DCount to work out their overall position which works perfectly:

Overall Position: DCount("*","[Data Table]","[Tasks - Weighted Score] >" & [Tasks - Weighted Score])+1

However, I also want to display a candidate's position within their syndicate so have tried many variations of the following which all return #error when the query is run:

Syndicate Pos: DCount("*","[Data Table]","[Tasks - Weighted Score] >" & [Tasks - Weighted Score] & " AND [Syndicate Number]=" & [Syndicate Number])+1

I've seen various posts with similar questions but my implementation of the various solutions don't seem to work. Can anyone see my (probably obvious) mistake and provide any advice/assistance?

Any comments greatly appreciated and please let me know if you need more information.

Many thanks in advance,

James
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:16
Joined
Oct 29, 2018
Messages
21,358
Hi James. Welcome to AWF!

That code looks fine to me. We may have to play with your data to see what could be causing the error. Are you able to share a sample copy of your db?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:16
Joined
Feb 28, 2001
Messages
27,001
First comment: You do yourself no particular favors with having field names that contain special characters or excess spaces. Lots of typing and you will NEVER get rid of all those brackets. TskWgtdScore and SyndNumb would be equally mnemonic, for example, but you could even omit the brackets in most SQL cases.

Second comment: What you wrote in the DCOUNT depends on the datatype of the two fields. Since you have told us that the "overall position" domain aggregate works, I think it is save to say that the problem is in the syndicate number. What is the data type of the two fields? Is there a chance that the syndicate number is NOT numeric despite its name?
 

J1D2A3

New member
Local time
Today, 05:16
Joined
Apr 27, 2020
Messages
2
theDBguy- thank you, I was just about to upload a sample and then saw The_Doc_Man's response.

The_Doc_Man-you've solved it for me. All the data is stored in a SharePoint list and the Syndicate Number field was a Choice (1-6). I changed it to number and it of course works as it should. I also note the points about my poor choice of field names- it has been a long time since I last worked with Access and when I set the SP list up I didn't think through to the eventual typing that it would require. Point taken and chalked up to experience.

Thank you very much for your quick assistance.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:16
Joined
Oct 29, 2018
Messages
21,358
theDBguy- thank you, I was just about to upload a sample and then saw The_Doc_Man's response.

The_Doc_Man-you've solved it for me. All the data is stored in a SharePoint list and the Syndicate Number field was a Choice (1-6). I changed it to number and it of course works as it should. I also note the points about my poor choice of field names- it has been a long time since I last worked with Access and when I set the SP list up I didn't think through to the eventual typing that it would require. Point taken and chalked up to experience.

Thank you very much for your quick assistance.
Hi. Glad to hear you got sorted out. I'm not sure SharePoint has a Caption property, so you may be stuck with using spaces for column names. However, you could maybe replace them with underscores instead. Good luck with your project.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:16
Joined
Feb 28, 2001
Messages
27,001
Glad to have pointed you in the right direction. Sometimes it only takes a tiny push, which I'm happy to offer.
 

zeroaccess

Active member
Local time
Today, 00:16
Joined
Jan 30, 2020
Messages
671
First comment: You do yourself no particular favors with having field names that contain special characters or excess spaces. Lots of typing and you will NEVER get rid of all those brackets. TskWgtdScore and SyndNumb would be equally mnemonic, for example, but you could even omit the brackets in most SQL cases.
What if brackets help you spot field names in VBA and SQL admidst all the code and table, query, and control names? It works well as a visual identifier.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:16
Joined
Feb 28, 2001
Messages
27,001
There is nothing to stop you from using brackets anyway. The point is, with my way you have a choice. With spaces and special characters, you don't.
 

Users who are viewing this thread

Top Bottom