09-18-09, 12:39 #1Registered User
- 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?
09-18-09, 14:09 #2Registered User
- 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.
09-19-09, 03:37 #3Moderator
- 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)