Values (instead of text) from a ComboBox

[yaguaso]

How can be change the following instruction in VBA Excel to read values (instead of text) from a ComboBox ( cmbDiameter). This way works for text but for values gives an error.

If cmbDiameter.Text <> "" Then

If cmbItem.Text <> "" Then

strSQL = strSQL & " AND [Diameter]='" & cmbDiameter.Text & "'"

Else

strSQL = strSQL & " [Diameter]='" & cmbDiameter.Text & "'"

End If

End If

Thanks in advance

 

[Chihiro]

You'll need to give more context. What's in combobox? String or numeric?

If it's numeric value, then you need to use CDbl() or other conversion function to convert the text returned from combobox into numeric.

It would help if you can upload sample file that clearly demonstrates your issue, and what you want to accomplish.

 

[yaguaso]

). It works for text in the ComboBox (cmbDiametro) but for numbers (1,2,….so on) gives this error:

Private Sub cmdReset_Click()

  'clear the data

  cmbItem.Clear

  cmbDiametro.Clear

  Sheets("View").Visible = True

  Sheets("View").Select

  Range("dataSet").Select

  Range(Selection, Selection.End(xlDown)).ClearContents

End Sub

Private Sub cmdShowData_Click()

  'populate data

  strSQL = "SELECT * FROM [data$] WHERE "

  If cmbItem.Text <> "" Then

  strSQL = strSQL & " [ITEM]='" & cmbItem.Text & "'"

  End If

  If cmbDiametro.Text <> "" Then

  If cmbItem.Text <> "" Then

  strSQL = strSQL & " AND [DIAMETRO]=' & cmbDiametro.Value "

  Else

  strSQL = strSQL & " [DIAMETRO]='" & cmbDiametro.Value & ""

  End If

  End If


  If cmbItem.Text <> "" Or cmbDiametro.Text <> "" Then

  'now extract data

  closeRS

  OpenDB



  rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

  If rs.RecordCount > 0 Then

  Sheets("View").Visible = True

  Sheets("View").Select

  Range("dataSet").Select

  Range(Selection, Selection.End(xlDown)).ClearContents



  'Now putting the data on the sheet

  ActiveCell.CopyFromRecordset rs

  Else

  MsgBox "I was not able to find any matching records.", vbExclamation + vbOKOnly

  Exit Sub

  End If

  End If

End Sub

Thanks in advance
---------------------------------------------------------------------------------------
Mod Edit: added code tag.

 

[Chihiro]

Oh, I see where you went wrong.
Try this. Your concatenation syntax, actually concatenates "cmbDiametro.Value" as literal string instead of concatenating it's value.

 If cmbItem.Text <> "" Then

  strSQL = strSQL & " AND [DIAMETRO]= " & cmbDiametro.Value

 

[yaguaso]

Thanks Chihiro that concatenation worked fine. 20/20 points as we grade in Venezuela.

 

[yaguaso]

Thanks Chihiro: The example that you send to me works fine for integers numbers but not for decimal numbers, in ComboBox Diametro (Ex 0,75).
I' m sending the file.
Thanks again

 

[Chihiro]

Looks like you started another thread. I replied there with my guess.

 

[yaguaso]

Thanks Chihiro. I have Excel 10 and Windows 10. I tried switching system's regional setting and Excel's setting for period and commas and even then I get the same error. Annexed there is a file with summary of the error.
Regards

 

[Chihiro]

Continue in the other thread, to avoid jumping back and forth. I'm closing this thread.