Question QUERY LIKE BIRTHDAY

capricorne83170

New member
Joined
Jan 13, 2022
Messages
4
Programming Experience
5-10
I have the following problem: I want to extract from my access database the concerts that took place on day X. (Anniversary principle). I wish to release a report from this result. (The state works fine on its own. When I call it from VB without modifying the where clause) I wanted to prepare, for example, 5 states in advance. So I wrote this code:
VB.NET:
Dim DateJour As String
Dim I As Integer = 1
Dim strDB As String = CheminBase & "JH.mdb"
Dim RptNom As String

For I = 1 To 5
    DateJour = I
    
    If DateJour < 10 Then DateJour = "0" & DateJour
    
    Dim Mois As String = Month(Date.Today)
    
    If Mois < 10 Then Mois = "0" & Mois
    
    Dim Annee As String = Year(Date.Today)
    
    DateJour = DateJour & Mois & Annee
    ClauseWhere = "Format([con_date],ddmm) = Format(" & _ DateAdd(Interval:=DateInterval.Day, I, Date.Today) & ",ddmm) order by con_Date DESC "
    RptNom = "FICHE_JOUR_CONCERT_PLAN"
    OLEOpenReport_PLAN(strDB, RptNom, AcView.acViewPreview, , ClauseWhere)
Next

MsgBox("Planification terminée")
Here is the code of OLEOpenReport_PLAN
VB.NET:
Private Function OLEOpenReport_PLAN(ByVal strDBName As String, ByVal strRptName As String, Optional ByVal intDisplay As Access.AcView = Access.AcView.acViewNormal, Optional ByVal strFilter As String = "", Optional ByVal strWhere As String = "") As Boolean
    Dim bReturn As Boolean = True

    Try
        Dim objAccess As New Access.Application

        objAccess.OpenCurrentDatabase(strDBName, False)
        objAccess.DoCmd.OpenReport(strRptName, intDisplay, strFilter, strWhere, _ Access.AcWindowMode.acWindowNormal)
        objAccess.DoCmd.OutputTo(Access.AcOutputObjectType.acOutputReport, strRptName, _ OutputFormat:="PDF", "C:\Users\" & Environment.UserName & "\AppData\Roaming\IP-Informatique _ Pourrieres\J H L Appli\Editions\Editions Planifiees\" & strRptName & "_" & DateJour & _".pdf",,,,)
        objAccess.Quit(Access.AcQuitOption.acQuitSaveNone)
        objAccess = Nothing
    Catch ex As Exception
        bReturn = False     
        MessageBox.Show(ex.ToString, "Erreur Automation", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Try

    Return bReturn
End Function
At runtime I receive an "Operator absent" error at the level of the clausewhere. I also tried:

'{'ClauseWhere = "Format([con_date],ddmm) = Format(Date()+" & 1 & ",ddmm) order by con_Date DESC "'}'

which gives me the same error.

To be complete, here is the code of the state request:

'SELECT CONCERTS.CON_Date, CITIES.VIL_NOM FROM CITIES INNER JOIN CONCERTS ON CITIES.IDVILLE = CONCERTS.IDVILLE; ` Thank you in advance for your help.

Thierry
 
Last edited by a moderator:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,602
Location
Sydney, Australia
Programming Experience
10+
It doesn't make sense to create a date string in VB and then pass that into your SQL code to be formatted when it doesn't rely on anything in the database. If you want a date string then create it in VB code:
VB.NET:
ClauseWhere = $"Format([con_date],ddmm) = '{Date.Now:ddMM}' ORDER BY con_date DESC"
 

capricorne83170

New member
Joined
Jan 13, 2022
Messages
4
Programming Experience
5-10
Cela n'a pas de sens de créer une chaîne de date dans VB, puis de la transmettre à votre code SQL à formater lorsqu'il ne repose sur rien dans la base de données. Si vous voulez une chaîne de date, créez-la en code VB :
VB.NET:
ClauseWhere = $"Format([con_date],ddmm) = '{Date.Now:ddMM}' ORDER BY con_date DESC"

The date string has no use in the request. It is just used for the identification of the report. In addition, your where clause will only give me today's date. This is not what I need. Thank you anyway for your answer.
 
Top Bottom