Non-continuous counting in query (1 Viewer)

Micron

AWF VIP
Local time
Today, 12:25
Joined
Oct 20, 2018
Messages
2,807
It means you're trying to add the wrong type of data somewhere - text instead of number for example.
Better to post code rather than pictures (in code tags please) and just state the error text (and its number if applicable). Pics are too small on some people's monitors, or too fuzzy, and it's impossible to scroll the window you're capturing to see what can't be seen.

BTW - I'm not trying to step on anyone's toes by jumping back in. You're getting some great help; I just didn't want you to languish over what might have been a simple break issue but I will be around a bit if no one responds for this part.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:25
Joined
May 21, 2018
Messages
3,163
That is not the correct code. When you sent your updated database with real data I provided updated code. I also do a Distinct query prior to running the code. This will save a ton of time. ClockNumber is a text field
Code:
Public Function GetAbsInRange(ClockNumber As String, Optional StartPeriod As Date = 0, Optional EndPeriod As Date = 0)
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim absPeriod As Integer
  Dim prev As String
  If StartPeriod = 0 Or EndPeriod = 0 Then
   strSql = "Select * from Occurences where clockID = '" & ClockNumber & "' order by WorkDate"
  Else
   strSql = "Select * from Occurences where clockID = '" & ClockNumber & "' AND WorkDate between #" & Format(StartPeriod, "mm/dd/yyyy") _
           & "# AND #" & Format(EndPeriod, "mm/dd/yyyy") & "# order by WorkDate"
  End If
  Set rs = CurrentDb.OpenRecordset(strSql)
  'Debug.Print strSql
  Do While Not rs.EOF
    'Debug.Print rs!WorkType
    If prev <> rs!WorkType And rs!WorkType = "ABS" Then
      absPeriod = absPeriod + 1
    End If
    prev = rs!WorkType
    rs.MoveNext
  Loop
  GetAbsInRange = absPeriod
End Function
 

AnnaMagna

New member
Local time
Today, 17:25
Joined
Mar 16, 2020
Messages
11
Hi MajP,

Sorry for the delay in responding to this; with the current climate and personal factors i have not had the chance to look at this for a week or two.

Thank you for this; sorry that was my mistake and as you said i hadn't updated the code correctly. I have now updated the code and it is working :D:D. Which has made me very happy, so thank you.

However, there is one thing i need changing which, my apologies, i did not outline at the beginning. The dates for the start period and end period need amending, how do i do this? I have another query in the database which outlines the date that is the end period, the query is "ABS absence hours last date" and the field is "MaxOfAbsEnd". The start date then needs to be:
26 weeks before this end date if the field "52wks" in table "Data" is No
52 weeks before this end date if the field "52wks" in table "Data" is Yes

Is this possible?
Sorry, i also know that my query name is not ideal with spaces in- i am working on amending them all so they follow good practice.

Thanks,
Anna
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:25
Joined
May 21, 2018
Messages
3,163
Build the query (qryOccurences) first. It should be a distinct query returning one record per ClockID, based on tblOccurrences and "ABS absence hours last date"
Code:
This query (qryOccurences) should have the fields
  ClockID
  EndPeriod: MaxOfAbsEnd, 
  StartPeriod: iif([52wks],dateAdd("w",-52,[MaxOfAbsEnd]),dateAdd("w",-26,[maxofabsEnd]))
Now create new query
Code:
Select ClockID, GetAbsInRange([clockID],[StartPeriod],[EndPeriod]) as Absences from qryOccurences order by ClockID
Here is an updated version of the function allows you to specify a between, start, end, or all.
Code:
Public Function GetAbsInRange(ClockNumber As String, Optional StartPeriod As Date = 0, Optional EndPeriod As Date = 0)
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim absPeriod As Integer
  Dim prev As String
  If StartPeriod = 0 And EndPeriod = 0 Then 'Include all records
   strSql = "Select * from Occurences where clockID = '" & ClockNumber & "' order by WorkDate"
  ElseIf StartPeriod = 0 And EndPeriod <> 0 Then 'Include all records <= endperiod
    strSql = "Select * from Occurences where clockID = '" & ClockNumber & "' AND WorkDate <= #" & Format(EndPeriod, "mm/dd/yyyy") & "# order by WorkDate"
  ElseIf StartPeriod <> 0 And EndPeriod = 0 Then 'Include all records >= to startPeiod
     strSql = "Select * from Occurences where clockID = '" & ClockNumber & "' AND WorkDate >= #" & Format(StartPeriod, "mm/dd/yyyy") & "# order by WorkDate"
  Else 'Include all records between startPeiod and EndPeriod
   strSql = "Select * from Occurences where clockID = '" & ClockNumber & "' AND WorkDate between #" & Format(StartPeriod, "mm/dd/yyyy") _
           & "# AND #" & Format(EndPeriod, "mm/dd/yyyy") & "# order by WorkDate"
  End If
  Set rs = CurrentDb.OpenRecordset(strSql)
  'Debug.Print strSql
  Do While Not rs.EOF
    'Debug.Print rs!WorkType
    If prev <> rs!WorkType And rs!WorkType = "ABS" Then
      absPeriod = absPeriod + 1
    End If
    prev = rs!WorkType
    rs.MoveNext
  Loop
  GetAbsInRange = absPeriod
End Function

Public Sub TestPeriod()
  Debug.Print GetAbsInRange("002002", #4/1/2019#, #5/1/2019#)
  Debug.Print GetAbsInRange("002002", #4/1/2019#)
  Debug.Print GetAbsInRange("002002", , #5/28/2019#)
  Debug.Print GetAbsInRange("002002")
End Sub
 

AnnaMagna

New member
Local time
Today, 17:25
Joined
Mar 16, 2020
Messages
11
Hi MajP,

Thanks for this.

I now have 1 query; for an employee where the latest record in the occurences table is ABS, it isn't including this in the occurences count. Is there a way round this? Also, for some employees that only have 1 ABS in the whole data set, they are showing on the query but with 0 occurences, is there a way round this? I was thinking i could just create a field in the query that =occurences, and if this is 0, then make it 1. But, what is bugging me is that for some employees with 1 occurence it is showing correctly as 1?

Thanks so much for your help, i really do appreciate it.

Thanks
Anna
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:25
Joined
May 21, 2018
Messages
3,163
Let me take a look, I would think it should handle that. Can you provide the ClockID for that case?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:25
Joined
May 21, 2018
Messages
3,163
I tested this and cannot recreate the issue. The function seems to be correct. The issue could be with your query. If it is not counting the last day absence it could be that you are not providing the correct last day or first day. Can you provide an update demo and point out ones not working? Thanks.
 

AnnaMagna

New member
Local time
Today, 17:25
Joined
Mar 16, 2020
Messages
11
Hi MajP,

I'm so sorry, i THINK this is me not updating the data correctly, i am just re-importing the data and will test again, hopefully in the next few hours. I will let you know....

Thanks
Anna
 

AnnaMagna

New member
Local time
Today, 17:25
Joined
Mar 16, 2020
Messages
11
Hi MajP,

I have re-imported my data and it is now working exactly as i want it, thank you so much, really appreciate all your help (y)

Anna
 

arnelgp

error reading drive A:
Local time
Tomorrow, 00:25
Joined
May 7, 2009
Messages
9,349
just passing by.
I made a function also in Module1
made query1 which filters only "ABS".
create query2 and uses udfGroupAbs() function.
lastly put query2 in a datasheet form (formDatasheet) so that we "reset" the function each time
the form's open.

recommend to use formDatasheet and not query2.
 

Attachments

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom