Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009

    Unanswered: Run a Between query of dollar amounts from one List Box

    Is there a way to run a query off of a List Box that allows you to choose between two dollar amounts? For example my List Box shows Revenue of $4,000; $19,000; $25,000; $61,000, $115,000; $240,000; $478,000; $818,000. I want to be able to select two of these amounts and run a query based off of them. Is this possible?

  2. #2
    Join Date
    Aug 2009
    Up Nort' Wi
    (OK OK it's friday, I didn't see the "Between" in the title)

    Assign each of the selected items to a variable from the multiselect list box.

    The query structure would look something like this:

    SQLStr = "SELECT * from TableName WHERE DollarAmount BETWEEN " &Variable1 &" AND " &Variable2 &";"

    I'll try to get a for loop built tonight to iterate through the listbox to assign values to each variable, at this point I'm hoping all you needed was the query structure (which is assembled for VBA atm).

    Sam, yay! Time to go home and pickle my brain
    Last edited by SCrandall; 09-18-09 at 18:03.
    Good, fast, cheap...Pick 2.

  3. #3
    Join Date
    Dec 2004
    Madison, WI
    Yes but I prefer to do it with 2 listboxes and in the AfterUpdate event of both list boxes, I add this code:
    Dim Dollar1 as currency
    Dollar1 = me!ListBoxName1
    If isnull(Dollar1) then exit sub
    Dim Dollar2 as currency
    Dollar2 = me!ListBoxName2
    if isnull(Dollar2) then exit sub

    Myquerysubform is simply a subform where I've set the sourceobject with a query name versus a subform name (ie. sourceobject = "query" & = & "myqueryName")
    (You could also simply open the query versus desiging a subform and setting a sourceobject to the query name. I like to do it this way because I get an excel type of navigation through the query doing it this way.)

    You're MyQueryName query then has criteria in it under the Dollar field such as : "Select * from myTable where Dollar > $" & Dollar1 & "$ and Dollar < $" & Dollar2 & "$"
    "Select * from myTable where Dollar > #" & Dollar1 & "# and Dollar < #" & Dollar2 & "#"
    (I couldn't remember if $ was valid)
    Last edited by pkstormy; 09-19-09 at 03:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts