following Dates

[habiler]

Hi,

My program crashes at the select instructions. Maybe a problem of quotes

thanks

Habiler

Private Sub createQry()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim qdf As DAO.QueryDef
    Dim newSQL As String
 
    newSQL = "SELECT LaTable.Num, LaTable.CODE,LaTable.LaDate, (DCount(""LaDate"", ""Latable"", ""Num="" & ""[num]"" and LaDate=""#"" & Format([LaDate] - 1, ""yyyy/dd/mm"") &""#"")) As C "
              & " FROM LaTable " _
              & " WHERE (((DCount(""LaDate"", ""Latable"", ""Num=" _
                       & "[num]  and LaDate=#"" & Format([LaDate] - 1, ""mm/dd/yyyy"") & ""#"")) = 0)) " _
              & " ORDER BY LaTable.Num, LaTable.CODE, LaTable.LaDate;"
Debug.Print newSQL
    Set qdf = db.CreateQueryDef("tempQry2", newSQL)
 
    DoCmd.OpenQuery ("tempQry2")
 
End Sub

[ranman256]

dont use vb code.
write it in an actual query. It will tell you whats wrong.

[habiler]

It's when I write this one that crashes "and LaDate=#"" & Format([LaDate] - 1, ""mm/dd/yyyy"") & ""#"")) = 0"

May be is the problem a quote ?

[JoeM]

Follow ranman's advice.

First create an example of this query in the Query Builder. When you get it working properly, switch to SQL View, and take a look at what the query looks like. This is what you are trying to build via VBA.
Now, after you build the SQL code in VBA, take a look at what it looks like and compare it to the query you manually built (it looks like you are already doing that with a Debug.Print command).
Note that differences and make the proper adjustments to your VBA code until they match. Then it should work.

[habiler]

Hello everybody,

I wrote a new code but all the records appears. I need only the records with the highest "n" for the NR_WKN = X.
How can i do that.

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