none
My query returns no records, can you help? RRS feed

  • Question

  • Good morning,

    I have a query named "Requête 1" based on a table named "Périodes fiscales"

    In that query I have 2 fields named "DébutPériode" and "FinPériode" that are date field the first for the beginning of a specific month, the other the end of that month... so far so good.

    Now, have a form named "Achats chez les fournisseurs" on which I have a date field named "Date de la facture", I tried to retrieve a record based on that field, so I put a criteria in that query... 

    So here is an image:

    So I tried something else on a button named "ACCEPTER" on the form "Achats chez les fournisseurs"

    Private Sub Accepter_Click()
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim strSQL As String
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("mouvement", dbOpenDynaset, dbSeeChanges)
    
    strSQL = "SELECT * " & _
    "FROM [Périodes fiscales]" & _
    "WHERE Forms![Achats chez les fournisseurs].[Date de la facture] BETWEEN [Périodes fiscales].[DébutPériode] AND [Périodes fiscales].[FinPériode]"
    Set rst2 = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    
    If Forms![Achats chez les fournisseurs].TransférerGL = 0 And Forms![Achats chez les fournisseurs].Payée = 0 And Forms![Achats chez les fournisseurs].TOTAL > 0 Then
    With rst
    .AddNew
        ![ecriture] = 3
        ![compte] = Forms![Achats chez les fournisseurs].[CompteGLFournisseurs]
        ![NomDuFournisseur] = Forms![Achats chez les fournisseurs].[NomSociété]
        ![PériodeFiscale] = rst2![PériodeFiscale]
        ![credit] = Forms![Achats chez les fournisseurs].[TOTAL]
        ![DateTransaction] = Forms![Achats chez les fournisseurs].[Date de la facture]
        If Me.CoûtTransféré = 0 Then
        ![Description] = "Réception facture du fournisseur sans inventaire" & "-" & [NomSociété]
        ![BonDeCommande] = Forms![Achats chez les fournisseurs].[Réf facture]
        ElseIf Me.CoûtTransféré > 0 Then
        ![Description] = "Réception facture du fournisseur avec inventaire" & "-" & [NomSociété]
        ![BonDeCommande] = Forms![Achats chez les fournisseurs].[Réf bon de commande]
        End If
        .Update
    End With
    End If
    
    End Sub

    This time I have an error on this line: Set rst2 = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

    it is an error 3061 too few parameters 1 expected... :)

    Thanks for your time

    Claude from Quebec, Canada


    Claude Larocque

    Friday, May 12, 2017 11:51 AM

Answers

  • strSQL = "SELECT * " & _
    "FROM [Périodes fiscales]" & _
    "WHERE Forms![Achats chez les fournisseurs].[Date de la facture] BETWEEN [Périodes fiscales].[DébutPériode] AND [Périodes fiscales].[FinPériode]"

    Hi Claude,

    I am not too familiar with all those variables build in in the strSQL. What I see is that you are missing a space before "WHERE". Perhaps you need some "#" around the dates.

    For construction SQL-strings I always use a standard way of writing, with never e space at the end of the line:

    strSQL = "SELECT *" _
               & " FROM [Périodes fiscales]" _
               & " WHERE Forms![Achats chez les fournisseurs].[Date de la facture] BETWEEN [Périodes fiscales].[DébutPériode] AND [Périodes fiscales].[FinPériode]"

    Except for the line with "SELECT.", all other lines start with a space. Easy to verify!

    Another typical way of my working is never to use BETWEEN, but instead the combination of  >=  AND <.

    The >= compares to the start of (any) period, the < compares to the start of the next period. This way of writing ensures a perfact match of subsequent time periods without and duplication of ppoints in time, and without any gap of points in time. If you have for whatever reason some decimal fraction in the dates, then all works correct. Besides it saves a column in your "Périodes fiscales" table.

    Imb.

    Friday, May 12, 2017 12:21 PM
  • Your query logic is a little bit off. English words here but the date criteria expression should look like the following:

    With respect to the SQL statement, the value entered in the Form controls must be evaluated outside of the string. This is air code but I think it's what you need:

    strSQL = "SELECT * " & _
    "FROM [Périodes fiscales] " & _
    "WHERE #" & Forms![Achats chez les fournisseurs].[Date de la facture] & "# BETWEEN [Périodes fiscales].[DébutPériode] AND [Périodes fiscales].[FinPériode]"
    It's possible the data from the Form may need to be formatted based upon what is entered, but I would try the above first.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, May 12, 2017 12:49 PM

All replies

  • strSQL = "SELECT * " & _
    "FROM [Périodes fiscales]" & _
    "WHERE Forms![Achats chez les fournisseurs].[Date de la facture] BETWEEN [Périodes fiscales].[DébutPériode] AND [Périodes fiscales].[FinPériode]"

    Hi Claude,

    I am not too familiar with all those variables build in in the strSQL. What I see is that you are missing a space before "WHERE". Perhaps you need some "#" around the dates.

    For construction SQL-strings I always use a standard way of writing, with never e space at the end of the line:

    strSQL = "SELECT *" _
               & " FROM [Périodes fiscales]" _
               & " WHERE Forms![Achats chez les fournisseurs].[Date de la facture] BETWEEN [Périodes fiscales].[DébutPériode] AND [Périodes fiscales].[FinPériode]"

    Except for the line with "SELECT.", all other lines start with a space. Easy to verify!

    Another typical way of my working is never to use BETWEEN, but instead the combination of  >=  AND <.

    The >= compares to the start of (any) period, the < compares to the start of the next period. This way of writing ensures a perfact match of subsequent time periods without and duplication of ppoints in time, and without any gap of points in time. If you have for whatever reason some decimal fraction in the dates, then all works correct. Besides it saves a column in your "Périodes fiscales" table.

    Imb.

    Friday, May 12, 2017 12:21 PM
  • Your query logic is a little bit off. English words here but the date criteria expression should look like the following:

    With respect to the SQL statement, the value entered in the Form controls must be evaluated outside of the string. This is air code but I think it's what you need:

    strSQL = "SELECT * " & _
    "FROM [Périodes fiscales] " & _
    "WHERE #" & Forms![Achats chez les fournisseurs].[Date de la facture] & "# BETWEEN [Périodes fiscales].[DébutPériode] AND [Périodes fiscales].[FinPériode]"
    It's possible the data from the Form may need to be formatted based upon what is entered, but I would try the above first.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, May 12, 2017 12:49 PM
  • Thanks Imb and Paul, by taking a part of your response, I was able to make it work perfectly, here's the code:

    Hopefully it will serve someone else :)

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim strSQL As String
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("mouvement", dbOpenDynaset, dbSeeChanges)
    
    strSQL = "SELECT * " & _
    "FROM [Périodes fiscales] " & _
    "WHERE #" & Forms![Achats chez les fournisseurs].[Date de la facture] & "# >= [Périodes fiscales].[DébutPériode] AND #" & Forms![Achats chez les fournisseurs].[Date de la facture] & "# <= [Périodes fiscales].[FinPériode]"
    Set rst2 = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    
    If Forms![Achats chez les fournisseurs].TransférerGL = 0 And Forms![Achats chez les fournisseurs].Payée = 0 And Forms![Achats chez les fournisseurs].TOTAL > 0 Then
    With rst
    .AddNew
        ![ecriture] = 3
        ![compte] = Forms![Achats chez les fournisseurs].[CompteGLFournisseurs]
        ![NomDuFournisseur] = Forms![Achats chez les fournisseurs].[NomSociété]
        ![PériodeFiscale] = rst2![PériodeFiscale]
        ![credit] = Forms![Achats chez les fournisseurs].[TOTAL]
        ![DateTransaction] = Forms![Achats chez les fournisseurs].[Date de la facture]
        If Me.CoûtTransféré = 0 Then
        ![Description] = "Réception facture du fournisseur sans inventaire" & "-" & [NomSociété]
        ![BonDeCommande] = Forms![Achats chez les fournisseurs].[Réf facture]
        ElseIf Me.CoûtTransféré > 0 Then
        ![Description] = "Réception facture du fournisseur avec inventaire" & "-" & [NomSociété]
        ![BonDeCommande] = Forms![Achats chez les fournisseurs].[Réf bon de commande]
        End If
        .Update
    End With
    End If

    Thanks again


    Claude Larocque

    Saturday, May 13, 2017 9:32 AM