0

Assume the following:

(Disregard the bad sql syntax in "create table" segments, I'm simplifying the message for better understanding)

Create table Event_Participants ( 
Event_Name, text
Event_Participant, text
Event_Participant_Arrival, date/time
Event_Participant_Leaving date/time
)

There is no PK, only an index on Event_Name and Event_Participant to make sure no Participant can be logged more than once for one Event.

Assume, also

Create table Events (
Event_Name, text
Event_Start_Time, date/time
Event_End_Time, date/time
)

Again, no PK: Only Event_Name is a unique index

date1=[Query to get a participant particular arrival time for Event1]

date2=[Query to get Event1's particular Start_Time] (As you might've noticed, date2 is singular)

I want to run an update query and change some data with it, based on multiple conditions, wherein I only got stuck on the following

where DateDiff("n",date1,date2)<0

(This particular condition should check if anyone is late).

What I'm stuck with, is, how to do this date/time differential in minutes for all participants?

I can make it work when date1 is a single row, but on multiple, it says the "can fetch maximum one row" or something of the sort.

Nikola Pavlovic
  • 91
  • 10
  • You may have to rephrase this - and include your queries to make it clear. – Gustav Sep 26 '18 at 17:21
  • `date1= (select Event_Participant_Arrival from Event_Participants where Event_name=[forms]![Participant]![cbo_picker])` `date2=(select Event_Start_Time from Events where Event_Name=[Forms]![Participants]![cbo_picker])` _They work flawlessly, when I run a query solely for date1 and date2_ They both depend on user input in the Form called "Participants" where exists a combo-box that lists desired Event_Names by the internal name of cbo_picker. I thought this was irrelevant because it works by its own. – Nikola Pavlovic Sep 26 '18 at 18:02
  • What are *date1* and *date2*? Textboxes on a form? Indeed, when calculating a single value using a subquery, the subquery must return only (at most) one row.Or are you trying to find all participants of the selected event that are late? Btw: The WHERE clause you postet can also be expressed as `WHERE date1>date2` which is probably more accurate (at least if you also want to respect the seconds). – Wolfgang Kais Sep 26 '18 at 22:19
  • I need this to work for another update where date-differential in minutes is absolutely necessary In the second, I would need the (date1-date2) I don't actually require the end result to be a date, but an integer so... what can I do? – Nikola Pavlovic Sep 27 '18 at 06:07
  • As @WolfgangKais notes, _date1_ and _date2_ are not two date values but two lists of dates. So it is not clear which dates to subtract or what to update. – Gustav Sep 27 '18 at 07:57
  • I might be overcomplicating this There are Events and there are Participants Participants come and go as they please (one date for coming, one for going for each unique participant-event pair) Event is only one and only one date for starting and one for ending I want to calculate TIME PASSED in minutes for everyone at the event and also have people who are late start their timer upon arrival (not upon event_start time) and people who bail early have their timer stopped when they bail (not when event ends) I thought datediff would solve my troubles, but to no avail.... – Nikola Pavlovic Sep 27 '18 at 08:35

1 Answers1

0
Dim rs As DAO.Recordset
Dim qry, points, pp As String
Dim pts As Integer

pp = Me.Form.cbo_query_picker.Value
Set rs = CurrentDb.OpenRecordset("SELECT DateDiff('n',IIf([Arrival Date]<=(SELECT Start_Date FROM Events WHERE Descriptive_Name='" & pp & "'),(SELECT Start_Date FROM Events WHERE Descriptive_Name='" & pp & "'),[Arrival Date]),IIf([Quitting Date]>=(SELECT End_Date FROM Events WHERE Descriptive_Name='" & pp & "'),(SELECT End_Date FROM Events WHERE Descriptive_Name='" & pp & "'),[Quitting Date])) AS pts, Event_Participants.Participant, Event_Participants.[Event Name] FROM Event_Participants")
rs.MoveFirst
Do Until rs.EOF = True
    pts = Int(rs("pts").Value / 30) * 3
    qry = "update customers set points = points + "
    qry = qry & Format(pts, "0")
    qry = qry & " where [Customer name] = '"
    qry = qry & rs("Participant") & "'"
    CurrentDb.Execute (qry)
    Debug.Print qry
    rs.MoveNext
Loop
rs.Close

This is the VBA code I finally scrambled together to make the access work as any sql standard dialect would. While I acknowledge the answer by @Wolfgang Kais I got my problem solved through VBA instead, and would appreciate any suggestion, how to make this more, elegant. I feel this code can be improved to look better, but don't know how for now.

Nikola Pavlovic
  • 91
  • 10
  • Don't use `CurrentDb` inside a loop. This is slow and can lead to errors , because everytime you call `CurrentDb` a copy of the Database-Object ( usually `DbEngine(0).Workspaces(0)`) is created.To avoid that store the reference in a variable (`Dim db as Dao.Database: Set db = CurrentDb`) or more elegant use a `With` staement (`With CurrentDB ... End With`). Also with a `TableDef` object and then refer to it fails (`Set tdf = CurrentDb.TableDefs("myTable"): Debug.Print tdf.Name`), because of the missing parent Database-Object. – ComputerVersteher Oct 01 '18 at 22:09
  • The only problem my code has is that, upon execution, it blows up if it finds that the end-user failed to enter all the necessary fields in his records. (Invalid use of Null) Can you suggest a check of some sort (either in sql itself or vba) so that if it finds a null value, it doesn't break the execution (The SELECT statement when issued directly as a query works even when there are [Arrival Date] or [Quitting Date] missing, because the if function simply returns the impossible comparison (is some date < null) as false and doesn't break SQL query) Can something like this be done in VBA too? – Nikola Pavlovic Oct 02 '18 at 07:47
  • Also, what should "Dim db as Dao.Database" replace, if anything? And how should I wrap the code with the "With" statement? Use my code to show how and where it could be improved upon (feel free to "answer the question" again so you have more text formatting options) – Nikola Pavlovic Oct 02 '18 at 07:54
  • You should validate the user input to prevent missing/wrong entries. If you can't do that, you can check if a value is Null with the `IsNull(value)` function or replace the Null with `Nz(value,ValueIfNull)`.. The `CurrentDb` issue is off-topic as answer bur I will edit your answer to my suggestion.and share this link [currentdbtabledefs-vs-dbtabledefs](https://social.msdn.microsoft.com/Forums/office/en-US/7ea9506f-5e91-4896-80b9-6712762388ea/currentdbtabledefs-vs-dbtabledefs-object-invalid-or-not-set-error?forum=accessdev) – ComputerVersteher Oct 02 '18 at 11:43