Basic Visual Basic Program Error - Probably Variable/ Syntax Error (marked Fat)

Question

This is the edited code, with Gutav´s function and the implemented code from André.

It still seems to not work without an error. I'm clueless.

New code:

Option Explicit
Private Sub Form_Load()
    
    Dim rs          As Recordset
    Dim rs2         As Recordset
    Dim Werte       As String
    Dim Datums_variable As Date
    Dim actual_date As Date
    Dim id          As Integer
    Dim strSql      As String
    
    actual_date = Date
    
    Set rs = CurrentDb.OpenRecordset("select * from query_offene_wartungspunkte ", dbOpenDynaset)
    
    Me.Test_Liste.RowSourceType = " Value List "
    
    Do Until rs.EOF
        
        Werte = rs!intervall
        
        Select Case Werte
            
            Case "jährlich"
                Datums_variable = actual_date - 365
            Case "halbjährlich"
                Datums_variable = actual_date - 180
            Case "monatlich"
                Datums_variable = actual_date - 31
            Case "wöchentlich"
                Datums_variable = actual_date - 7
            Case "täglich"
                Datums_variable = actual_date - 1
            Case "vierteljährlich"
                Datums_variable = actual_date - 90
                
        End Select
        
        id = rs!id
        
        strSql = "select * from query_offene_wartungspunkte " & _
                 "where [Date] > " & CSql(Datums_variable) & " And id = " & id
        Debug.Print strSql

        Set rs2 = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
        
        If Not rs2.EOF Then
            Werte = " Arbeitsplatz/Maschine: " & rs2!arbeitsplatz_maschine & " ArbeitsplatzNr: " & rs2!arbeitsplatz_nr
            Me.Test_Liste.AddItem (Werte)
            
        Else
            ' rs2 is empty
            
            
        End If
        
        rs.MoveNext
    Loop
End Sub


Public Function CSql( _
       ByVal Value As Variant) _
       As String
    
    #If Win32 Then
        ' Serves only to make the code compile unmodified in 32-bit VBA
        ' which misses the constant VBA.vbLongLong.
        Const vbLongLong    As Integer = 20
    #End If
    
    Const SqlNull           As String = " Null"
    
    Dim Sql                 As String
    
    Select Case VarType(Value)
        Case vbEmpty        '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull        '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger        '    2  Integer.
            Sql = Str(Value)
        Case vbLong        '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle        '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble        '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency        '    6  Currency.
            Sql = Str(Value)
        Case vbDate        '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
        Case vbString        '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = "        '" & Sql & "'"
            End If
        Case vbObject        '    9  Object.
            Sql = SqlNull
        Case vbError        '   10  Error.
            Sql = SqlNull
        Case vbBoolean        '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant        '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject        '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal        '   14  Decimal.
            Sql = Str(Value)
        Case vbByte        '   17  Byte.
            Sql = Str(Value)
        Case vbLongLong        '   20  LongLong integer (Relevant in 64-bit VBA only).
            Sql = Str(Value)
        Case vbUserDefinedType        '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray        ' 8192  Array. Ignored.
            Sql = SqlNull
        Case Else        '       Should not happen.
            Sql = SqlNull
    End Select
    
    CSql = Sql & " "
    
End Function

Can someone help me please?

Like this:

Set rs2 = CurrentDb.OpenRecordset("select * from query_offene_wartungspunkte where Date > " & Datums_variable & " AND id = " & id = " & id, dbOpenDynaset)

I've tried this but it also throws an error

like this: Set rs2 = CurrentDb.OpenRecordset("select * from querry_offene_wartungspunkte where Date > " & Datums_variable & " AND id = " & id = " & id, dbOpenDynaset) — Valac, May 28 '21 at 12:56
Date parameter values need to be enclosed in single quotes, or, for MS Access, pound signs (#). — HardCode, May 28 '21 at 14:41
I am sorry to ask, but could you provide the full code with your solution, since very new in VBA and i cant seem to get it implemented right... — Valac, Jun 02 '21 at 12:57
What is the error / problem? What is the output of `Debug.Print strSql` ? (Use Ctrl+g to see it). — Andre, Jun 08 '21 at 17:34
this is the output: select * from query_offene_wartungspunkte where [Date] > #2020/12/12 00:00:00# And id = 2 — Valac, Jun 10 '21 at 12:25
Laufzeitfehler 3061 1 Parameter wurden erwartet, aber es wurden zu wenig Parameter übergeben — Valac, Jun 10 '21 at 13:04

score -1 · Answer 1 · answered May 30 '21 at 23:41

-1

If you want to concatenate variables with SQL, use Gustav's CSql() function .
It handles dates, strings and other variables.

strSql = "select * from query_offene_wartungspunkte " & _
         "where [Date] > " & CSql(Datums_variable) & " AND id = " & id
Debug.Print strSql
Set rs2 = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

Why Debug.Print? See How to debug dynamic SQL in VBA

The correct way to check if the recordset has rows is:

If Not rs2.EOF Then
    ' use values
Else
    ' rs2 is empty
End If

answered May 30 '21 at 23:41

Andre

26,751
7
36
80

I am sorry to ask, but could you provide the full code with your solution, since very new in VBA and i cant seem to get it implemented right... – Valac May 31 '21 at 08:37
Andre i have edited the code as u suggested, but it gives out following error while debugging: CSql function not declared – Valac Jun 07 '21 at 11:19
I linked the function, you need to copy it into a code module. – Andre Jun 07 '21 at 13:08
i updated the Code, could you please take a look? – Valac Jun 08 '21 at 08:28
select * from query_offene_wartungspunkte where [Date] > #2020/12/10 00:00:00# And id = 2 – Valac Jun 09 '21 at 09:49
Looks ok, what is the problem? – Andre Jun 09 '21 at 10:36

Basic Visual Basic Program Error - Probably Variable/ Syntax Error (marked Fat)

1 Answers1