Combo box rowsource data type question (1 Viewer)

[dcx693]

I posted this question about a week ago, but got no responses. Hopefully I can explain it more clearly and get some of your ideas.

I have two combo boxes on my form. In cboA you choose a field name. Based on that selection, I have code like this which assigns the row source of cboB:
"SELECT DISTINCT " & Me.cboA & " FROM tblInventory ORDER BY " & Me.cboA & ";".

The problem is that the items in cboB are sometimes treated as text, sometimes as numbers. It doesn't seem to matter what the underlying data type of the field in cboA actually is.

Anyone else ever run into this problem? Am I doing something wrong? I have attached a sample database with an example.

 

[jtvcs]

Not sure what you eventual goal is, but playing with your problem, did discover that if you place 'Password' as the Input Mask for the second combo box on your screen, your code will work when you select Comment in the first combo box. This would indicate that Access is not evaluating for both number and/or text entries in Combo 2. Maybe this will light up an idea on how to proceed in your head. HTH

 

[dcx693]

The eventual goal is to take the value chosen from the second combo box and include it in a SQL string so I can display a set of records matching, say "Supplier=1", or "ItemNo=500".

The problem is, when building a SQL string, certain data types need to be properly delimited. If you ask for "ItemNo=500" and the ItemNo field is stored as text in your database, that piece of the SQL string will be incorrect. In this case, the SQL code would need to be "ItemNo= ' 500 ' " (I added spaces to separate out the quote marks).

As you discovered, the values in the combo box are presented as numbers, even if the original data was in text format.