CONTROLL sequence of week date - strong prob

[sal21]

in the db are a table with a sequence of date (week days steep from moonday to friday).
The first 4 digit are the name of agency (AAAA, BBBB...ecc)
i need to controll if all agency have a sequence of days week steep...
possible with a sql query or a lop on the recordset?

note:
- In this case all agency have all the correct steep of days week but not have the steep day week based the current week 30/08/2010 to 03/09/2010.
- the first days week to start the controll is 31/05/2010 to 06/04/2010

I hope understand me:-(

[HansV]

The crosstab query Query2 in the attached version provides a quick overview.

db1.zip

[sal21]

The crosstab query Query2 in the attached version provides a quick overview.

db1.zip

WOW!

But how to understand wath is the agency and wath the steep of date not is present?

Similar fill a var with TEST=AAAA-...eccc when the steep of days week not is found

in effect i need to integrate the query in a vba excel code:-)

[HansV]

Perhaps something like this?

Code: Select all

Sub ListMissing2()
  Dim d As Date
  Dim strAgenzia As String
  Dim cnn As ADODB.Connection
  Dim rst As New ADODB.Recordset
  strAgenzia = "AAAA"
  Set cnn = CurrentProject.Connection ' change as needed
  rst.Open "SELECT * FROM Query1 WHERE AGENZIA='" & strAgenzia & "'", _
    cnn, adOpenKeyset, adLockOptimistic, adCmdText
  For d = DateSerial(2010, 5, 31) To Date Step 7
    rst.Find "DATA='" & Format(d, "yyyymmdd") & "'"
    If rst.EOF Then
      Debug.Print strAgenzia, d
    End If
  Next d
  rst.Close
  Set rst = Nothing
  Set cnn = Nothing
End Sub

[sal21]

Perhaps something like this?

Code: Select all

Sub ListMissing2()
  Dim d As Date
  Dim strAgenzia As String
  Dim cnn As ADODB.Connection
  Dim rst As New ADODB.Recordset
  strAgenzia = "AAAA"
  Set cnn = CurrentProject.Connection ' change as needed
  rst.Open "SELECT * FROM Query1 WHERE AGENZIA='" & strAgenzia & "'", _
    cnn, adOpenKeyset, adLockOptimistic, adCmdText
  For d = DateSerial(2010, 5, 31) To Date Step 7
    rst.Find "DATA='" & Format(d, "yyyymmdd") & "'"
    If rst.EOF Then
      Debug.Print strAgenzia, d
    End If
  Next d
  rst.Close
  Set rst = Nothing
  Set cnn = Nothing
End Sub

OK...
If i have understand i nee dto use this sub as a Function and loop the 5 agengy...? or not?
...
for i = 1 to 5
agengcy=sheets("test").range("A" & i)
ListMissing2 agengcy
next i
...

ecc...

[HansV]

Yes. You'd have to change the sub as follows:

Code: Select all

Sub ListMissing2(strAgenzia As String)
  Dim d As Date
  Dim cnn As ADODB.Connection
  Dim rst As New ADODB.Recordset
  Set cnn = CurrentProject.Connection ' change as needed
  rst.Open "SELECT * FROM Query1 WHERE AGENZIA='" & strAgenzia & "'", _
    cnn, adOpenKeyset, adLockOptimistic, adCmdText
  For d = DateSerial(2010, 5, 31) To Date Step 7
    rst.Find "DATA='" & Format(d, "yyyymmdd") & "'"
    If rst.EOF Then
      Debug.Print strAgenzia, d
    End If
  Next d
  rst.Close
  Set rst = Nothing
  Set cnn = Nothing
End Sub

[sal21]

Yes. You'd have to change the sub as follows:

Code: Select all

Sub ListMissing2(strAgenzia As String)
  Dim d As Date
  Dim cnn As ADODB.Connection
  Dim rst As New ADODB.Recordset
  Set cnn = CurrentProject.Connection ' change as needed
  rst.Open "SELECT * FROM Query1 WHERE AGENZIA='" & strAgenzia & "'", _
    cnn, adOpenKeyset, adLockOptimistic, adCmdText
  For d = DateSerial(2010, 5, 31) To Date Step 7
    rst.Find "DATA='" & Format(d, "yyyymmdd") & "'"
    If rst.EOF Then
      Debug.Print strAgenzia, d
    End If
  Next d
  rst.Close
  Set rst = Nothing
  Set cnn = Nothing
End Sub

no comment! WORK PERFECT

[sal21]

Yes. You'd have to change the sub as follows:

Code: Select all

Sub ListMissing2(strAgenzia As String)
  Dim d As Date
  Dim cnn As ADODB.Connection
  Dim rst As New ADODB.Recordset
  Set cnn = CurrentProject.Connection ' change as needed
  rst.Open "SELECT * FROM Query1 WHERE AGENZIA='" & strAgenzia & "'", _
    cnn, adOpenKeyset, adLockOptimistic, adCmdText
  For d = DateSerial(2010, 5, 31) To Date Step 7
    rst.Find "DATA='" & Format(d, "yyyymmdd") & "'"
    If rst.EOF Then
      Debug.Print strAgenzia, d
    End If
  Next d
  rst.Close
  Set rst = Nothing
  Set cnn = Nothing
End Sub

hummmmmmmmmmm.. possible to exclude from the loop the current week?

[HansV]

The line

For d = DateSerial(2010, 5, 31) To Date Step 7

loops up to the current date. If you want to exclude the current week, you could change it to

For d = DateSerial(2010, 5, 31) To Date - 7 Step 7

[sal21]

The line

For d = DateSerial(2010, 5, 31) To Date Step 7

loops up to the current date. If you want to exclude the current week, you could change it to

For d = DateSerial(2010, 5, 31) To Date - 7 Step 7

!!! Respect for you twice because when you post a solution, always explain the code, this is a good choice for me .
Tks Hans.
Sal.

[sal21]

The line

For d = DateSerial(2010, 5, 31) To Date Step 7

loops up to the current date. If you want to exclude the current week, you could change it to

For d = DateSerial(2010, 5, 31) To Date - 7 Step 7

Friend... my mind is destroyed to chenge the code for month!

Base the code in post is possible instead week chek the steep month... and exclude, as usual, the current month?

[HansV]

Code: Select all

d = DateSerial(2010, 5, 31)
Do While d <= Date - Day(Date)
  ...
  d = DateAdd("m", 1, d + 1) - 1
Loop

[sal21]

Code: Select all

d = DateSerial(2010, 5, 31)
Do While d <= Date - Day(Date)
  ...
  d = DateAdd("m", 1, d + 1) - 1
Loop

Fow to integrate here (the example is for old week speep):

Code: Select all

Sub ListMissing2(AGENZIA_NT As String)

    Dim D As Date
    Dim rst As New ADODB.Recordset

    rst.Open "SELECT * FROM DATE_INQ WHERE DT='" & AGENZIA_NT & "'", _
             CN, adOpenKeyset, adLockOptimistic, adCmdText
    For D = DateSerial(2010, 5, 31) To Date - 7 Step 14
        V1 = Format(D, "yyyymmdd")
        rst.Filter = "DATE_1 = '" & V1 & "'"
        If rst.EOF Then
            Me.ListBox1.AddItem AGENZIA_NT & "-" & D & "-" & DateAdd("D", D, 4)
            CONTA = CONTA + 1
            Me.Label14.Caption = CONTA
        End If
    Next D

    rst.Close
    Set rst = Nothing

End Sub

[HansV]

Replace

For D = DateSerial(2010, 5, 31) To Date - 7 Step 14

with

d = DateSerial(2010, 5, 31)
Do While d <= Date - Day(Date)

and

Next D

with

d = DateAdd("m", 1, d + 1) - 1
Loop

[sal21]

Replace

For D = DateSerial(2010, 5, 31) To Date - 7 Step 14

with

d = DateSerial(2010, 5, 31)
Do While d <= Date - Day(Date)

and

Next D

with

d = DateAdd("m", 1, d + 1) - 1
Loop

Code: Select all

Sub ListMissing1(AGENZIA_NT As String)


    Dim D As Date
    Dim rst As New ADODB.Recordset

    rst.Open "SELECT * FROM DATE_INQ WHERE DT='" & AGENZIA_NT & "'", _
             CN, adOpenKeyset, adLockOptimistic, adCmdText

    D = DateSerial(2010, 10, 31)
    Do While D <= Date - Day(Date)
        V1 = Format(D, "yyyymmdd")
        rst.Filter = "DATE_1 = '" & V1 & "'"
        If rst.EOF Then
            Debug.Print AGENZIA_NT & "-" & D & "-" & DateAdd("D", D, 1)
            Me.ListBox1.AddItem AGENZIA_NT & "-" & D & "-" & DateAdd("D", D, 1)
            CONTA = CONTA + 1
            Me.Label14.Caption = CONTA
        End If
        D = DateAdd("m", 1, D + 1) - 1
    Loop

    rst.Close
    Set rst = Nothing
    
End Sub

based this code see wath i have in debug.print for this loop!

8501-31/10/2010-01/11/2010
8501-30/11/2010-01/12/2010
8501-31/12/2010-01/01/2011

note:
for month i have changed 31/05/2010 to 31/10/2010

[HansV]

So? What is the problem?

[sal21]

So? What is the problem?

i need:
8501-01/11/2010-31/11/2010
8501-01/12/2010-31/12/2010
8501-01/01/2011-31/01/2011
...

[HansV]

You have to change the line

Me.ListBox1.AddItem AGENZIA_NT & "-" & D & "-" & DateAdd("D", D, 1)

[sal21]

You have to change the line

Me.ListBox1.AddItem AGENZIA_NT & "-" & D & "-" & DateAdd("D", D, 1)

????

[HansV]

You should be able to work this out for yourself, Sal. I'm not going to do your entire job for you.