how to insert comma in sql expression

Question

I have code line like this:

  "select count(1) from tblEquipmentRegister where EquipmentID = " & Me.cmbEquipmentID.Value & " and StartTime is null"

which results this string:

  "select count(1) from tblEquipmentRegister where EquipmentID = 203203,16 and StartTime is null"

when I pass this string to function

  Public Function getOneValue(strSQL As String)
  Dim q As QueryDef
  Dim rec As Recordset
  Set q = CurrentDb.QueryDefs("qTmp")

  q.SQL = strSQL
  Set rec = q.OpenRecordset

  getOneValue = rec.Fields(0).Value

  Set q = Nothing
  Set rec = Nothing

  End Function

I get syntax error (comma) in query expression I don't get error if EquipmentID is number without a comma. How should I construct string with a comma in the EquipmentID?

Enclose the EquipmentId with in single quote. "select count(1) from tblEquipmentRegister where EquipmentID = '" & Me.cmbEquipmentID.Value & "' and StartTime is null" — Thangadurai
– Thangadurai, Commented Jan 9, 2015 at 6:42

Tom Robinson · Accepted Answer · 2015-01-09 22:18:49Z

2

If there is any possibility that EquipmentID could contain a single quote, the above code will fail.

I use a function qString to both wrap the string in quotes and to double-up any embedded quotes:

    ' Return string S quoted, with quotes escaped, for building SQL.
    ' O'Connor -> 'O''Connor'
    Public Function QString(ByVal s As String) As String
       QString = "'" & Replace(s, "'", "''") & "'"
    End Function

If all you want is the record count, you can get it much more easily with the DCount built-in function. This single statement will replace all of your code:

    getOneValue = DCount("*", "tblEquipmentRegister", _
                  "EquipmentID = " & QString(cmbEquipmentID) & _
                  " AND StartTime IS NULL")

answered Jan 9, 2015 at 22:18

Tom Robinson

1,9501 gold badge15 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chankey Pathak · Accepted Answer · 2015-01-09 06:40:32Z

1

select count(1) from tblEquipmentRegister where EquipmentID = 203203,16 and StartTime is null

should be

select count(1) from tblEquipmentRegister where EquipmentID = '203203,16' and StartTime is null

In short

select * from table where val=number

is correct if value is integer.

but if it's a string then you should use

select * from table where val='some string'

So 203203,16 is string and 20320316 is number, that's why it works fine without comma.

So you should edit your code to

select count(1) from tblEquipmentRegister where EquipmentID = '" & Me.cmbEquipmentID.Value & "' and StartTime is null

answered Jan 9, 2015 at 6:40

Chankey Pathak

21.8k12 gold badges88 silver badges138 bronze badges

2 Comments

PauliusM Over a year ago

then I get data type mismatch in criteria expression on the code line set rec = q.OpenRecordset

Chankey Pathak Over a year ago

that's a must do, you can't enter string in integer data type.

Collectives™ on Stack Overflow

how to insert comma in sql expression

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related