count the weekend in a total of days

[habiler]

Hello has all,

In the continuity of adjoining days I try to fill the space leave by the weekends.

In fact I have a field LaDate with the data
1- In May 01st


1 - May 02nd
1 - May 03rd
1 - May 04th Saturday,
1 - May 05th Sunday,
1 - May 06th
2 - May 01st
2 - May 02nd
2 - May 03rd

As result I would like to have:
1 - May 01st - May 06th 6 days
2 - May 01st - May 03rd 3 days

Thanks to all

Habiler

[ranman256]

make a query to get the Max and Min of the record
Q1: select id, Max(dateFld),Min(DateFld)

then Q2 , use Q1 to get the #days
Q2: select MaxOfDate - MinOfDate as TotalDays from Q1

[June7]

You want to concatenate the first and last records of each group as well as count?

Does that first record really have the word 'In'? Why? Are the words 'Saturday' and 'Sunday' really in the data?

Try an aggregate (GROUP BY) query.

SELECT field1, Min(field2) & " - " & Max(field2) AS Period, Count(*) AS CntDays FROM tablename GROUP BY field1;

However, this won't work if the date range for each group crosses months because you are using month names.

[habiler]

Hello,

My problem was completely badly written.

In fact I have a field LaDate with the data
1- May 01st
1 - May 02nd
1 - May 03rd
(1 - May 04th Saturday, ) - Don't appear in the DB
(1 - May 05th Sunday, ) - Don't appear in the DB
1 - May 06th
1 - May 07th
1 - May 18th
2 - May 03rd
(Weekend 4 & 5/5)
2 - May 06th
2 - May 07th
2 - Jun 01st
As result I would like to have:
1 - May 01st - May 07th 5 day
1 - May 18th - May 18th 1 days
2 - May 03rd - May 07th 3 days
2 - Jun 01st - Jun 01st 1 day

I've written this code by i can't include the WE.

Public Function nbJ(nP As Long, d1 As Date)
   Dim n As Long, sSQL As String
   n = 0
   While True
      n = n - 1
      sSQL = "NR_WKN = " & nP & " And PERIODE = #" & Format(DateAdd("d", n, d1), "yyyy-mm-dd") & "#"
      'Debug.Print sSQL,
      'Debug.Print Nz(DCount("*", "Maladies", sSQL))
      If Nz(DCount("*", "Maladies", sSQL)) = 0 Then
         nbJ = n
      Exit Function
      End If
   Wend
End Function

[aytee111]

See if this is doing what you want.