ComboBoxes to search data in a table

[yaguaso]

Two ComboBoxes to search data in a table. One to Select ITEM y the other DIAMETRO. The first search data strings and later to search numbers.


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

error à 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

The above subroutine works fine for integers in DIAMETRO but for decimals (ex 0,75) gives the following error:

A runtime error occurred -2147217900 (80040e14):

[Microsoft] [Controller] ODBC Excel] Syntax error (comma) in the query expression '' [ITEM] ='TUBERIA' AND [DIAMETER] = 0,75. '

Note: I declared Public strSQL As Variant

I will thanks any help to solve this situation.

 

[NARAYANK991]

Hi ,

Your code is :

If cmbItem.Text <> "" Then

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

Else

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

End If

The second assignment to strSQL should be :

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

Narayan

 

[yaguaso]

Thanks Narayan but that does not work.
Iam sending the file. It works for integer number in DIAMETER but not for Decimals (ex 0,75). Try it.

 

[NARAYANK991]

Hi ,

I tried out the options you mentioned , and everything works correctly.

I don't get an error message.

Narayan

 

[yaguaso]

Again, Thanks NARAYANK
I do not what happen with this program but for me it still gives error message even yours -pull_Info1. I. I will continue looking the refered error.
Regards.

 

[NARAYANK991]

Hi ,

Can you post the following 2 :

1. The error message itself

2. The line of code which is highlighted when the error message is displayed

Narayan

 

[Chihiro]

My guess is that it's due to decimal separator.

What's used in your db for decimal separator? Is it comma "," or period "."?

As well, what's your system's regional setting and Excel's setting?

If there is conflict between db and either two, this can cause issue.
You'll either have to change your system setting or replace separator to match that of your db.

 

[yaguaso]

Hi ,

Can you post the following 2 :

1. The error message itself

2. The line of code which is highlighted when the error message is displayed

Narayan

Ok Narayan. Annexed there is a file with the summary of the error.
Regards

 

[Chihiro]

FYI - If you need to use comma as separator, ensure you nest it in single quote.
Ex:

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

 

[yaguaso]

Thanks Chihiro. I installed Office 2013 and the refered program works fine. The problem was i had Excel 2010.
Regards

 

[yaguaso]

Thanks Narayank991. After many attempts to run the referred program I realized that it does not work with Excel 10. Therefore I decided to install Office 2013 and try the program and it worked very well.

Regards.

 

[Chihiro]

Hmm? Excel 2010 or 2013, ADO code works.
I routinely use it for compatibility for Excel 2003 to 2016.

And the error message in your initial post indicates that issue lies in syntax of query string.

Likely some setting that was changing your decimal separator was reset when updated.

 

[yaguaso]

Thanks Chihiro for reopening the blog.

You are right the referred problem is with the decimal separator. I tested the program with both version of Microsoft Office 2013. It works well with English version, but not for the Spanish version. Definitely is a situation where is required to set the decimals with points, in a dynamic way, that is the decimal point must appears in the ComboBox instead of comma. I am still in search of the solution of this problem.

Regards. Orlando.

 

[NARAYANK991]

Hi ,

In your Spanish version of Excel , how does 0.75 display in a worksheet cell ?

How does 0.75 display in the second combobox in the worksheet ?

When you are adding records to the second combobox , instead of this line of code :

            cmbDiametro.AddItem rs.Fields(0)

can you try using this :

            cmbDiametro.AddItem Format(rs.Fields(0), "0" & Application.International(xlDecimalSeparator) & "00")

Narayan

 

[Chihiro]

Alternate to what Narayan suggested. You could just replace "," with "." during query string construction.
Ex:

Replace(cmbDiametro.Value,",",".")

 

[yaguaso]

Hi ,

In your Spanish version of Excel , how does 0.75 display in a worksheet cell ?

How does 0.75 display in the second combobox in the worksheet ?

When you are adding records to the second combobox , instead of this line of code :

            cmbDiametro.AddItem rs.Fields(0)

can you try using this :

            cmbDiametro.AddItem Format(rs.Fields(0), "0" & Application.International(xlDecimalSeparator) & "00")

Narayan

Hi Narayan

I modified the coded line that you proposed.

The cmbDiametro showed this:

It gives only Integer values of 1 in Diametro instead of 0,50. Same for 0,75, and for 1,5 shows DIAMETRO values of 2.

Note: Anexed is another file (Print ITEM) but with the same conception of the previous one.

Thanks,

Orlando.

 

[yaguaso]

Values in a ComboBox (Solved)


Hi Narayan, Chihiro

A UserForm with Two ComboBoxes one to filter data with text (cmbItem) and the other for filtering integers and decimals values (cmbDiametro)

To find unique values in the ComboBox cmbDiametro there is a SQL Statement to populate data and filter out data as per the drop down options selected. The statement is the following:

1. It was necessary to format the cmbDiametro.AddItem with Application.International (xlDecimalSeparator) due the Excel regional setting is configured with comma to separate decimals.value.

2. The Where clause was modified to convert the decimal number to text. I use the Str function to achieve an explicit formatting of decimal numbers.


The programs works fine.

Thanks for helping me to solve the referred problem.

Orlando

 

[Chihiro]

Thanks for the update. Glad you got it solved.