Hello world:
I have a ListBox that I fill as a ValueList from a SQL query. The SQL looks
like:
SELECT Format(COST,"$# ,000") As ItemCost FROM tblPricing ...
I put semicolons between each entry before I load it into the ListBox.
However, if my entry has a comma in it ($2,500 for example) Access adds
another column, thereby messing up the ListBox display. It shows as $2 and
then 500.
Why is Access using a comma as an list separator, when it is only supposed
to use semicolons? Is there any way to escape the comma, and have it be
accepted as just another character in the ListBox display?
Thanks in advance for any help offered.
Alan 4 10373
Alan Lane wrote: Hello world:
I have a ListBox that I fill as a ValueList from a SQL query. The SQL looks like:
SELECT Format(COST,"$# ,000") As ItemCost FROM tblPricing ...
I put semicolons between each entry before I load it into the ListBox. However, if my entry has a comma in it ($2,500 for example) Access adds another column, thereby messing up the ListBox display. It shows as $2 and then 500.
Why is Access using a comma as an list separator, when it is only supposed to use semicolons? Is there any way to escape the comma, and have it be accepted as just another character in the ListBox display?
Trying enclosing each item in quotes.
Function escapeListItem( ByVal s As String) As String
escapeListItem = Chr$(34) & s & Chr$(34)
End Function
Access.Version > 97 = .AddItem (escapeListItem (rs!ItemCost))
Alan Lane wrote: I put semicolons between each entry before I load it into the ListBox. However, if my entry has a comma in it ($2,500 for example) Access adds another column, thereby messing up the ListBox display. It shows as $2 and then 500.
Commas and semi-colons are used. In cases like this, I delimit my
values with double quotes. So, for example (air code), using DAO::
Sub sPopulateList
dim dbs as DAO.database
dim rst as DAO.recordset
dim strS as string
dim strList as string 'value list
set dbs = access.currentd b
'I'm not using any where clause below.
'Note the double quotes in the format function
strs = "SELECT Format(COST,""$ #,000"") As ItemCost FROM tblPricing"
set rst = dbs.openrecords et(strs, dbopensnapshot)
with rst
if .eof then
'slap in some message for no records found
else
.movefirst
strlist = ""
do while .eof = false
if strList <> "" then strlist = strList & ";"
'Chr(34) is the ascii code for "
strList = chr(34) & .fields!ItemCos t & Chr(34)
.movenext
loop
end if
end with
'populate the value list
me.lstMyListBox .Rowsource = strList
Exit_Proc:
rst.close
set rst = nothing
dbs.close
set dbs = nothing
End Sub
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Hello Tim and rkc:
Thanks for the help from both of you. The double-quotes idea didn't work
for me, unfortunately. I took another part of both your suggestions,
though, and used "Chr(34)" before and after the ItemCost, when entering it
into the ListBox's ValueList. That worked like a charm!
Alan
"Tim Marshall" <TI****@PurpleP andaChasers.Moe rtherium> wrote in message
news:d9******** **@coranto.ucs. mun.ca... Alan Lane wrote:
I put semicolons between each entry before I load it into the ListBox. However, if my entry has a comma in it ($2,500 for example) Access adds another column, thereby messing up the ListBox display. It shows as $2 and then 500.
Commas and semi-colons are used. In cases like this, I delimit my values with double quotes. So, for example (air code), using DAO::
Sub sPopulateList
dim dbs as DAO.database dim rst as DAO.recordset dim strS as string dim strList as string 'value list
set dbs = access.currentd b
'I'm not using any where clause below. 'Note the double quotes in the format function
strs = "SELECT Format(COST,""$ #,000"") As ItemCost FROM tblPricing"
set rst = dbs.openrecords et(strs, dbopensnapshot)
with rst
if .eof then
'slap in some message for no records found
else
.movefirst
strlist = ""
do while .eof = false
if strList <> "" then strlist = strList & ";"
'Chr(34) is the ascii code for "
strList = chr(34) & .fields!ItemCos t & Chr(34)
.movenext
loop
end if
end with
'populate the value list
me.lstMyListBox .Rowsource = strList
Exit_Proc:
rst.close
set rst = nothing
dbs.close
set dbs = nothing
End Sub -- Tim http://www.ucs.mun.ca/~tmarshal/ ^o< /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake /^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Alan Lane wrote: Hello Tim and rkc:
Thanks for the help from both of you. The double-quotes idea didn't work for me, unfortunately. I took another part of both your suggestions, though, and used "Chr(34)" before and after the ItemCost, when entering it into the ListBox's ValueList. That worked like a charm!
You actually can put double quotes in using _double_ double quotes (boy,
does that sound confusing). It can be difficult to keep track of and
when I can't be bothered, I find the use of chr(34) easier.
another useful delimiter (though not for list/combo box rowsources) is
chr(35) which is #.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: sheree |
last post by:
I would like to create a query where one of the columns of the queries
comes from a combo list box on a form.
For example, if my table has the following fields:
id
name
interest1
interest2
interest3
my combo box would be a dropdown containing these choices:
|
by: dog |
last post by:
I've seen plenty of articles on this topic but none of them have been
able to solve my problem.
I am working with an Access 97 database on an NT4.0 machine, which has
many Access reports.
I want my users to be able to select a report, click on a command
button on a form, which will then automatically create the report as a
pdf file and save it to the user's machine.
I am using Adobe Acrobat (5.0 I think) and have Adobe Distiller as a
|
by: Grasshopper |
last post by:
Hi,
I am automating Access reports to PDF using PDF Writer 6.0. I've
created a DTS package to run the reports and schedule a job to run this
DTS package. If I PC Anywhere into the server on where the job is
running, the job runs sucessfully, PDF files got generated, everything
is good. If I scheduled the job to run at the time that I am not
logged into the server, Access is not able to print to the printer.
The error is pretty...
|
by: AAJ |
last post by:
Hi all
I have a listbox on a form. If I set its rowsource directly, and the
query in the rowsourse returns no data, then the displayed listbox is
empty (exactly as you would expect)
However....
If I programatically set the rowsource, and the query returns no
records then access completely crashes and switches its self off,
|
by: Alan Lane |
last post by:
Hello world:
I have a Parent form with a combobox, where the user selects a Contract or
Estimate number.
Then, on a subform, I have a listbox that gets filled with the details of
the selected Contract.
The subform listbox shows 21 rows.
That has been fine, but now we are starting to get some Contracts or
Estimates with more than 21 items.
| |
by: Neil |
last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is
upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server
at the same time. We're moving to SQL Server 2005, and, since he already has
licenses for Office Pro 2002, he wants to upgrade to that.
I've been saying that we need to upgrade to Access 2003, not 2002, even if
Office is kept at 2002. We are also looking to do a fair amount of...
|
by: garry.oxnard |
last post by:
Can anyone help me to solve a problem which involves switching from
Access to Excel (then back to Access) programatically please?
I have an Excel template which, on open, also opens an Access database
- containing a list of addresses.
Sequence of events is =
(1) Excel template opens in its default XXX.xls filename.
(2) Code runs to save the spreadsheet as XXX.xls.
(3) User clicks a button to open an Access database containing an
|
by: awmb |
last post by:
Language C#, Version ASP.Net 2.0
Hi
I have a GridView which is bound to a table which contains Restaurant information. In the Edit Row there is a Listbox (ListBox1) in the Food Type column, bound to a table containing the food types.
<EditItemTemplate>
<asp:ListBox ID="ListBox1" runat="server" AppendDataBoundItems="True" DataSourceID="FoodTypes"
DataTextField="FoodType" DataValueField="FoodType"...
|
by: zufie |
last post by:
I have a main form containing a command SEND button that prompts an
email form to pop up.
The email address(es) that are supposed to appear on the email form
are those corresponding to the respective agency record appearing on
the main form at the current time.
Instead, what I get is the email form without the appropriate
information in the textboxes of the email form.
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |