How can I put a comma in a ListBox.AddItem string? Must be easy!

[julietbrown]

I'm trying to populate a listbox in code. The code that does this is ...

strToAdd = rstSkeleton!SkeletonString & ": " & Me.EventTitle & "; " & dateStr
Me.NewMailItems.AddItem strToAdd

The bits of string are, for example ...

rstSkeleton!SkeletonString = "Tickets"
Me.EventTitle = "Garden Party"
dateStr = Format(EventDate, "d m yy")

This does what I expected, and adds the following to the list box ...

"Tickets: Garden Party; 10 5 09"

But I don't want a semicolon in there after Party, I want a COMMA! Access won't let me have a comma within a string to assign to a row of a list box. It just ignores everything after the comma. I've tried

& "',' " &

i.e. putting the comma in single quotes (which you can barely see here!) but it makes no difference. Is there any way of getting a comma in there?

(I won't ask you about how to get 10/5/09 instead of 10 5 09 because it's too pathetic and I will manage to find out by myself!)

[julietbrown]

WHOOPS! HOLD EVERYTHING, EXPERTS!
Suddenly it doesn't work with the semi-colon either. Something weird is happening ... Sorry. Don't waste your superior brains on this until I get back to you with more (accurate) information about the problem.

[julietbrown]

OK, let's have another go ...

With a comma, it never works. With a semi-colon it seems to decide on the fly whether to work or not (which is very worrying). With a colon, it's fine.
I have changed strToAdd to the following ...

strToAdd = rstSkeleton!SkeletonString & ": " & Me.EventTitle & " (" & dateStr & ")"

... and this produces, e.g

"Tickets: Garden Party (10/5/09)"

which is acceptable (and, yes, I solved the "/" problem!)

But I'm still curious about how to get a comma into such a string???

[missinglinq]

This worked for me:

"'Tickets: Garden Party; 10 5 09'"

Note that this is

Double Quotes
Single Quotes
Tickets: Garden Party; 10 5 09
Single Quotes
Double Quotes

Linq ;0)>

[NeoPa]

I think the comma is seen as a column separator. Thus it treats it as the end of the data for a particular column.

To understand more precisely we'd benefit from more info about your NewMailItems object.

[julietbrown]

Yes, I think that's true. The thing that surprises me is that I can't somehow 'hide it' in the string so that it simply views it as a character in the string rather than a separator. Having sorted that in my brain I couldn't imagine why it even sometimes seemed to let me get away with a semi-colon, as that's what you put between string literals in setting up a value-list in the 'Properties' sheet.

Don't spend any more brain on this unless you can't bear to let it go! I decided an hour or so ago the whole thing I was trying to do was bonkers: as the result needed to end up in a field in a table in the end anyway, I have written code to put it in there FIRST, and then set the RowSource for the listbox to a selection from that table: neater, and no problem having as many commas as you want.

[missinglinq]

Have you tried the answer I gave you in Post # 4? As I said, it works consistently for me.

Linq ;0)>

[julietbrown]

Qs and As come so thick and fast I think your answer just crossed over with my "let's give up" in cyberspace!

Your solution looks good, thank you. Even if I've changed the implementation (as per my last post) I keep all these little hints and tips on a file just in case I need them later. But I'm not sure how to incorporate these single quotes into a string made up of var names, vis

strToAdd = rstSkeleton!SkeletonString & ": " & Me.EventTitle & " (" & dateStr & ")"

... as I already tried

strToAdd = rstSkeleton!SkeletonString & ": " & Me.EventTitle & " ','" & dateStr 

with no success. Where should I put the single quotes to make it work?

[julietbrown]

Ooooh! We just crossed over again ... I'm going to wait an hour now before "interrupting" again!!!

[NeoPa]

If you want the SQL string 'Tickets: Garden Party; 10/5/09', then something like :

strToAdd = "'" & rstSkeleton!SkeletonString & ": " & Me.EventTitle & "; " & dateStr & "'"

I'm assuming you have the relevant values in each of these locations. Personally, I'd never use 2-digit years except under extreme provocation, but I'm guessing dateStr is already prepared prior to this line executing.

[missinglinq]

Personally, I'd never use 2-digit years except under extreme provocation

Oh, come on, NeoPa! The Millennium Bug was going to send the world back to the Dark Ages, wasn't? But we're all still here, aren't we, with our lights and computers and banking systems?

What concerns me are these people who insist on writing dates as

Day - Month -Year

instead of the proper, Microsoft-Approved way of

Month - Day - Year

;0)>

[NeoPa]

I didn't expect to have to explain this to someone of your experience and awareness of IT Linq. The Millenium Bug was only not an issue if you simply listened to the media - principally those whose intellect was too limited to appreciate the complexities of the issue. The reason planes didn't fall from the sky was principally because of the billions of dollars spent, principally in the First World, to ensure that all the stupid (yes - it was stupid even in the late seventies when people tried to convince me I shouldn't worry about the problem) date issues introduced into programs, libraries & OS code, were fixed. Obviously it wasn't only air safety concerned, but I know many people who made a good living for a time fixing the very many real issues caused by this irresponsible attitude. I personally had much work to do to ensure many PCs didn't stop working properly over that period. I did laugh when the media reported it as a storm in a teapot. Completely unaware, it seems, of all the effort put in to avoid a catastrophe. I can assure you from personal experience that had the fuss not been made that time would have been extremely uncomfortable for many many people (and businesses) across the world. Nowadays of course, everyone just seems to remember what they were told by the media, none of whose representatives, as far as I can remember from that period, ever really understood the why of the issue in the first place.

I know you were being tongue-in-cheek Linq ;) It's just that I see so many references to it as if the media reporting were remotely accurate, that I don't like to miss an opportunity to set the record straight. I was working for Barclays Capital at that time. If they got a pound back for every time someone has commented that there never was a real problem they'd take in millions, but still be well out of pocket on the deal.

As far as Microsoft-Approved is concerned, I think you're being unfair to them. I know they're fundamentally American (They have branches all over the world of course), but I'm not aware that they've ever suggested using date formats inappropriate for the medium in which they are being used :D

In my view, it is always better to be unambiguous when using dates. With ever increasing globalisation, the world is shrinking, and, just like the 2-digit years, there will come a time shortly when dd/mm/yyyy and mm/dd/yyyy will be ambiguous and will lead to problems. I'd support a format which is consistent in that it goes from largest to smallest - yyyy/mm/dd. That would avoid most of our current problems. It would even sort properly. Something that neither the European nor American formats handle well atm.

[julietbrown]

Wow! Well, thanks, Missinglinq ... the original problem has been solved by you. As for the "dateStr" that prompted such howls from NeoPa, this was only a DISPLAY issue for the row source of a combo box!! The underlying dateTime field not only has 4 digit years, it has hours/mins/secs as well!

Incidentally, I was in Hong Kong 1998 to 2000. You may be entertained to know that the ever-entrepreneural HK Chinese were selling pills on the street which were claimed to stop "you" getting the Millenium Bug, and also little watch batteries for sale, claiming that if you put them into your computer (somewhere/somehow) they would stop it getting the bug. On New Year's Eve, 1999, we were on a boat in Victoria Harbour, counting down to midnight on a huge digital clock. When it hit 2000 there was a perceptible few seconds of total silence among the huge crowd, just holding our breaths in case a missile came tearing out of China ... before the cheering and jollities started. I can tell you WE took it seriously and were truly glad all the work had been done.

[topher23]

I couldn't resist jumping in to point out that ISO standard 8601 for date format is almost precisely what NeoPa recommended: yyyy-mm-dd. Search ISO 8601 on Wikipedia. ;)

PS I also hate that I have to educate people when they poke fun of the "hype" of the millenium bug.

[NeoPa]

@julietbrown
I'm still trying to decide if you're being serious or just pulling my chain Juliet. I'll assume the latter, as the former would be somewhat overkill for a simple suggestion. I'll resist the temptation for further howling, if I'm allowed to explain that I do appreciate the difference between display and storage. You're perfectly at liberty to ignore my advice of course, but I would recommend using 4 digits in all cases where space allows.

[NeoPa]

@topher23
Thanks for that Topher. The more complicated issue is the take-up of the standard. Logic and usability is not the only issue when people across the world already have their own, generally workable, standards that they don't want to move away from. Shame, but you can understand why it's unlikely to happen any time soon.

[julietbrown]

I'm getting confused now, because I don't know who I'm replying to!
But NeoPa seems to be pissed off with me ... I don't know why. Maybe I was being defensive because I thought I was being told off for using a two digit year (which I wouldn't do, even though it's now 990 years until the next "2000 problem") ... Weren't you telling me off? I'm baffled

Anyway, I completely agree with everything you said about date formats, and I don't know which bit of which message was allegedly "chain-pulling", but whatever ... my apologies if required, grovel-grovel

[mshmyob]

It's all a big conspiracy. Don't you guys know that time doesn't really exist (according to Einstein - no absolute time) now physists want to abolish it all together.

ALL HYPE!!! (hehe - don't hate me)

I am more concerned with Neo using text speak in his messages (atm). Now if only I had some TIME to report this infraction.

cheers,

[NeoPa]

@julietbrown
Let me take those in reverse order.

No. I wouldn't presume to tell you off for not taking my (technical) advice - especially before I'd given it. My wording was a strong expression of my opinion on the matter. That was all. Rereading my comment I can see how it may have come across as criticism of you, but it wasn't intended that way. I should probably have expressed myself more clearly.

I wasn't pissed off as such. I was more confused and unsure of what you were saying. Had it turned out to be intended as a disparaging remark, then I would have been put out certainly, but irked rather than annoyed would have described it. As it is, it seems to be a simple misunderstanding, which I'm happy to live with.

Actually, I've caught some of your other comments around the place, and they've all been positive from what I can see, so I'm more than happy to see more from you, whether question or answer.

[NeoPa]

@mshmyob
Good spot (How did I let that one slip)!