Using commas in "Value List" list boxes

I am trying to add a column that contains a comma to a 'value list' list box. 
I have a patient name (Last, first MI) column and a patient ID column (among 
others). I have my Row Source Type set to Value List and the number of 
columns correct, column widths etc. all correct, but when I try and do this:

lstbox.AddItem "TreatmentId;LastName, FirstName MI;PatientId" 

The help documentation shows that the delimeter should be the ; not comma's, 
however instead of getting the name all in one column, it is being placed in 
two separate columns (because of the comma... it is being seen by Access as a 
delimeter, separating columns). I have tried to escape the comma... to get it 
to be seen as a literal. Ive tried using chr(44) in place of it... all to no 
avail...

is there anyway to use a comma in a 'value list' list box without Access 
splitting the string on the comma?

btw: Im using 2007 if it makes any dif...
0
Utf
7/21/2007 10:20:00 AM
access.forms 6864 articles. 2 followers. Follow

3 Replies
1526 Views

Similar Articles

[PageSpeed] 55

Try enclosing the LastName, FirstName part in single quotes. 

However, are you trying to do this in Access 2007 as I don't believe
the standard List Box control has an AddItem method (perhaps you are
using Visual Basic 2007).

HTH

Peter Hibbs.

On Sat, 21 Jul 2007 03:20:00 -0700, csharppoet
<csharppoet@discussions.microsoft.com> wrote:

>I am trying to add a column that contains a comma to a 'value list' list box. 
>I have a patient name (Last, first MI) column and a patient ID column (among 
>others). I have my Row Source Type set to Value List and the number of 
>columns correct, column widths etc. all correct, but when I try and do this:
>
>lstbox.AddItem "TreatmentId;LastName, FirstName MI;PatientId" 
>
>The help documentation shows that the delimeter should be the ; not comma's, 
>however instead of getting the name all in one column, it is being placed in 
>two separate columns (because of the comma... it is being seen by Access as a 
>delimeter, separating columns). I have tried to escape the comma... to get it 
>to be seen as a literal. Ive tried using chr(44) in place of it... all to no 
>avail...
>
>is there anyway to use a comma in a 'value list' list box without Access 
>splitting the string on the comma?
>
>btw: Im using 2007 if it makes any dif...
0
Peter
7/21/2007 11:40:03 AM
I am 'using' 07 in the sense that I am using it to develop my db, but saving 
it in 2000's  .mdb format. Which brings up a good point: I want to eventually 
move my db to 07's .accdb format, so will my lstbox.AddItem method break when 
I convert to that format?

Thank you Peter, now I know how to include comma's in value list lstboxes, 
it wasn't documented well with the online help from MS.

Best,
Mark

"Peter Hibbs" wrote:

> Try enclosing the LastName, FirstName part in single quotes. 
> 
> However, are you trying to do this in Access 2007 as I don't believe
> the standard List Box control has an AddItem method (perhaps you are
> using Visual Basic 2007).
> 
> HTH
> 
> Peter Hibbs.
> 
> On Sat, 21 Jul 2007 03:20:00 -0700, csharppoet
> <csharppoet@discussions.microsoft.com> wrote:
> 
> >I am trying to add a column that contains a comma to a 'value list' list box. 
> >I have a patient name (Last, first MI) column and a patient ID column (among 
> >others). I have my Row Source Type set to Value List and the number of 
> >columns correct, column widths etc. all correct, but when I try and do this:
> >
> >lstbox.AddItem "TreatmentId;LastName, FirstName MI;PatientId" 
> >
> >The help documentation shows that the delimeter should be the ; not comma's, 
> >however instead of getting the name all in one column, it is being placed in 
> >two separate columns (because of the comma... it is being seen by Access as a 
> >delimeter, separating columns). I have tried to escape the comma... to get it 
> >to be seen as a literal. Ive tried using chr(44) in place of it... all to no 
> >avail...
> >
> >is there anyway to use a comma in a 'value list' list box without Access 
> >splitting the string on the comma?
> >
> >btw: Im using 2007 if it makes any dif...
> 
0
Utf
7/22/2007 4:26:03 PM
Mark,

I have done a bit of research and it seems that Access 2007 now allows
the AddItem method on combo and list boxes, see -
http://msdn2.microsoft.com/en-us/library/bb224614.aspx

As I don't have A2007 I don't know if this procedure will work in the
..accdb format. If you are intending to use the database under Access
2000 then I think I would check whether this is going to work first
before going much further with the design, I would think it unlikely
that it would. You can enter a string in the RowSource property which
would then be compatible with earlier versions (although bear in mind
that in A2000 you are limited to a maximum of 2048 characters).

Perhaps someone who has A2007 and A2000 could pick up this thread and
give a bit more information.

Regards.

Peter.

On Sun, 22 Jul 2007 09:26:03 -0700, csharppoet
<csharppoet@discussions.microsoft.com> wrote:

>I am 'using' 07 in the sense that I am using it to develop my db, but saving 
>it in 2000's  .mdb format. Which brings up a good point: I want to eventually 
>move my db to 07's .accdb format, so will my lstbox.AddItem method break when 
>I convert to that format?
>
>Thank you Peter, now I know how to include comma's in value list lstboxes, 
>it wasn't documented well with the online help from MS.
>
>Best,
>Mark
>
>"Peter Hibbs" wrote:
>
>> Try enclosing the LastName, FirstName part in single quotes. 
>> 
>> However, are you trying to do this in Access 2007 as I don't believe
>> the standard List Box control has an AddItem method (perhaps you are
>> using Visual Basic 2007).
>> 
>> HTH
>> 
>> Peter Hibbs.
>> 
>> On Sat, 21 Jul 2007 03:20:00 -0700, csharppoet
>> <csharppoet@discussions.microsoft.com> wrote:
>> 
>> >I am trying to add a column that contains a comma to a 'value list' list box. 
>> >I have a patient name (Last, first MI) column and a patient ID column (among 
>> >others). I have my Row Source Type set to Value List and the number of 
>> >columns correct, column widths etc. all correct, but when I try and do this:
>> >
>> >lstbox.AddItem "TreatmentId;LastName, FirstName MI;PatientId" 
>> >
>> >The help documentation shows that the delimeter should be the ; not comma's, 
>> >however instead of getting the name all in one column, it is being placed in 
>> >two separate columns (because of the comma... it is being seen by Access as a 
>> >delimeter, separating columns). I have tried to escape the comma... to get it 
>> >to be seen as a literal. Ive tried using chr(44) in place of it... all to no 
>> >avail...
>> >
>> >is there anyway to use a comma in a 'value list' list box without Access 
>> >splitting the string on the comma?
>> >
>> >btw: Im using 2007 if it makes any dif...
>> 
0
Peter
7/22/2007 6:22:26 PM
Reply:

Similar Artilces:

Combo Box Value List/Query Criteria
Hi Folks - I have a combo box with a value list of Option1, Option2 and ALL. I pass this combo box info into a query. Here's a sample of the query criteria: IIf([Forms]![frmSwitchboard]![cboCaseType]="all",Is Null,[Forms]![frmSwitchboard]![cboCaseType]) In other words, if the combo box selection is ALL, then display all records, otherwise use selected option. The above criteria does not work. Any suggestions? Thanks. Michael Michael, Try... (IsNull is an operator, not a value) IIf([Forms]![frmSwitchboard]![cboCaseType]="All", Null,[Forms]![frmSwitchboar...

Value list updates not appearing in form
I have a table with several fields who's display control is combo-box, source is value list, and multiple values are allowed. In one of the fields, when I add a new option to the value list, it automatically appears in the drop-down list in the form. Not so for the other field! When I go back and add more values to the list, they do not appear in the combo box drop-down list in the form. I've been working around it by manually adding the new options to the value list in the form's properties. Why does it work for one field and not the other? I can't see anywhere whe...

Value List needs updating with 600 rows
Hi , I am attempting to get a list of 600 values added into a Value List without having to type them in...I have added them into the MSP_ATTRIBUTE_STRINGS table in a MS ACCESS copy of the project file however when I open the plan back up again in Project, the look up value list has not been refreshed..any ideas?? In Project 2007 you can cut and paste into the lookup table list from excel. That should be easier and safer than mucking around in Access. Isn't 600 values a lot to choose from? The users will have to scroll a lot. -Jack "Steve" <Steve@discussions....

Allow other values other than ones in Value List
Is there a way i can be permitted to add values to a drop down value list of a cell? If the value is not in the list at present i get a "The value you entered is not valid" error. I do not want to add that particular value to the list each time as it may never be used again, and the list would grow beyond the never never.... Any idea's? Corey, if this is from data validation uncheck show error alert on the error alert tab in data validation -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from...

Item value list and snapshot cost report
Hi everyone, Whats the difference between the item value list and the snapshot cost report? My total cost value for a branch shows 17,000 on the snapshot report but 52,000 on the item value report at HQ and branch even after running a 501 and a 190? Surely these should not be different? Thanks The Snapshot Cost canned report should never have summed the Cost column, since that would only be meaningful if each item in the store had quantity of one. The custom report Item Value uses the Extended Cost field to show cost * quantity for each item, which will sum for an accurate total of t...

Sync 2 combo boxes where one of the combo box has a value list
I have a combo box "Multi-Homing Enabled"that is unbound, its set to value list (Yes;No) and table named "No. of LMG" (also unbound.) o If user selects Multi-Homing Enabled asNo, NumOfLMG should equal to 0. o If user selects Multi-Homing Enabled asNo, NumOfLMG should equal to 1. I'm new to access and need help in doign this, can anyone Please help? Your post is confusing... The first combo box has only yes/no as choices? I suggest using a checkbox instead if you only have two choices, saves a mouse click and is just more logical. The second combobox is bound to...

Best Practice Value list or linked table?
I am beginning to wonder if i overcomplicate my database. i am going to add a field to my T_partnumbers for unit of measure (UOM) and can not decide if i should do it like i have in the past and use a combo (on my forms of course) with a row source to a T_UOM with Ea, LB, OZ, Ft Ect. and store the UOMid in my part numbers table or just use a value list in my combo and save the actual UOM abbreviation in my P/N table. What is a good determining factor for which method to use? And What to store in my table LB, OZ, EA, ect. or their respective ID's? I feel linked tables for...

Item Value List vs Item Movement History Report (including cost)
I have been trying to create reports for previous points in time, but I am so confused at this point as to what is what. I've ran an Item Value list, which shows the current value of items, and got an extended cost. Then I ran an Item Movement History Report that includes cost with a filter that begins before the store opened and ends today. The extended cost in this report was very close, but not the same as the extended cost in the Item Value list. Is there something that the Item Movement History Report takes into account that the Item Value list doesn't, or vise versa? Beca...

Report Parameters forms value list set by VBA?
NOTE: I"m a VBA novice I have a set of reports that use a common Report Parameter form. When I first designed them it was convenient as the same set of filters were applicable to all the reports: Example: report for invoices, report for open orders, report for processing orders Filters were: Location, Manager & Employee Now they also want to filter by date range, which won't consistent between reports: IE. Invoices = 30, 60 or 90 days old vs Pending Orders = 10, 15, 30 days old I'd like to just add one more drop down box to the parameter report and have the value list ch...

Extract names from string based on value list
I have a list of productnames and a list of brandnames. I want to supply the productnames with an attribute "Brand". I am currently manually filtering the list of productnames based on "Contains... Brand" and adding the Brand to the product using copy paste, but this takes me too long and I can only match records one brand at a time. Can I make Excell search through the list of productnames and whenever it comes across a brandname within the string of a productname it will add it to the column next to it? One way Assume productnames running in A2 down A...

Using commas in "Value List" list boxes
I am trying to add a column that contains a comma to a 'value list' list box. I have a patient name (Last, first MI) column and a patient ID column (among others). I have my Row Source Type set to Value List and the number of columns correct, column widths etc. all correct, but when I try and do this: lstbox.AddItem "TreatmentId;LastName, FirstName MI;PatientId" The help documentation shows that the delimeter should be the ; not comma's, however instead of getting the name all in one column, it is being placed in two separate columns (because of the comma... it is...

Trying to have this code generate only a numerical value list by rows: 1, 2, 3, ... 10, 11
Originally this was used to create an alpha listing. I use this to create hiearchy and need the code to count upwards from 1 by 1 as high as is needed. (probably at least to three digits). Code being used: lastletter = "1" Cells(2, 16) = lastletter For intalpha = 3 To Cells(65536, 3).End(xlUp).Row If Cells(intalpha, 2) = 0 Then lastletter = Chr(Asc(lastletter) + 1) Cells(intalpha, 16) = lastletter Next intalpha ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software!http://www.ozgrid.com/Services/excel-software-categories.htm ** Your co...

Multi Value list box selects all customer
I have this code as a multiValue listbox but everytime i select which customer i want it select them all, can anyone tell me how i can fix's this thanks Dim varitem As Variant Dim strTempItem As String For Each varitem In Me.lstReportFilter.ItemsSelected strTempItem = strTempItem & " [SalesGroupingField]=" & Me.lstReportFilter.ItemData(varitem) & " Or " Next strTempItem = "(" & Left(strTempItem, Len(strTempItem) - 4) & ")" Have you set the MultiSelect to Simple on the list's property box (it's...

Value list
i need help with making value lists in excel so that i can make my data base consistant with the data i am entering That's not much to go on. What is a value list? And what constitutes making your database consistent with the data you are entering? Post back and explain in detail what you have, what you want to do, and what you want to happen. Some examples are always good. HTH Otto "Maint Girl" <Maint Girl@discussions.microsoft.com> wrote in message news:4EDE3701-B704-4BE8-B449-58662FDCB495@microsoft.com... > i need help with making value lists in ...

Create value list from range
I've got a range of cells containing state abbrev. that I want to list in another range excluding all duplicated states abbrev's. Is there a way to list the non-duplicated values? Thanks for your help... -- mallets123 ------------------------------------------------------------------------ mallets123's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25090 View this thread: http://www.excelforum.com/showthread.php?threadid=506174 Try this: Make sure you have a heading for your list. I'll assume your range is A1:A100 and A1 is STATE. In a blank ...

historical inventory (Item Value List)
Is there any way to get RMS to display the Item Value List on previous dates? I would like to be able to view historical inventory category values. TIA ______________________________________________________ Larry Leveen OlyBikes Bikes, Parts, Repairs & GREAT Customer Service! 124 State Avenue NE Olympia, WA 98501 P: 360-753-7525 F: 360-528-7526 info@olybikes.com www.olybikes.com Featuring free bike advocacy and safety materials! I actually just contacted Microsoft about this myself for a client of mine. They informed me that the inventory lists are real-time only, that there i...

Deleting multiple rows from value list
Is there a way to delete multiple rows in a value list for a Text column? I have over 200 rows out of a value list that are duplicates that I need to delete. I can delete them row by row but I'd like to click on the 1st row and shift click on the last row that needs to deleted. Is this possible? Thanks in advance (from a Project newbie) Yes. Select then click on the cut icon. Try it. -Jack Dahlgren "Val" <Val@discussions.microsoft.com> wrote in message news:6596A51E-9C60-46A4-B9AB-AAB335A92906@microsoft.com... > Is there a way to delete multiple ro...

How to populate a value list
I have a field that is a valuelist. I select the value from a list that I typed in. That is fine. But I need to have those value in the order of selection. Ex : I have in the list the value from a to z. I select f, m, a. I want them to show up in that oder. At best I would like to use the same thing as Access when we define a combobox, we have 2 lists : the available field and the selected fields with 4 buttons in the middle. Can that be done ? Thank you -- eric On Mon, 23 Nov 2009 10:10:01 -0800, ericb <eric@b.com> wrote: >I have a field that is a ...

problem with mulit-column value list combo box
I am trying to read the values of 2 columns of the selected item in a multi-column combo box. cboField1 is the multi-column combo box cboField1.rowsource=3Dr1c1;r1c2;r2c1;r2c2;r3c1;r3c2..... cboField1 Row Source Type=3DValue List cboField1 Column Count=3D2 Me("cboField1").ItemData(2) gives me the value for column #1 in row #2 Me("cboField1").Column(1) gives me the value for column #2 in row #1 I can't find the syntax for getting the value for column #2 in row #2. Thanks for any help! THANKS! David G. On Tue, 24 Nov 2009 21:38:31 -0500, Dav...

Combo Row SOurce Value List Data
Please help, I have changed in the code some text info in the code, which value list is compared by Replace on many form and now I have to change the same text data stored in the value list of combo buttons, which are stored somewhere (hidden etc.) There are many forms afected and I really do not whant to go to each form, look for combo buttons and change value list by hand one by one. I wonder if one can access and replace these data faster somehow. Is there hidden or system table somewhre out there to go to? Thanks for advise. Milan. "Milan Wendl, aaaengineering....

Correcting a typo in large value list
I'm new to Project so this may be simple. I have several project files that the value list for a Text column was incorrectly entered. Is there a way I can add a string prefix to all 200+ values in the list? For example, the data I have in the Text13 column is "1.3.1" and I want "ABC.1.3.1". I've tried to do a formula but I keep getting a recursive error since I need the new value to be in Text13 plus I need to keep the ability to be able to select a new value from the list (in case something changes). If it was Excel, this would be simple becaus...

Create value list from a range of values
I've got a range of cells containing state abbrev. that I want to lis in another range excluding all duplicated states abbrev's. Is there a way to list the non-duplicated values automatically with function? I've tried using the excel Data Filter menu but it seems that needs t be performed manually. I need a function that automatically calculate when there is a change or addition to my list of states. Thanks for your help.. -- mallets12 ----------------------------------------------------------------------- mallets123's Profile: http://www.excelforum.com/member.php?action=...

How to add a new record from List Box to Value List
I'm trying to learning Access by doing - so this question is REALLY basic. I have a field in a table called "CompanyName". The field is a list box and the list comes from another, linked table called Contractors. Contractors is just a list of names of contracting companies that we deal with. When I am in my first table and I am adding a new record, when I get to the "CompanyName" field and the contracting company I need to enter is not already in the list I type it in. How do I get this new contracting company that I just typed to then be automatically added to the ...

Report-Item value list with stores
Hi guys, On the standard item value list in headquarters, I need to add the Stores so I can see which store has what stock value and quantity. Does anyone know the column name I need to add to the report? Thanks in advance Richard Use the Snapshot Item Value List. This is the one that contains the store values. Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ---------------------------------------------- Please DO NOT respond to me directly but post all responses here in the newsgroup so that all can share the information. RichyRich wrote: > Hi guy...