Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
Home > Forums > Access
 
Access Forum

Welcome to the Microsoft Access Forum. Access is by far our most popular topic here at Computer Learning Zone. This forum is for everyone to read but only students can post here. If you'd like to become a student, all you have to do is enroll in one of my courses, which you can do for as little as $1. After you create a logon, you will have full access to the Forums.

Subscribe to this forum and receive an email update whenever new posts are added. You will also be notified by email if any conversations you are involved in receieve new comments. Yes, even non-students can subscribe to update notifications.

NEW: Access Developer Students, make sure you subscribe to the Developer Student Forum.

Click Here to Post a comment and start a new discussion

Please read the FORUM RULES before posting.

 

Sort List Box from Combo Upload Images   Link 
Brent Rinehart 
47 days ago
I have two combo boxes that need to filter a list box (BidList), Filter by Office and Year the year is being filtered from the BidDate from the PropT, which stores the full date. YearCombo is populated from YearT and is unbound, Column 0 is YearID, Column 1 is YearSort and Column 2 is YearNumber. OfficeCombo is populated from OfficeT and is bound to OfficeName, Column 0 is OfficeID and Column 1 is OfficeName.

"SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)= YearCombo.column(3) " & _
    "AND OfficeName = OfficeNameCombo.column(2) " & _
    "ORDER BY " & Forms!PropListF!SortBy

My issue I think is always syntax no matter how much I do this grasping this syntax hopfully will kick in someday.



Kevin Robertson
47 days ago
Hi Brent,

You are referencing your ComboBoxes inside the quotes.
Try this:

"WHERE YEAR(BidDate)=" & YearCombo.column(3) & " " & _
"AND OfficeName =" & OfficeNameCombo.column(2) & " " & _
Richard Rost
47 days ago
Don't forget quotes around OfficeName

"AND OfficeName = """ & OfficeNameCombo.column(2) & """ " & _
Brent Rinehart
47 days ago
Thank You guys for helping me out I really appreciate it. as soon as I got home I attempted what you guys said and its still giving me a compile error. These quotes are the death of me. Bellow is what I tried.
Looks like I missed something I updated doesnt give me compile error now, but when I select either box it doesnt sort I get a blank list box I will need to dig further maybe something else now is the problem.

BidList.RowSource = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)=YearCombo.column(3)&"" & _
    "AND OfficeName= """ & OfficeNameCombo.Column(2)& """ "  & _
    "ORDER BY " & Forms!PropListF!SortBy
Adam Schwanz
47 days ago
Ambersands are inside of quotes after the both the words column
Adam Schwanz
47 days ago
Actually maybe I'm lost in all the line breaks too, just make a textbox on the form called status then make
status = bidlist.rowsource
in your code, so you can see what it's actually saying.
Scott Axton
46 days ago
Brent -
I often find that putting it back into the query builder and letting Access help saves me a lot of headaches sometimes.

Another thing I do is make it work first by putting in known data. Then 1 by 1 switch out the known data with the variables.

Something that helps me is to think of the variables as including the &.  So...  & variable &

"WHERE YEAR(BidDate)=YearCombo.column(3)&"" & _  
      above is missing the & before YearCombo.
so & YearCombo.column(3) &

Something else that is helpful for me is to set the sql string to a variable and using Richards StatusBox or even a MsgBox look at what the sql is converting to.  Like this:

     Dim SQL As String

     SQL = "SELECT BidID, JobName, JobCity, EnteredDate ..."    ' Enter the rest of the SQL string here.
    
     MsgBox SQL
  
   ' Once You have it working set the RowSource and remark out the MsgBox

    ' BidList.RowSource = SQL
Scott Axton
46 days ago
You also need ending quotes after  "ORDER BY " & Forms!PropListF!SortBy
so   "ORDER BY " & Forms!PropListF!SortBy & ""

Try this (I think it works):

SQL = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
          "FROM PropT " & _
          "WHERE YEAR(BidDate)= " & YearCombo.Column(3) & " " & _
          "AND OfficeName= """ & OfficeNameCombo.Column(2) & """ " & _
          "ORDER BY " & Forms!PropListF!SortBy & ""

.
Alex Hedley
46 days ago
Just to complicate things I usually use Chr(34), the double quote character " instead of double double quotes as sometimes it's easier to see where they should start and end.
Richard Rost
46 days ago
Scott's SQL looks good. It's syntactically correct. If the results don't work you may need to cast your year value to an integer.

"WHERE YEAR(BidDate)= " & CInt(YearCombo.Column(3)) & " " & _

But that shouldn't be necessary. And you don't need the little "" at the very end. :)
Scott Axton
46 days ago
FYI -
If you're interested in learning more about the StatusBox that Adam and I spoke about Richard covers it here:   Customer Contact Template
Brent Rinehart
44 days ago
Thank You guys for all the input, one big issue I had was I put list values into the table design (I think this was a no no) no I have it all working from a table for the combo boxes values. Looks like I got it working except for Null values in the OfficeNameCombo If they select a year and show all offices  it doesnt work but if they select a year and a particular office it works. I think al I need now is how to included all value in OfficeName including Null Values

BidList.RowSource = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)=" & YearCombo.Column(2) & " " & _
    "AND OfficeName =" & OfficeNameCombo.Column(0) & _
    " ORDER BY " & Forms!PropListF!SortBy
Adam Schwanz
44 days ago
You can make it give null's a value with the NZ function. NZ Function
Brent Rinehart
44 days ago
I tried the NZ if I am using right but still when I select a year and select all offices I get nothing.

BidList.RowSource = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)=" & YearCombo.Column(2) & " " & _
    "AND OfficeName =" & Nz(OfficeNameCombo.Column(0)) & _
    " ORDER BY " & Forms!PropListF!SortBy


I create a status box and the SQL shows this, It looks right to me?

SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName FROM PropT WHERE YEAR(BidDate)=2020 AND OfficeName =0 ORDER BY EnteredDate DESC
Alex Hedley
44 days ago
=NZ( value, valueIfNull )
Brent Rinehart
44 days ago
HI Alex I tried Nz(OfficeNameCombo.Column(0)) with no luck I tried giving a a value if null, still no luck
Nz(OfficeNameCombo.Column(0), 0)
Adam Schwanz
44 days ago
what about

If IsNull(OfficeNameCombo) Then
  'exit sub or msgbox, do something or do your same code with a where clause that doesn't include the officename criteria
Else
BidList.RowSource = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)=" & YearCombo.Column(2) & " " & _
    "AND OfficeName =" & OfficeNameCombo.Column(0) & _
    " ORDER BY " & Forms!PropListF!SortBy
End If
Kevin Robertson
44 days ago
Have you tried using an If block?

   If Not IsNull(ComboBoxName) then
      RowSource if value...
   Else
      RowSource if no value...
   End if
Kevin Robertson
44 days ago
And Adam got there first. Lol :)
Brent Rinehart
44 days ago
I think we got it guys thank you it seems to be working, I was going to pull the rest of my hair out.  
If IsNull(OfficeNameCombo) Or OfficeNameCombo = 0 Then
   BidList.RowSource = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)=" & YearCombo.Column(2) & " " & _
    " ORDER BY " & Forms!PropListF!SortBy
Else
    RequeryBidList
End If
Add a Reply

Show All Comments

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

9/19/2021Project Management
9/17/2021Overlapping Windows
9/16/2021Inventory
9/10/2021Benefits of Access
9/10/2021Access on a Phone
9/5/2021Prevent Deletion
9/4/2021Booklet Template
9/4/2021Inspections Template
8/29/2021What is Microsoft Access?
8/27/2021Union Query
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: microsoft access forum  PermaLink