Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-25-2018, 11:36 AM   #31
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by ridders View Post
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_TypesINNER JOIN
3. The Serialize code has also altered
Code:
Serialize("qry_Ot_Calls__","StaffsId",[StaffsId]) 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.

Quote:
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

Gint32 is offline   Reply With Quote
Old 04-25-2018, 11:41 AM   #32
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,556
Thanks: 88
Thanked 1,610 Times in 1,500 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: help with the Easiest way to finding where in the list

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 04-25-2018 at 01:00 PM.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Gint32 (04-25-2018)
Old 04-25-2018, 05:46 PM   #33
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by ridders View Post
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 by Gint32; 04-26-2018 at 03:23 AM.
Gint32 is offline   Reply With Quote
Old 04-25-2018, 11:08 PM   #34
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,556
Thanks: 88
Thanked 1,610 Times in 1,500 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: help with the Easiest way to finding where in the list

Hi
Can you please add the form as that's referenced for two query fields.
I'll then have a look properly later today
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 04-26-2018, 03:22 AM   #35
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by ridders View Post
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.
Attached Files
File Type: accdb 4RUpload.accdb (1.10 MB, 23 views)
Gint32 is offline   Reply With Quote
Old 04-26-2018, 07:58 AM   #36
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,556
Thanks: 88
Thanked 1,610 Times in 1,500 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: help with the Easiest way to finding where in the list

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
Attached Files
File Type: zip 4RUpload_CR.zip (252.9 KB, 18 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 04-28-2018, 12:42 AM   #37
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by ridders View Post
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 -
Quote:
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

Gint32 is offline   Reply With Quote
Old 04-28-2018, 03:10 AM   #38
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,556
Thanks: 88
Thanked 1,610 Times in 1,500 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by Gint32 View Post
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
Attached Images
File Type: png Capture.png (21.5 KB, 104 views)
Attached Files
File Type: zip 4RUpload_CR - FIXED.zip (253.6 KB, 10 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 04-29-2018, 04:08 PM   #39
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Cool Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by ridders View Post
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.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!
Gint32 is offline   Reply With Quote
Old 04-29-2018, 11:32 PM   #40
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,556
Thanks: 88
Thanked 1,610 Times in 1,500 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: help with the Easiest way to finding where in the list

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Gint32 (05-01-2018)
Old 05-08-2018, 03:31 PM   #41
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by ridders View Post
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
Hi Ridders,
Just a quick observation/question, the form and qry you supplied works well if you wish to only use the current date(today), but I wish it to not just take the records for the current date only, I originally had it open up to a user date selection via a datepicking form so as when the user clicked the textbox = txt_OTDate on the form ,they could then jump to those records for the date selected.
I noticed that you commented this out, but this was and needs to be big part of the functionality. So I attempted to alter your query/criteria by changing one field from = date() to > = Forms![ot_board]![txt_OTDate], and thought it would be a simple as that, but not as I am now getting the same errors once again now
Code:
InputDateField txt_OTDate, "Select A Date to view Availability for O/T"
Gint32 is offline   Reply With Quote
Old 05-08-2018, 03:56 PM   #42
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,556
Thanks: 88
Thanked 1,610 Times in 1,500 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by Gint32 View Post
Hi Ridders,
Just a quick observation/question, the form and qry you supplied works well if you wish to only use the current date(today), but I wish it to not just take the records for the current date only, I originally had it open up to a user date selection via a datepicking form so as when the user clicked the textbox = txt_OTDate on the form ,they could then jump to those records for the date selected.
I noticed that you commented this out, but this was and needs to be big part of the functionality. So I attempted to alter your query/criteria by changing one field from = date() to > = Forms![ot_board]![txt_OTDate], and thought it would be a simple as that, but not as I am now getting the same errors once again now
Code:
InputDateField txt_OTDate, "Select A Date to view Availability for O/T"
I'd forgotten all about this as its 2 weeks on.

When I posted my solution I stated:
Quote:
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.
The InputDateField code was disabled by you before you uploaded it
I recognise that code as its used in my 'Better Date Picker' which I posted here https://www.access-programmers.co.uk...d.php?t=298062
I've just re-enabled the code but the required module & form needed for it to work had been omitted by you

So I've restored the frmDatePicker & modDatePicker items & it works again
I've also added a Change Date button to the form so you can do that easily

It runs without error
Attached Files
File Type: zip 4RUpload_CR_UPDATED_20180509.zip (282.7 KB, 5 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 05-09-2018, 12:01 AM   #43
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by ridders View Post
I'd forgotten all about this as its 2 weeks on.

When I posted my solution I stated:


The InputDateField code was disabled by you before you uploaded it
I recognise that code as its used in my 'Better Date Picker' which I posted here https://www.access-programmers.co.uk...d.php?t=298062
I've just re-enabled the code but the required module & form needed for it to work had been omitted by you

So I've restored the frmDatePicker & modDatePicker items & it works again
I've also added a Change Date button to the form so you can do that easily

It runs without error
Thanks, I downloaded and yes the date does change but the records that are returned are for the same date, i can't see where in the VBA you are passing the new selected date to the exist query, would you mind clarifying how this is achieved.
As I said before I attempted to do this myself with using
Code:
= Forms![ot_board]![txt_OTDate]
within the query AssRank but as I say it fails.
Gint32 is offline   Reply With Quote
Old 05-09-2018, 01:00 AM   #44
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,556
Thanks: 88
Thanked 1,610 Times in 1,500 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: help with the Easiest way to finding where in the list

Try this modified version.

It now seems to be updating correctly & once again without errors.
Test it with your real data

BTW there are other issues with your form e.g. #Name in the bottom left that I'll leave you to sort out
Attached Files
File Type: zip 4RUpload_CR_UPDATED_20180509_v2.zip (292.4 KB, 5 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 05-09-2018 at 02:58 AM.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Gint32 (05-09-2018)
Old 05-10-2018, 04:20 AM   #45
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by ridders View Post
Try this modified version.

It now seems to be updating correctly & once again without errors.
Test it with your real data

BTW there are other issues with your form e.g. #Name in the bottom left that I'll leave you to sort out
Ridders, Thanks once again for your speedy responses and working solution!

Gint32 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] List not finding correct show Infinite Forms 0 07-03-2015 10:23 AM
finding smallest combinations of a list form another list buckau Modules & VBA 9 03-22-2009 03:31 PM
finding list needed yasin Forms 2 06-15-2005 09:21 PM
Finding List needed yasin Forms 0 06-15-2005 09:41 AM
[SOLVED] finding the average in a list of numbers edtree Reports 4 10-28-2000 10:00 AM




All times are GMT -8. The time now is 08:56 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World