VB SQL error; Syntax error (comma) in query expression-CodePudding

I have a VBA function returning a decimal value based on a WHERE clause.

SELECT * FROM [tbl_break] WHERE [From] < 6,25 AND [To] >= 6,25

I am getting the error... Syntax error (comma) in query expression '[From] < 6,25 AND [To] >= 6,25'.

The Tabel look like this...
From    To  Break
0,00    4,00    0,25
4,00    8,00    0,50
8,00    12,00   0,75

How can I deal with decimals in query statements?

Public Function CalculateBreak(Units) As Double

On Error GoTo ErrorHandler

Dim connection As Object, result As Object
Dim sql As String

    sql = "SELECT * FROM [tbl_break] WHERE [From] < " & Units & " AND [To] >= " & Units
    Debug.Print sql
    
    ' Connect to the current datasource of the Excel file
    Set connection = CreateObject("ADODB.Connection")
    With connection
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With
    
    ' Run the SQL query
    Set result = connection.Execute(sql)
    
    CalculateBreak = result(2)
    Debug.Print result(2)
    
    Exit Function
    
ErrorHandler:
    CalculateBreak = 0
    Debug.Print Err.Description
  
End Function

The solution works well with integers but I depend on decimal numbers.

CodePudding user response：

Use Str to force a string expression of the decimal value with a dot as the separator which is was SQL expects:

sql = "SELECT * FROM [tbl_break] WHERE [From] < " & Str(Units) & " AND [To] >= " & Str(Units) & ""

Result:

SELECT * FROM [tbl_break] WHERE [From] < 6.25 AND [To] >= 6.25