By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 IT Pros & Developers. It's quick & easy.

Issue changing combobox row source that includes Concat'd columns in VBA

P: 5
I have an interesting question that has stumped me for a little while, so I was hoping there would be an easy answer that I'm over looking.

I have a form that has a series of three comboboxes that ideally would filter each other.

Without getting into to much detail (unless more detail is desired), I have a date combobox with a Month, Year, an unbound Concat of the Month and Year, VendorID, OrderID Columns. The only visible column is the Concated Month/Year.

By default when the form loads It will show all dates for all orders.
I want to be able to manipulate it so that when the user selects a vendor in the other combo box it will filter it by VendorID. However the catch being I want the value in the combobox to be "Month Year".

For Reference- the ID numbers are numbers. The Month is a "Short Text"/String, the Year is a Number.
(I essentially did it this way because the design isn't actually Month/Year, its an internal calendaring system that is essentially Q1, Q2, Q3, etc for month, and than a Year).

So I set up the combobox with this SQL using Access' builtin Query builder:
Expand|Select|Wrap|Line Numbers
  1. SELECT Order.Order_Month, Order.Order_Year, [Order_Month] & " " & [Order_Year] AS [OrderDate], Vendor.Vendor_ID, Order.Order_ID FROM Order INNER JOIN Vendor ON Order.Vendor_ID = Vendor.Vendor_ID;



In VBA I'm adding the WHERE clause, adding the entire thing to the row source and requerying:
Expand|Select|Wrap|Line Numbers
  1. SELECT Order.Order_Month, Order.Order_Year, [Order_Month] & " " & [Order_Year] AS [OrderDate], Vendor.Vendor_ID, Order.Order_ID FROM Order INNER JOIN Vendor ON Order.Vendor_ID = Vendor.Vendor_ID
  2. WHERE Vendor.Vendor_ID = *value from another combobox*

However its giving me a blank recordset.
I've managed to figure out that no matter how I setup


Expand|Select|Wrap|Line Numbers
  1. [Order_Month] & " " & [Order_Year] AS [OrderDate]
that's what is causing the issue with the SQL query.

If I take that out, the query works fine, but obviously will only show the month OR the year in the combobox when a value is selected. It doesn't show both, which is what I was looking to do.

I had seen some information online about doing another subquery or a UNION query, however I was hoping for something a bit simpler.
My working theory is that it may have something to do with concatenating a number to a string.

Thank you!
Sep 29 '17 #1

✓ answered by NeoPa

Hi Castelan.

I've read through this a couple of times and it makes my head hurt. Trying to work out what you're asking for is a struggle for me - and bear in mind I'm somewhat experienced at this by now. Nevertheless, I decided to persevere with it as you've clearly gone to some effort to express a complicated situation as well as you can. Full marks for that.

However, you seem to be saying the WHERE clause is not doing what you expect it to, yet this seems to be the one bit of information that you haven't included in detail.

I can't be sure I understand well enough to know this is what your question's about, but if it is then that's the info we most need to see. The INNER JOIN info is also critical so well done for including that.

In case it helps, I have no idea how your second post relates to the question. However, as you've mentioned them I'll include a link here to something on Subqueries (Subqueries in SQL).

PS. Please see your Private Messages as I've written to you about the Spam reference in your edit comment.

Share this Question
Share on Google+
2 Replies


P: 5
I'm still open to suggestion, but I'm also certain that because its an unbound column that I will need to put a subquery into the SQL.

If this turns out to be the only solution than not only will I have to admit that I'm a bit rustier on SQL than I care to admit, but also that Access does a good job hiding some of the complexities of its SQL in it Query builder.
That would mean that the Query builder accepts the syntax:
Expand|Select|Wrap|Line Numbers
  1.  [column_name1]] & "  " & [column_name2]
and takes that and does a hidden subquery it would seem.
Sep 29 '17 #2

NeoPa
Expert Mod 15k+
P: 31,186
Hi Castelan.

I've read through this a couple of times and it makes my head hurt. Trying to work out what you're asking for is a struggle for me - and bear in mind I'm somewhat experienced at this by now. Nevertheless, I decided to persevere with it as you've clearly gone to some effort to express a complicated situation as well as you can. Full marks for that.

However, you seem to be saying the WHERE clause is not doing what you expect it to, yet this seems to be the one bit of information that you haven't included in detail.

I can't be sure I understand well enough to know this is what your question's about, but if it is then that's the info we most need to see. The INNER JOIN info is also critical so well done for including that.

In case it helps, I have no idea how your second post relates to the question. However, as you've mentioned them I'll include a link here to something on Subqueries (Subqueries in SQL).

PS. Please see your Private Messages as I've written to you about the Spam reference in your edit comment.
Sep 30 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.