Limit items in a combo box

[kdave]

Hello All,

I am trying to limit the items in a combo box on a form based on what has been selected in another combo box. I have this part working using some code in the AfterUpdate. What I'm now trying to do is limit the items further by only show items that have a 1 or 2 immediately after the decimal point.

For example, I have sections labeled 1 through 11. If I select section 1, I only want to see sub-sections 1.1a, 1.1b, etc. There are also sub-sections labeled 1.2a. I don't want to see those yet. (I have 2 forms, one will show sub section 1.1a, et al and the other will show the 1.2's).

The line of code that makes the SQL statement to limit the sub-sections is:

sCategory = "SELECT [sub_sections].[id], " & _
                " [sub_sections].[sub-section_name] " & _
                "FROM sub_sections " & _
                "WHERE [section] = """ & Me.Section_Num.Value & """"

What do I need to add to limit the sub-sections to 1 or 2 as needed?

Thanks, in advanced, for your help!

 

[Micron]

I don't know exactly how to reference your sub values since that part of your post is not clear to me. I would say you need to use the Left and Instr functions OR Mid and Instr, and Dim a variable for the section (svNumVal). Assuming the sub values are part of the section value (e.g. 11.1a, not that you're gettting 11 from one place and the .1a from somewhere else) I'm thinking it would be something like

WHERE Left(Instr(1,svNumVal,".")+1) = your sub value
This should get everything where the first character after the . = 1 or whatever the sub value is. You can replace 'your sub value' with a reference or actual number. If you need to reference more than just one character after the ., then you'd need the Mid function to return more than one value. Check this site for more info on how these functions work so that you can determine if you need to worry about nulls or empty strings.

MS Access: Functions - Listed by Category

 

[kdave]

Thanks, Micron.

I know how to use left, mid, and instr. In fact, I can get the part that I want; it's just putting it the SQL that I need help with. When I run a query using absolute values, I get a HAVING line. That's where I get stuck.

Here's how it is laid out:

Section	Sub-Section	Category
1	1.1a	1
1	1.1a	2

The category is an ID number for another table. This filters based on the sub-section and works just fine. The sub-section filters when I choose a section perfectly, but I see 1.1a, 1.1b, etc. AND 1.2a, 1.2b. In this table, I only want to see all the ones that begin with 1.1. Does that clarify things a bit?

 

[Micron]

Post what you have that works with actual values and I'll see what I can do with it using references, as long as I know your table/field and/or form/control names. If you've got HAVING in your sql statement, you must be using a Totals query. You may not be using any aggregate functions in it, but you must at least be grouping with it. The criteria can be applied to a grouping (HAVING) but it is more efficient to apply it to the table/domain (WHERE) if you are using groupin. You can do this by adding fields a second time and putting the criteria in those query fields. You can uncheck these so they do not display if need be.

If there's no performance issues, you can treat HAVING the same as WHERE as far as the syntax goes and you won't need to add query fields more than once. So if you can do this with WHERE because you know how to use those functions, you should be OK with HAVING as well.

 

[kdave]

When I create a stand-alone query for what I'm looking for, this is the SQL I get:

SELECT Sections.ID, Sub_Sections.ID
FROM Sections INNER JOIN Sub_Sections ON Sections.ID = Sub_Sections.Section
WHERE (((Sections.ID)="1") AND (Mid([Sub_Sections].[id],InStr([sub_sections].[id],".")+1,1)="1"));

What I'm doing on my form is showing items in one combo box based on the items in another combo box. I have tried using the AND in my WHERE statement and I get an error about it being too complex.

The code that I have in the after update event of my Section combo box is :

Private Sub Section_Num_AfterUpdate()
Dim sCategory As String

sCategory = "SELECT [sub_sections].[id], " & _
                " [sub_sections].[sub-section_name] " & _
                "FROM sub_sections " & _
                "WHERE [section] = """ & Me.Section_Num.Value & """"
Me.Sub_Section.RowSource = sCategory
Me.Sub_Section.Requery
End Sub

If you can help me with the logic, I can figure out where my actual items need to go.

Again, when I choose a section number, the sub-sections filter for that section. I need to further filter them based on a 1 or 2 after the "."

 

[Micron]

Your logic looks fine, but before I delve into it, there is one thing I noticed that can generate the error you're getting. It's the minus sign in your field name sub-section_name. Let me know if that's just a typo or if not, if it's the cause.

 

[Micron]

BTW, I don't know exactly what your table structures are, but I think that another problem would be that your expression needs to use -1, not +1. If you have 2.1 in the related table, you will get sub sections for 2.1, 3.1, etc. when you choose 1 in the first combo because you're looking at the 1's that follow the "."

In that case, if you use -1, you will navigate to the left of the '.' and only get 1.1; 1.2; 1.3 etc. if you correctly use the value of the first updated combo (1 or 2 or 3 as the case may be).