Recent content by cheekybuddha

Don't wait too long! 99 times out of 100, multiple users sharing/accessing the same copy of an Access db will lead to errors and eventual corruption.

I take it you saw Post #9 ?

Did that include rebooting your router?

Oops! 😬 Cheers, Isaac! (y) Probably too late now that an edit will have no effect, (ie notification to wrong member will already have been sent), so I'll leave it as is. 🤪

@Doc, have you tried disabling your anti-virus temporarily? Otherwise, a reboot of your router is worth a try, and also trying other DNS providers if you are using ISP provided DNS (eg Cloudflare: 1.1.1.1 or Google 8.8.8.8)

Ah! I completely missed the PRON_ID! How about this: -- ... INNER JOIN ( SELECT a.PRON_ID, a.CurrentProcPointID, a.TimeStamp, a.ProcPointStartDate, ( SELECT TOP 1 b.ProcPointStartDate FROM tbl_Actual_PROC_Point AS b WHERE...

Interesting! I wonder where that 12/152025 came from in the second record. My guess it came from the Timestamp tie-splitter. I know you have a solution, so no worries if you can't be bothered, I'm still curious if either of the following variations might work (since I have no data to test...

Also curious as to how different this query would be from yours SELECT p.PRON, a1.TimeStamp, c.[Current Proc Point], a1.ProcPointStartDate, Nz(a1.EndDate, Date()) AS EndDate, Int(Nz(a1.EndDate, Date()) - a1.ProcPointStartDate) AS TimeInProc, a1.[Updated By], c.PALT FROM (...

Sometimes speed is the issue, or a requirement to have data frozen at a fixed point in time, but often it seems to be done unnecessarily. Using the SELECT query directly is usually safer since it will reflect live data and not need to be updated when underlying data changes.

I'm always curious why you need to insert data that can be got with a select query into another table when you could just use the query instead?

Ouch! Just noticed CurrentProcPointID used in the join isn't selected in the subquery - will edit the query in Post #4 to correct that.

If this Requery code is for two listboxes in the same form, then why isn't the code in that form's module? If you put this code in the form's module: Sub RequeryLstBx() Const lst1 As String = "lstName", _ lst2 As String = "lstTrans" Dim lst As Variant, lsts As Variant lsts =...

NB. All the above is complete AIRCODE! 😬

Next, add in the other fields from tbl_Actual_PROC_Point : SELECT a.PRON_ID, a.CurrentProcPointID, a.TimeStamp, a.ProcPointStartDate, ( SELECT TOP 1 b.ProcPointStartDate FROM tbl_Actual_PROC_Point AS b WHERE b.ProcPointStartDate > a.ProcPointStartDate ) AS EndDate...

Break it down into bitesize chunks. First make sure you get the right end dates from the start dates. SELECT a.PRON_ID, a.ProcPointStartDate, ( SELECT TOP 1 b.ProcPointStartDate FROM tbl_Actual_PROC_Point AS b WHERE b.ProcPointStartDate > a.ProcPointStartDate ) AS...