Concatenate SQL in Access VBA

Question

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 & "'""

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
– Andy G, Commented Dec 4, 2018 at 16:17
@AndyG If you think that typos are not suitable for stackoverflow, you are completely missing the point of stackoverflow. — Jeffrey
– Jeffrey, Commented Dec 4, 2018 at 17:25
@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
– Andy G, Commented Dec 5, 2018 at 8:27
@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, Commented Dec 5, 2018 at 15:03
@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
– Andy G, Commented Dec 5, 2018 at 15:16

Gustav · Accepted Answer · 2018-12-04 17:09:28Z

1

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.

edited Dec 4, 2018 at 17:09

answered Dec 4, 2018 at 16:24

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

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

3 Comments

Andy G Over a year ago

I think that you should mention the correction to IN.

Gustav Over a year ago

Right @AndyG, forgot that. Thanks.

Jared H Over a year ago

Thank you, I feel dumb for missing the IN, but that's what I get for rushing. This reduced version works much better.

Collectives™ on Stack Overflow

Concatenate SQL in Access VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related