Concatenate SQL in Access VBA

0
votes

I'm trying to run an after update query in a field on my form to populate another ComboBox in the form with a concatenated value. I can make the query work in SQL itself, but am having trouble getting the syntax right in VBA. If anyone has any advice I would greatly appreciate it. The Query is below.

[Prod_List].RowSourceType = "Table/Query"
[Prod_List].RowSource = "SELECT ProductID, Make '" & " - " & "' Model '" " - " & "' ProductNum AS product FROM dbo_ProductInfo " & _
"WHERE ProductID = "SELECT ProductID from dbo_ProductInfo " & _
                 "WHERE Make = '" & Make.Value & "'""
1
ms-accessvba
You are missing an ampersand. A simple typo is not a suitable topic for stackoverflow. Actually, there's an additional quote introduced as well, but still not suitable for SO. (Are you sure it works in SQL?) – Andy G
@AndyG If you think that typos are not suitable for stackoverflow, you are completely missing the point of stackoverflow. – Jeffrey
@Jeffrey You are wrong. When you have sufficient reputation to submit close votes you will discover that simple typographical errors are a legitimate reason to suggest closing topics, as they have limited value to future readers. They just add clutter. In this instance, though, it proved to be more than just a typo. – Andy G
@AndyG you are correct, typos are a sufficient reason to CLOSE a topic, but that doesn't exclude them from being a suitable question on this sight. A typo question follows all the rules for stackoverflow.com/help/on-topic if asked correctly and doesn't violate anything in stackoverflow.com/help/dont-ask Most of the time the OP doesn't know they made a typo, hence the question they are asking. By telling someone their question is invalid, you are discouraging them from asking more questions and getting help to improve their coding skills, i.e. the purpose of this site. – Jeffrey
@Jeffrey Pointing out a typo, and explaining that such a topic is not suitable I do not consider as strong as saying the question is invalid. Also, in this particular case, I suspected that the OP was aware that the issue and question might just be about correctly constructing the expression - fixing typos. In most other cases I would just comment to point out the typo and use the close reason. – Andy G

1 Answers

1
votes

Try to reduce it a bit:

[Prod_List].RowSource = "SELECT ProductID, Make & '" - "' & Model & '" - "' & ProductNum AS Product FROM dbo_ProductInfo " & _
"WHERE ProductID IN (SELECT ProductID from dbo_ProductInfo " & _
                    "WHERE Make = '" & Make.Value & "')"

It may only work with IN, as the query may not know if more than one record could be returned from the subquery. Using IN, it can be any count, though it probably always will be only one.