help with the Easiest way to finding where in the list (1 Viewer)

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:48
Joined
May 7, 2009
Messages
19,175
From the Query you posted in post#14, create a New Query out of it returning all the columns.
for the New Rank Column, put this Expression:
Code:
Rank: fncRank("YourFirstQueryName", "StaffID", [StaffID])

Copy and paste the function in a Module.


Code:
Public Function fncRank(QueryName As String, FieldName As String, FieldValue As Variant) As Long
    Dim Criteria As String
    Select Case TypeName(FieldValue)
        Case "Integer", "Long", "Double", "Single", "Boolean"
            Criteria = FieldName & "=" & FieldValue
        Case "Date"
            Criteria = FieldName & "=#" & Format(FieldValue, "mm/dd/yyyy") & "#"
        Case "String"
            Criteria = FieldName & "=""" & FieldValue & """"
        Case Else
            Exit Function
    End Select
    With CurrentDb.OpenRecordset(QueryName, dbOpenSnapshot)
        If Not (.BOF And .EOF) Then
            .FindFirst Criteria
            If Not .NoMatch Then
                While Not .BOF
                    fncRank = fncRank + 1
                    .MovePrevious
                Wend
            End If
        End If
    End With
End Function
 

Gint32

Registered User.
Local time
Today, 06:48
Joined
Jan 21, 2018
Messages
39
From the Query you posted in post#14, create a New Query out of it returning all the columns.
for the New Rank Column, put this Expression:
Code:
Rank: fncRank("YourFirstQueryName", "StaffID", [StaffID])
Copy and paste the function in a Module.


Code:
Public Function fncRank(QueryName As String, FieldName As String, FieldValue As Variant) As Long
    Dim Criteria As String
    Select Case TypeName(FieldValue)
        Case "Integer", "Long", "Double", "Single", "Boolean"
            Criteria = FieldName & "=" & FieldValue
        Case "Date"
            Criteria = FieldName & "=#" & Format(FieldValue, "mm/dd/yyyy") & "#"
        Case "String"
            Criteria = FieldName & "=""" & FieldValue & """"
        Case Else
            Exit Function
    End Select
    With CurrentDb.OpenRecordset(QueryName, dbOpenSnapshot)
        If Not (.BOF And .EOF) Then
            .FindFirst Criteria
            If Not .NoMatch Then
                While Not .BOF
                    fncRank = fncRank + 1
                    .MovePrevious
                Wend
            End If
        End If
    End With
End Function

Thanks for that, I did as you asked and get the following error message when the code gets to this line:
Code:
With CurrentDb.OpenRecordset(QueryName, dbOpenSnapshot)

Run time error 3061: To few parameters Expected 2.

Also, FYI
Code:
        Case "Integer", "Long", "Double", "Single", "Boolean"
            Criteria = FieldName & "=" & FieldValue

does return the values = FieldName & FieldValue

any ideas .thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:48
Joined
May 7, 2009
Messages
19,175
You have Form reference in your Original query. You must open the form for my func and ridders to work.
 

Gint32

Registered User.
Local time
Today, 06:48
Joined
Jan 21, 2018
Messages
39
You have Form reference in your Original query. You must open the form for my func and ridders to work.

thanks, yes I was aware of that, so always had it open in every attempt.
 

Gint32

Registered User.
Local time
Today, 06:48
Joined
Jan 21, 2018
Messages
39
Strange thing is ...If I create a brand new query which only returns all the staffids without any filters Rank column will return the position/value of where the records are on the filtered list. so if it returns say 4 records then it'll have in each record/row the corresponding number.

Rank: fncRank("qryBrandnewQuery", "StaffID", [StaffID])
 

isladogs

MVP / VIP
Local time
Today, 13:48
Joined
Jan 14, 2017
Messages
18,186
Do you get the two parameter errors with the original query when the filters are included?
Are you still getting the errors using the approach I described in my last post using Serialize or have you scrapped that to use arnel's almost identical function?
 

Gint32

Registered User.
Local time
Today, 06:48
Joined
Jan 21, 2018
Messages
39
Do you get the two parameter errors with the original query when the filters are included?
Are you still getting the errors using the approach I described in my last post using Serialize or have you scrapped that to use arnel's almost identical function?
I am still getting the same messages with
Code:
AssRank: Serialize("qry_Ot_Calls","staffID",[staffID])
and same with Arnelgp Function.
 

isladogs

MVP / VIP
Local time
Today, 13:48
Joined
Jan 14, 2017
Messages
18,186
I am still getting the same messages with
Code:
AssRank: Serialize("qry_Ot_Calls","staffID",[staffID])
and same with Arnelgp Function.

Please read my instructions again from post #20 as that's not what you should be using

However, one thing I've just noticed from your code in post 14 - if you still have the sort order code in your query you need to remove it as it will conflict with the other functions - whichever one you are using. Suggest you strip down the query to the fewest fields necessary for this purpose
 
Last edited:

Gint32

Registered User.
Local time
Today, 06:48
Joined
Jan 21, 2018
Messages
39
Please read my instructions again from post #20 as that's not what you should be using

However, one thing I've just noticed from your code in post 14 - if you still have the sort order code in your query you need to remove it as it will conflict with the other functions - whichever one you are using. Suggest you strip down the query to the fewest fields necessary for this purpose

Apologies for the delay in getting back to you, thanks for your suggestions, I have stripped down the new query to the minimum required, although, I am still getting an error for each record the new query attempts to return. Also, unfortunately if I remove the sorts then it'll defeat the purpose of attempting to find out where staff are on the Over Time call list. This form sorts staff by the shortest hours accrued hours.
Error message says Error 3061 in Serialize procedure : Too few Parameters. Expected 1.

Code:
  SELECT Serialize("qry_Ot_Calls__","StaffsId",[StaffsId]) AS AssRank, WorkLocations.Priority, Tbl_Staffs_Types.Staffs_OrderID, Tbl_Staffs.OT_Hours, AttendTypes.Attend, Tbl_Staffs.StaffsId, Attend.AttDate, Tbl_Staffs_Types.Staffs_Type_ID
  FROM Tbl_ReasonTypes RIGHT JOIN ((WorkLocations INNER JOIN (Tbl_Staffs_TypesINNER JOIN Tbl_Staffs ON Tbl_Staffs_Types.Staffs_Type_ID = Tbl_Staffs.Staffs_Type_ID) ON WorkLocations.WorkLocation = Tbl_Staffs.WorkLocation) INNER JOIN (Attend INNER JOIN AttendTypes ON Attend.AttType = AttendTypes.AttType) ON Tbl_Staffs.StaffsId = Attend.AttStaffs) ON Tbl_ReasonTypes.Reason_ID = Attend.Reason_ID
  WHERE (((AttendTypes.Attend)<>"Nothing Booked") AND ((Attend.AttDate)=[Forms]![OT_Board]![txt_OTDate]) AND ((Tbl_Staffs_Types.Staffs_Type_ID)<>4))
  ORDER BY WorkLocations.Priority, Tbl_Staffs_Types.Staffs_OrderID, Tbl_Staffs.OT_Hours, AttendTypes.Attend, Tbl_Staffs.StaffsId;
 

isladogs

MVP / VIP
Local time
Today, 13:48
Joined
Jan 14, 2017
Messages
18,186
Only 1 parameter now - I suppose that's progress

Can I ask whether you actually looked at the example queries I included in the sample database?

Regarding the query fields, I only wanted you to remove the SortOrder part of the SQL which you seem to have done

BUT there are several issues with this SQL (some of which I've mentioned before)
1. Previously you referred to tblStaff, StaffID etc
Now all references are to tblStaffs, StaffsID etc - with an added 's'
Is that correct?

2. On the second line there is a missing space between Types & INNER in this section
Code:
Tbl_Staffs_[B][COLOR="Red"]TypesINNER[/COLOR] [/B]JOIN

3. The Serialize code has also altered
Code:
Serialize("qry_Ot_Calls[COLOR="red"]__[/COLOR]","Staff[COLOR="red"]s[/COLOR]Id",[Staff[COLOR="red"]s[/COLOR]Id]) AS AssRank
Quite apart from the StaffsID part, the query name has also changed and now has 2 underscores at the end.

However I understood that the original query (without Serialize) was called 'qry_Ot_Calls'
What is the name of the new query with the Serialize function?
If for example it was called qryStaffAssRank then this part should be

Code:
Serialize(qryStaffAssRank","StaffId",[StaffId]) AS AssRank

Substitute your new query name instead.

4. If your SQL is being run as VBA code from a procedure then change "Nothing Booked" to 'Nothing Booked' (single quotes)

Please make those changes and report back.

If you still get a parameter error then I think it will be necessary for you to post a stripped down copy of your database containing,
If you need to do so, change any names & personal details first
 

Gint32

Registered User.
Local time
Today, 06:48
Joined
Jan 21, 2018
Messages
39
Only 1 parameter now - I suppose that's progress

Can I ask whether you actually looked at the example queries I included in the sample database?

Regarding the query fields, I only wanted you to remove the SortOrder part of the SQL which you seem to have done

BUT there are several issues with this SQL (some of which I've mentioned before)
1. Previously you referred to tblStaff, StaffID etc
Now all references are to tblStaffs, StaffsID etc - with an added 's'
Is that correct?

2. On the second line there is a missing space between Types & INNER in this section
Code:
Tbl_Staffs_[B][COLOR=Red]TypesINNER[/COLOR] [/B]JOIN
3. The Serialize code has also altered
Code:
Serialize("qry_Ot_Calls[COLOR=red]__[/COLOR]","Staff[COLOR=red]s[/COLOR]Id",[Staff[COLOR=red]s[/COLOR]Id]) AS AssRank
Quite apart from the StaffsID part, the query name has also changed and now has 2 underscores at the end.

However I understood that the original query (without Serialize) was called 'qry_Ot_Calls'
What is the name of the new query with the Serialize function?
If for example it was called qryStaffAssRank then this part should be

Code:
Serialize(qryStaffAssRank","StaffId",[StaffId]) AS AssRank
Substitute your new query name instead.

4. If your SQL is being run as VBA code from a procedure then change "Nothing Booked" to 'Nothing Booked' (single quotes)

Please make those changes and report back.

If you still get a parameter error then I think it will be necessary for you to post a stripped down copy of your database containing,
If you need to do so, change any names & personal details first

thanks Yes, I did download your example queries

Apologies, but the reason for the SQL differences was to keep the names obscure from public view!

Also, yes the name of my query is a copy of the original like you stated to do. with as many field as I could stripped out, this is why I have 2X underscores. This is the query I am using with the serialize Function.

AssRank: Serialize("qry_Ot_Calls_","officerID",[officerID])
Also, my query is not being run from SQL code
So sorry, but I need to get some sleep now ..3:30 AM, will check in again later thanks for reading
 

isladogs

MVP / VIP
Local time
Today, 13:48
Joined
Jan 14, 2017
Messages
18,186
Seems like you are 7 hours ahead of the UK so there will be some inevitable time delays when you do log back in

Anyway, now I'm totally confused. OfficerID? One underscore at end this time?

Please upload the relevant parts of your database with personal details changed for obvious reasons.
Without seeing this 'in the flesh' I'm unable to suggest a solution for your situation
 
Last edited:

Gint32

Registered User.
Local time
Today, 06:48
Joined
Jan 21, 2018
Messages
39
Seems like you are 7 hours ahead of the UK so there will be some inevitable time delays when you do log back in

Please upload the relevant parts of your database with personal details changed for obvious reasons.
Without seeing this 'in the flesh' I'm unable to suggest a solution for your situation

Thanks and yep, 7hrs(Aus), Heading to the UK - beginning of May though for a month. I have attached the stripped down query, hope this helps
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 13:48
Joined
Jan 14, 2017
Messages
18,186
Hi
Can you please add the form as that's referenced for two query fields.
I'll then have a look properly later today
 

Gint32

Registered User.
Local time
Today, 06:48
Joined
Jan 21, 2018
Messages
39
Hi
Can you please add the form as that's referenced for two query fields.
I'll then have a look properly later today


Hey and thanks the from is now added. :)
 

Attachments

  • 4RUpload.accdb
    1.1 MB · Views: 108

isladogs

MVP / VIP
Local time
Today, 13:48
Joined
Jan 14, 2017
Messages
18,186
Phew! Fixed it.
The problems weren't anything to do with the Serialize function itself

1. Your query used for the form is qry_OT_Calls. It had the following issues
a) AdjustedHours included a reference to a form field ShiftHrs that doesn't exist - it should be ShiftHours
b) The query SQL requires two values from the form itself but the form isn't open until the query is read so you have an impossible loop
- I replaced the criteria for AttDate with Date() as that's what it is anyway!
- I changed the AdjustedHours field to OT_Hours+ShiftHours

As a result qry_OT_Calls no longer requires the form to be open (& the form still works)

Next issue: In the badly named 'qry_OT_Calls__' used for the Serialize function you used the original query qry_OT_Calls rather than the new query name. I fixed it & it worked with no parameter errors

I also created a better named version qryAssRank to do the same job
I believe this now give correct results with your test data
Please test it on your real data & report back

You no longer need the form to be open

NOTE: various old items saved as ... _old
 

Attachments

  • 4RUpload_CR.zip
    252.9 KB · Views: 110

Gint32

Registered User.
Local time
Today, 06:48
Joined
Jan 21, 2018
Messages
39
Phew! Fixed it.
The problems weren't anything to do with the Serialize function itself

1. Your query used for the form is qry_OT_Calls. It had the following issues
a) AdjustedHours included a reference to a form field ShiftHrs that doesn't exist - it should be ShiftHours
b) The query SQL requires two values from the form itself but the form isn't open until the query is read so you have an impossible loop
- I replaced the criteria for AttDate with Date() as that's what it is anyway!
- I changed the AdjustedHours field to OT_Hours+ShiftHours

As a result qry_OT_Calls no longer requires the form to be open (& the form still works)

Next issue: In the badly named 'qry_OT_Calls__' used for the Serialize function you used the original query qry_OT_Calls rather than the new query name. I fixed it & it worked with no parameter errors

I also created a better named version qryAssRank to do the same job
I believe this now give correct results with your test data
Please test it on your real data & report back

You no longer need the form to be open

NOTE: various old items saved as ... _old

Well many thanks for your valued time end effort, I am not sure about the changes you made as I downloaded your amended and couldn't find any changes within anything!
So all I could do was sort my hrs to hours
Any chance you could reload upload the DB again?

re -
As a result qry_OT_Calls no longer requires the form to be open (& the form still works)
Not sure as your the expert, but I think I'll still need the form to be loaded from my query as my form needs to get a date passed from a popup - Calendar that pre-loads and passes the date to the field (txt_OTDate) on the form. via the sql AttDate = Me.txt_OTDate

Thanks
 

isladogs

MVP / VIP
Local time
Today, 13:48
Joined
Jan 14, 2017
Messages
18,186
Well many thanks for your valued time end effort, I am not sure about the changes you made as I downloaded your amended and couldn't find any changes within anything!
So all I could do was sort my hrs to hours
Any chance you could reload upload the DB again?

re - Not sure as your the expert, but I think I'll still need the form to be loaded from my query as my form needs to get a date passed from a popup - Calendar that pre-loads and passes the date to the field (txt_OTDate) on the form. via the sql AttDate = Me.txt_OTDate

Thanks

Sorry about that. I uploaded the wrong version

New form OT_Board_New with a record source using new query qryAssRank (with Serialize function).
I've left all _old items but once you're happy with this those can be deleted

Have modified test data dates so these included today's date
I've added the AssRank field to the left of the form and made the Shift Hours visible so I could easily enter data



As far as I understand it the form is taking data for today's date only so I've based my solution on that. If not, it will need further tweaking later.

FIXED version attached
 

Attachments

  • Capture.png
    Capture.png
    21.5 KB · Views: 264
  • 4RUpload_CR - FIXED.zip
    253.6 KB · Views: 102

Gint32

Registered User.
Local time
Today, 06:48
Joined
Jan 21, 2018
Messages
39
Sorry about that. I uploaded the wrong version

New form OT_Board_New with a record source using new query qryAssRank (with Serialize function).
I've left all _old items but once you're happy with this those can be deleted

Have modified test data dates so these included today's date
I've added the AssRank field to the left of the form and made the Shift Hours visible so I could easily enter data

As far as I understand it the form is taking data for today's date only so I've based my solution on that. If not, it will need further tweaking later.:banghead:Also I like the feature of turning the label text to vertical never thought of that!

FIXED version attached

Wish I had the time to devote to the IT side of this, So sorry but I have been working shifts for a few nights so hadn't had the time to devote as much as I would have liked, but hey thanks, I have just now imported all the amended forms and all works as you stated..you are a champ!!! ,So once again many thanks for your patience and effort!! Hats off to you for getting the rank in list running. As it was apparent that I couldn't do it...now I'll have HR play with this till I get back from the UK in 7weeks for there verdict on run or tweak more ...Fingers Xsd they are happy with my and your efforts!
 

isladogs

MVP / VIP
Local time
Today, 13:48
Joined
Jan 14, 2017
Messages
18,186
You're welcome.
If you study my solution, hopefully you will be able to make use of the idea in the future for other purposes.

Good luck
 

Users who are viewing this thread

Top Bottom