Multi-select Listbox And Date Range
Nov 26, 2014
I recently have ontained some responsibilies from a coworker who has left the company.
I have frmEdgeReport which uses ratio buttons to populate the
multiselect listbox .. this all works.. now i need to add date ranges and
Everything I do seems to screw up what I already have working..
I have added two textboxes - txtStartDate and txtEndDate.
Currently I am getting error when i put the SELECT statement in its own query that says "Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'."
and when I just run my form I am getting an error that says "Error 3142 - Characters found after end of SQL statement"
this is what i have so far :
Private Sub SetReport()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
[Code] .....
View Replies
ADVERTISEMENT
Jun 10, 2015
I am using the selections made of the form to generate a query for the user.
I have a CITIES listbox that is populated with values from a stored query.
I would like to make it multi-select and populate a LOCATIONS list box and a NAMES list box based upon the CITIES that are selected.
I have the locations currently populated from a stored query that reads the City selection from the Form. It looks like this
Code:
SELECT DISTINCT (t_location.LOCATION) AS Expr1
FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION
WHERE (((t_location.CITY)=[Forms]![MasterQueryGenerator]![CityList]));
I also want multi-select so that is you can un-select all and get the results for all cities.
Here is my half thought approach.
Code:
Private Sub CityList_AfterUpdate()
'Dim LocQryStr As String
'Dim r As Integer
'Dim ctl9 As Control
'LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
[Code] ...
I intended to have the variable LocQryStr as the row source but I abandoned the idea of having multi-select when I saw that .Selected(I) never returned true. Its like the values aren't read in this subroutine.
View 5 Replies
View Related
Aug 19, 2005
i have a multiselect listbox in my form.
The multiselectlistbox contains the names of different persons from tblUsers.
it's allready possible to write the id's of the names to another table (tblPresent).
But what I can't manage to do is re-select the values in another multiselect listbox. This multiselectlistbox is located on my editform.
I can display the values using a valuelistbox, but i need to see the non-selected items too..
hope someone can help me out
View 1 Replies
View Related
Jul 19, 2005
hello!
i'm trying to select more than one value in a listbox based on a comma-seperated string in a textbox. it seems to be doing what it's supposed to do, except it's only selecting the last value it loops through. it doesn't seem to remember the others. there's probably an easier way to do this than how i did it, but here is the code:
Code:Dim lst As ListBoxDim lngCount As LongDim strSelection As StringDim strNewSelection As StringDim intLen As IntegerDim lngLen As LongstrNewSelection = ""strSelection = Me.Text19.ValueWhile InStr(1, strSelection, ",") <> 0strSelection = Trim(strSelection)lngLen = Len(strSelection)intLen = InStr(1, strSelection, ",")strNewSelection = Left(strSelection, intLen - 1)Set lst = Me!List0lst.RowSource = lst.RowSourceFor lngCount = 0 To lst.ListCount - 1 If lst.Column(0, lngCount) = strNewSelection Then lst.Selected(lngCount) = True Exit For End IfNext lngCount strSelection = Right(strSelection, lngLen - intLen)Wend
any bright ideas???
thanks,
*j
View 3 Replies
View Related
Jul 27, 2006
Dear All,
I am just starting out in Access Development. I have a database that runs a logistics department. This was working fine until the users wanted a little more tweaking.
The report prints out the vehicles with all corresponding drops. This comes out all on sheet.
Is there a way that I can create a form to filter the report via a multi select listbox and print each drop selected on a seperate sheet of paper.
I am using Access 2003
Many thanks for any help or source code given
View 3 Replies
View Related
Jun 25, 2014
I am having a form that has a listbox, which brings up a lot of records, and the user may choose multiple records, but when user wants to scroll down the page to multi select, he cant do it with the ctrl keyboard button, when he press ctrl the page jumps back up, is there any workaround for that?
View 14 Replies
View Related
Feb 6, 2015
I have two listboxes. One for Activities and the other for Organizations.
I set the Org listbox to be multi-select so I could run a loop on that listbox to join multiple Orgs to a single Activity. That part works well.
To do so, I am using the bound column (which is the pkey value) from the multi select listbox, and on the single select listbox I'm using the ListboxName.value to gather the pkey for the "1" side of this 1:Many series of inserts.
Now... I want to use one of the other-than-bound-column value from the multi-select listbox, but I don't know how. When setting a value I only know of the use of :
Code:
ListBoxOrganizations.ItemData(varItem)
and I don't know how to do the equivalent of
Code:
ListBoxOrganizations.ItemData(varItem's column(2).value)
yes, I just made that up, but I hope you get the point. Meaning, as the loop cruises the ItemsSelected on the multiselect I'd like to use other than the bound column when setting variables.
I've tried using the column property to then cruise to the proper record in the listbox, e.g. :
[code]
txtCollaborationDesc = "Collaborate " & ListBoxOrganizations.Column(2, varItem) & " with " & listboxActivities.Column(2)
[code]
But this seems to return the column(2) value of the first record loaded into the list box, as if the varItem piece is being ignored. I believe the loop is working properly, as when the inserts are happening correctly with the bound column of the multi-select list is correctly present as an Fkey in the resulting child records.
I just can't get any other column's value for some reason.
MS Access 2010 and this is an accdb.
View 3 Replies
View Related
Sep 24, 2014
1. I have created a parameter query in access 2010. This parameter is on only one field.
2. I have created a multi-select list box in a form so that users can select one of more items.
I want to pass the selected items as parameters to the save access query.
View 1 Replies
View Related
Oct 10, 2014
I'm using pbauldy's code to filter a report by a multi-select listbox. The only issue is..how do I include more (7 to be exact) listboxes to the code? It seems the OpenReport vba is only allowing 1 where clause?
Code:
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'add selected values to string
Set ctl = Me.VP_ListBox
[Code] .....
View 7 Replies
View Related
Dec 12, 2012
So I have a list box that lists organizations. I recently changed the list box type to extended multi select. On the same form, I have a button that opens a new form where the user can input contacts for each organization. When the list box was not multi select, the expression [forms]![media]!
[List30] made the default value of one of the fields in my contact form the bound column from the selection in the list box. However, now that the list box is multi select, the contact input form does not seem to be able to get the value from the bound column in the list box. When multi select is turned on, is the bound column stored differently.
To even get the contact input button to work, I had to change the code from:
Private Sub Command40_Click()
On Error GoTo Err_Command40_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Contactsqry"
stLinkCriteria = "[Organization ID]=" & Me.List30
[Code] .....
View 14 Replies
View Related
Jan 30, 2008
All -
For the life of me I can't get the Multiselect Listbox to correctly pass along all of the item selections to a Query which a form is based on.
I've been up and down the forum, and I can't figure out what piece of code to use and how to use it successfully.
I've been able to get a string created using the example posted here (http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47909) and it's in the format of "54,67,89,100" etc.
Public Function
Public Function fMultiSelect(ctlRef As ListBox) As Variant
Dim Criteria As String
Dim i As Variant
' Build criteria string from selected items in list box.
Criteria = ""
For Each i In ctlRef.ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & ","
End If
Criteria = Criteria & Format(ctlRef.ItemData(i), "0000000")
Next i
fMultiSelect = Criteria
gMultiSelect = Criteria
End Function
Call:
Call fMultiSelect(Forms!frmPreSPIPComp!lstProjects)
I now need to pass that string to a Query. Once it's been passed to the query, I can open the report based on it.
Essentially I have a button that will perform the string creation, and I would then like to open a report. I want to base the report off of a query and then have the query use Criteria to dwindle down the report.
Am I missing something here?
The long explanation:
I have a single form that allows for the selection of the report. Once the report is selected, certain fields appear that allow for certain criteria to be selected (ie. Class Name, Multi-Select Class Name, Student Names, Multi select Student Names, Dates, etc.)
Once the report has been selected and the criteria set, a user hits a single button that runs the specific report.
Any better ideas of how to set this up. The reports will ultimately be basing their criteria on what the form has in all of it's unbound fields.
I also have a table that specifies the Display Name, actual Report Name for the button to figure out what report to run.
Bottom Line. I want to use a Listbox to filter a report. If I can use a query to base the report off of even better. I don't want to create the SQL in VBA.
Any ideas? Thanks!
View 5 Replies
View Related
Apr 7, 2013
I have a multi select listbox that I rebuild as I index thru some products in another listbox. This listbox then gives me the ingredients I can use. Works well, but the ghost selections in the listbox stay hilighted even after the rebuild.
I can select and de-select at will if there are actual items in the list box, but the ghosts remain.
Here is the code to clear the listbox
Dim _
ctlSource As Control, _
varSelected As Variant, _
intListCount As Integer
Set ctlSource = Forms.frm_select_UPC_LoinGrade!lstBox_ColdStorage
For intListCount = 0 To ctlSource.ListCount
ctlSource.Selected(intListCount) = False
Next intListCount
Forms.frm_select_UPC_LoinGrade.Refresh
Forms.frm_select_UPC_LoinGrade.Repaint
View 3 Replies
View Related
Jun 15, 2014
I have a multi-select listbox (called PotentialItems) which behaves perfectly unless one of the fields has a null value in it, in which case I get an error 13, "Type mismatch" in the following code. It is valid business-wise for column 9 to be null.
Code:
Dim varItem As Variant
Dim dblTargetPrice As Double
With Me.PotentialItems
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
dblTargetPrice = Nz(.Column(9, varItem), 0) '<----- Error here
End If
Next
End With
View 5 Replies
View Related
Jul 1, 2013
I am currently trying to make a form which will generate a report for the users. Currently the form has 3 combo boxes where users select which fields they want displayed from the table in the report. They also have sorting options next to them (Asc or Desc). I am now trying to put in a multi select list box under each combo box to allow users to filter the report to their liking. Currently I am trying to pull distinct values from the specific field of the table and display them in the listbox for users to select.
I have tried using vba to set me.filter1.rowsource = SELECT DISTINCT me.combo1 FROM EVAP_Database, but this doesnt seem to work. I have also tried a few other codes and still no luck.
Table: EVAP Database
Combo: Combo1
Filter Listbox: Filter1
View 6 Replies
View Related
Aug 22, 2014
I am attempting to filter records using a multi-select listbox, but all records are being returned. Here is my code.
Private Sub btnKeyboxCount_Click()
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] ....
View 1 Replies
View Related
Aug 28, 2007
hi, how do I select my data where the date is beyond the date range specified? For instance, I wan to select all the rows except the rows that are between 3/22/2005 to 10/17/2005. Is it possible to select the query? I tried the condition 'where date < #3/22/2005# and date > #10/17/2005#', but it return no rows at all. Does anyone knows how to do smth lidat??
View 14 Replies
View Related
Dec 12, 2007
Hi all, I need to extract in a query a recent range of records, these records should be all before a certain date and all the following future records example:
if today date is 12/12/2007 I need to extract all records that are dated from the 1st of november and all the records that come after the 12/12/2007.
In the criteria I have set "Date()" what else should I be adding to get the range mentioned above?
Thanks
View 5 Replies
View Related
Apr 23, 2008
Hi folks,
I have a crosstab query that counts the number of records that each employee has.
I need to be able to specify the date range to show though and the query won't let me use:
"Between [Type the beginning date:] And [Type the ending date:]"
I've attached a snapshot of my query design and output.
I've tried everything I could think of!!
View 2 Replies
View Related
Jul 1, 2014
I'm using a form to select a date range. Using the following, and entering start date of 6/1/14 and end date of 7/1/14 I would expect to pull the records with a date of 7/1/14; however it doesn't. I have to enter and end date of 7/2/14 to pull 7/1/14 records.
>=[Forms]![F_Transaction_Date_Range]![txtStartDate] And <=[Forms]![F_Transaction_Date_Range]![txtEndDate]
View 14 Replies
View Related
Nov 30, 2014
I am trying to create a form that will select records by date range. The form contains 2 textboxes, with pop-up calendars to select dates. I managed to come up with this code, however, it's not working. Whenever I run the code, my database just goes blank, as though it's selecting something that is not in the records.
Code:
Private Sub Command16_Click()
Dim Task As String
Dim startDate As Date
Dim endDate As Date
startDate = Me.Text12
endDate = Me.Text14
Task = "SELECT * FROM Final WHERE Final.Timestamp BETWEEN #" & startDate & "# AND #" & endDate & "#;"
Me.RecordSource = Task
End Sub
How does one actually do the date range search? Could it be because my timestamp actually has both date and time, that's why the date search is not working?
View 5 Replies
View Related
Oct 24, 2013
I've been using the following code successfully in Access 2003 & now I need to migrate to Access 2010. The purpose of the code is to use the items that the user selects in the list box to build the criteria of a query. Access 2010 keeps giving me a syntax error when I try to run the query & I don't know why:
My code is:
On Error GoTo Err_Command151_Click
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] .....
The syntax error I get in Access 2010 is:
Syntax Error in query expression 'SELECT * FROM
qryContractListSummarybyDateContract3TYPEBREAK WHERE
qryContractListSummarybyDateContract3TYPEBREAK.Rep ortableName IN('Adbri
Masonry NSW');'
View 12 Replies
View Related
Apr 23, 2015
I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
View 2 Replies
View Related
Aug 28, 2004
Hi,
is there any (reasonably simple) way to select or deselect multiple items from the List Box with individual clicks without using Ctrl key. Eg first click on an item would select it leaving all other items as they are, subsequent click on the already selected item would deselect it etc. I hope this is not too confusing and I would appreciate some help.
Thanks!
View 1 Replies
View Related
Jun 17, 2005
Hello,
I've got this multiple select listbox which writes data into a textbox:
Private Sub List2_AfterUpdate()
Dim Cursisten As String
Dim ctl As Control
Dim Itm As Variant
Set ctl = Me.List2
For Each Itm In ctl.ItemsSelected
If Len(Cursisten) = 0 Then
Cursisten = ctl.ItemData(Itm)
Else
Cursisten = Cursisten & "," & ctl.ItemData(Itm)
End If
Next Itm
Me.txtCursisten = Cursisten
End Sub
And I've got a SELECT ALL button to select all records in the listbox:
Private Sub cmdSelectAll_Click()
On Error GoTo Err_cmdSelectAll_Click
Dim i As Integer
If cmdSelectAll.Caption = "Alles Selecteren" Then
For i = 0 To Me.List2.ListCount
Me.List2.Selected(i) = True
Next i
cmdSelectAll.Caption = "Alles De-Selecteren"
Else
For i = 0 To Me.List2.ListCount
Me.List2.Selected(i) = False
Next i
cmdSelectAll.Caption = "Alles Selecteren"
End If
Exit_cmdSelectAll_Click:
Exit Sub
Err_cmdSelectAll_Click:
MsgBox Err.Description
Resume Exit_cmdSelectAll_Click
End Sub
The only thing is that when I use the SELECT ALL button, the function List2_Afterupdate doesn't work anymore. There must be a simple solution but I just can't figure it out. Can anyone please help me?
Tnx a lot!
View 13 Replies
View Related
Feb 28, 2008
I need to attribute a value to records between a certain range, example
All records that the field "Date" is between 01/04/2007 and 31/03/2008 I need to return a value in a field named "Group" 2008 in my table.
Example:
12/02/2003 = 2003
15/03/2003 = 2003
15/04/2003 = 2004
20/08/2003 = 2004
17/12/2003 = 2004
20/02/2004 = 2004
01/04/2004 = 2005
View 5 Replies
View Related
Oct 22, 2013
I wish to generate a new query consisting rows that I have selected in a multi colum list box. May I know if it is possible to work? I am totally lost now
View 10 Replies
View Related
