Trimming Data In Listbox
Oct 26, 2006
Another issue for my contacts database for work. I have a listbox on the edit contacts form that lists all contacts in the database.
The listbox rowsource is
SELECT [ContactID], [LastName] & ", " & [NamePrefix] & " " & [FirstName] & " " & [Business/Organization] FROM tblContacts ORDER BY [LastName] & ", " & [FirstName] & " " & [Business/Organization];
Some of the contacts that are businesses or organizations, do not actually have the first and last name filled in, just the business/organization name. So what happens in the list I get all of the entries that do not have FirstName/LastName at the top of the list, with the Business name following a few spaces. The way it looks is:
, Stop & Shop Supermarkets
But I would like to trim that beginning part if there is no FirstName/LastName so that Stop & Shop Supermarkets gets sorted with the S's, like this:
Stabile, Lisa
Stop & Shop Supermarkets
Stott, Joan
Is there something I need to put in the rowsource to accomplish this? I've searched these forums for an answer, and turned up no results.
Attached, I have a screenshot of the form with the listbox.
Thanks
ScrmingWhisprs
View Replies
ADVERTISEMENT
Apr 15, 2014
I've got a piece of VBA scripting which runs as an event linked to a button on my MS Access form.
I maintain a database of members of staff at my organisation. It's pretty outdated...
I'm basically wanting to pull in their updated data (extracted from on our payroll system) from a spreadsheet, into a form, when clicking a button on a particular person's record.
This is what I have so far.
Code:
start = Forms!frm_main2!txt_start_row.Value - 1
conv = DDEInitiate("EXCEL", "Staff List.xlsx")
cell_employee_number = "R" & start & "C1"
cell_surname = "R" & start & "C2"
[Code] ....
The function "CStr(DDERequest())" converts the cell number into the readable data, however I seem to have whitespace below the value.
What would I need to do to strip out this whitespace? Would I use strtrim? If so, I am unsure of the syntax... how would I incorporate strtrim into the above?
View 7 Replies
View Related
Apr 21, 2006
Hi, I'm new here, so I hope I'm posting this in the correct place. I've searched the forum to see if there are any existing threads that might help me, but I've not found anything that does...
(I think this thread ( http://www.access-programmers.co.uk/forums/showthread.php?t=93444&highlight=Copying+data )may be trying to achieve something similar to me, but I'm a beginner and don't really understand it)
I shall stop waffling! I'm not entirely sure that what I'm trying to achieve is possible, I expect it probably is!
Right, I have a form (frmGroupRegister, which contains exactly the same fields as the table it comes from, tblGroupRegister), which consists of three things:
-GroupDate - The date a group took place on. It is my primary key, as no more than one group occurs on a specific date.
-ParentList (A listbox which contains a query showing the ID number, forename and surname of everyone in a table, tblParentDetails)
-ParentsAttending (A blank listbox)
I would like to place buttons in between the ParentList and ParentsAttending, which would allow users to conduct a 'register' of attendance by copying individual/multiple details from ParentList into ParentsAttending (much like you get when choosing which fields to include in a form when using a wizard for example). I would also like them to be able to remove people from ParentsAttending by using a button in case of accidentally adding the wrong person into the ParentsAttending box.
I'm aware that another, probably simpler way of achieving this would be to use a tick-box system, but I feel that visually, the first method would both look better and demonstrate who is present more clearly.
Any help would be much appreciated, but my Access skills are quite basic and things will probably need to be spelled out for me.
I'm using Access 2000 and Windows XP.
Thanks for your help,
Alice :)
View 1 Replies
View Related
Feb 25, 2007
Good Morning to all
i would like to kindly ask if there is a pre defined function in the VBA SQL of access in which it will trim a certain field programmatically
for example
Old string = Manila-MSE Building - 30.26f
New String = Manila-MSE Building
Thanks for your help
View 4 Replies
View Related
Mar 21, 2007
I have a table that has about 5000 street addresses (ex. 1234 your st.). I want to get the all the characters until the first space. So for (1234 Your St.) I want to get 1234 for W1234 St I want W1234. Is this possible?
View 1 Replies
View Related
Aug 25, 2005
HI, i have a field in a query called [cost_type], these typically contains the values "principle works" or "additional works" how can i set the query up so that it doesn't return the "works" part of the record? Could i just return the first 10 characters of the result?
thanks
View 1 Replies
View Related
Oct 21, 2005
My DB is on SQL Server with an Access adp front end.
SQL uses the Windows NT user name and password for security and also pulls in the user name into a field in every record so we know who has added and modified it.
For one table we are actually using the user name field on the form and in a report for reasons other than security.
The user name is filled in with first the name of the network group, then a slash, then the user name itself. So there are 7 characters I don't really care about.
Is there a way in VBA to remove those first seven characters and only show what comes afterwards? Thanks for the help!
View 3 Replies
View Related
Jun 6, 2007
If I have a DB with several tables containing thousands of records, and most of those records only ever need say 8 characters, does anyone know if changing the field size from default 255 chars to 8 chars will actually benefits the the DB?
Potential benefits I'm thinking may occur are reduced filesize and maybe some speed?
View 3 Replies
View Related
Nov 21, 2005
im new to this so hello every1!!!
umm.. i have a form with a combo box which is linked to a table which gives me customer id numbers. and i have a listbox which i want the names and addresses to appear when i select a cusotmer id number from the combo box
so on the list box i put
" SELECT CustName FROM Customers WHERE CustID=$combo43; " in row source; if i change $combo43 to 0 the name appear but that is fixed and i want it 2 change when i select a cusomer id number from the combo box
can some1 plzz help me, its 4 my college project!
thanxs in advance!
purejoker!
View 1 Replies
View Related
Oct 17, 2006
I have a list box which contains data about projects. It has the proNo and proName. I i want to be to select a project and tranfer it to anohter list box on the form using a command button.
I want to be to do this until i have all the projects I want in the second list box. I want to also be able to trnasfer projects back to orginal list box if i make a mistake.
You see this in some databases where the command buttons have < and > to transfer data back and forth.
How do I do this?
View 5 Replies
View Related
Nov 27, 2013
I have two particlular listboxes in an Access Form the first is named listEmp and the second is listAllocated
The source of the first is a query which is based on previous combo boxes.
How can I move selected or all items from one box to another and back if needed.
I need the items moved to be removed from the original box so the data is only in one or the other box.
Please include any VBA that may be needed.
View 14 Replies
View Related
Mar 17, 2008
i want to allign a listbox column data, associated with a table as right, center & left, is it possible? if possible please help me
thanks in advance
View 2 Replies
View Related
Jun 11, 2006
I have a form that student grades on. I use a listbox to pick the names. I also have a pop up form for me to enter new students that are not in the pop up "query" form. I can go to my form with the grades and hit a refresh button and the listbox is updated. I would like for my data to be requeried and up to date when I close the pop form. Any suggestion.
Thanks
View 3 Replies
View Related
Dec 23, 2006
Hey guys,
I was wondering... is there a way to have a listbox display values associated with a parent form ID? in other words... say I have a customer with invoices associated with their name, I want to display a form for that customer with a listbox (or maybe even subform) containing the invoice IDs associated with their name.
Right now, I've got:
SELECT qryClientData.InvoiceID, qryClientData.InvoiceDate FROM qryClientData ORDER BY [InvoiceDate]; to display information in the listbox. How do I modify this to display ONLY Invoices associated with the Clientname on the parent form?
I hope this makes sense. I can clarify if need be, I am just completely stumped. thanks and happy holidays!
View 7 Replies
View Related
Apr 26, 2006
http://forums.aspfree.com/microsoft-access-help-18/menu-list-values-won-t-pass-to-db-20951.html
Here's a page that shows almost what I am trying to do, but my question is how do I make selecting an item in my listbox pupulate date in multiple field on my form?
For instance, I want to click on a line in a textbox and have the fields "City" "State" and "Zip" all changed on my form, not just "City"
View 4 Replies
View Related
Aug 15, 2013
I have an append query that takes an item (once selected) from a listbox and it appends the contact name to a new table. Is there a way I can make an append query thay takes the ContactID and the ContactName and append it.
The listbox box has column 2 Bound (ContactName) soeven though I have written the query to take the ContactID it still only takes whats in the bound column!
View 1 Replies
View Related
Aug 11, 2014
I have a trouble to save data from Listbox to table. I can select mutiple data from Listbox, but the data cannot be saved on the table.
Here is the detail infor:
FORM name: Attendance
FORM record source: TBL_Attendance
Listbox Name: ARR_Result
table for Listbox to use: TBL_ARR
I want to these multiple records which come from Listbox (data on the TBL_ARR table)to be saved in table TBL_Attendance.
View 8 Replies
View Related
Mar 14, 2013
How can I pass data (an employee's first and last name) to a report ? I captured the employee's name from the listbox, but can't seem to pass it to the report. The desired report will only have the employee's name and records for related fields on the report. The table (contains emloyees' history data), form name, listBox (contains employees' names), and variable (contains the employee's name) are listed below.
Table_Employee_Detail_History
Form_Employee's Reports
Report_Attendance_Report
stremployee (variable
View 14 Replies
View Related
Jun 18, 2014
I have a Listbox named List5 and a search textbox named txtProperty and a table name sms , after i search in textbox the results in listbox . i would link to inport the results in listbox to excel but the code i have export the whole table to excel .
here is my code
''''''''*''''''''*''''''''*''''''''*''''''''*''''' '''*''''''''*''''''''*'
''''''''*''''''''*'''''''' BUTTON 3 ''''''''*''''''''*''''''''*'''''''
''' EXPORT THE LIST TO EXCEL AS List5.XLS ''''''''*''''''''*''
''''''''*''''''''*''''''''*''''''''*''''''''*''''' '''*''''''''*''''''''*'
''''''''*''''''''*''''''''*''''''''*''''''''*''''' '''*''''''''*''''''''*'
Dim outputFileName As String
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String
outputFileName = CurrentProject.Path & "List5.xls"
[Code] ....
View 3 Replies
View Related
Aug 25, 2014
I have two listboxes on a form listbox1 and listbox2. listbox1 is populated by table1 which has 40 fields and 1000 records. listbox2 is populated by table2 which has identical structure to table1(same fields) but has no records.
For both listboxes Multi Select is set to simple. Rowsource is Select * statement from tables. Row Source Type is set to Table/Query.
I would like to select multiple records from listbox1 and add them to listbox2 and table2 or just table2. After selecting the records from listbox1 the user will click a button to add the records to table2. Also need to delete the values selected from table1 on same button click.
The database is saved with a .mdb file extension.
View 2 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
Feb 4, 2014
I've filtered a listbox query between two dates selected by a user through an unbound text, however the listbox only changes after you click it. I've requeried it after someone updates the 2 unbound date textboxes, yet it still doesn't work.
View 1 Replies
View Related
Jul 17, 2014
I have a Access table that stores BatchNo,Scandate,NewBatchNo . As I can't attach Access database here so I have exported data from Access to Excel in Sheet1 to show how the data is stored in Access table.
Now I have to write code in VBA that will check the last NewBatchNo in the table . In the attached workbook its 194389. Now vba code should check the NewBatchNo which are blank before 194389 . Now in the records where NewBatchNo is blank , it should add the corresponding BatchNo,ScanDate in the listbox1 in form1.
e.g Sheet2 in the attached workbook stores Bathcno and Scandate of those records where NewBatchNo is null and before the Last used NewBatchNo which is 194389 in our example.
View 4 Replies
View Related
Jun 5, 2014
The following code gives me runtime error message "couldn't set the list property , Type mismatch".
Private Sub UserForm_Initialize()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws1 As Worksheet
Dim j As Long
j = 0
[Code] ....
View 3 Replies
View Related
Mar 18, 2013
I saw the following code on the forums...
Dim i As Integer, c As String
With Me.ListBox.Column(0)
For i = 0 To Me.ListBox.ListCount - 1
c = c & Me.ListBox.ItemData(i) & ", "
Next
End With
c = Left(c, Len(c) - 2)
This works great!! Now I am trying to concatenate the column 3 of the listbox.
Tried to use the code below (pointing to the second column) but it always returns the first column data.
'Dim i As Integer, d As String
With Me.ListBox.Column(2)
For i = 0 To Me.ListBox.ListCount - 1
d = d & Me.ListBox.ItemData(i) & ", "
Next
End With
d = Left(d, Len(d) - 2)
ItemData does not allow pointing to my 3 column of the listbox.
View 4 Replies
View Related
Aug 22, 2014
Is it possible to load the data in input from by double clicking on data from list box ?
I have single form on which both input form and read-only form is present. as i used visible property to display or not accordingly.
User enters the data from input form. (It has been done)
User go onto read only form where combo box and list box. from drop down values load into list-box. (It has been done)
Now double click on any record then it should re-directed on input form with loading the data in editable mode for updation purpose. ???? (How this step will be done) ?
View 9 Replies
View Related