del comma in excel output, in a split cell, access macro

[Guest]

I have a cell of product numbers some delimited by carriage returns but it
also has stray commas. I need to get rid of where in the access macro it is
putting the comma in the product number cells. I want the program/product
numbers to be delimited by just a carriage return. Here is a snippet of the
output commands.

The rsData is the dataset it is sorted by division name . The sProgramList
variable is a list of products sorted in rows by divisions. I think it loops
and inserts the program numbers all together for one style. The problem is
those pesky commas I would like to get rid of here. I think they might have
tried the trim command to get rid of it and it doesn't work? Otherwise I
don't know what the trim command does because there are no floating points
only integers. I don't know what the Nz is. I've looked through the code
and it seems to be used when the recordSetData is called. The lrow is a
placeholder. Somehow when it is looping through the rows adding program
numbers to the cell for that division it adds commas:


sDivName = rsData!DivisionName


lOrigRow = lrow
.Cells(lrow, 1).Value = LCase(rsData!EventName)
sProgramList = Trim(Replace(Nz(rsData!Programs, ""), ";", ""))

thanks,

 

[Guest]

I'll try to cover what some of these things do here
LCase converts all text within the parameter to lowercase, so whatever is in
the recordset, EventName field is being converted to lowercase. Easy to
compare things that are all lower or uppercase to overcome typo errors by
users.
Trim() removes any 'white space' as spaces and tabs from the beginning and
end of a string of characters, so " abc def ghi " would become "abc def ghi"
Nz() helps overcome problems where you are working with a value that is
null. It means NulltoZero the first parameter is what to evaluate, the
second parameter is what to return if first one does turn out to be Null. In
this case looks like they've chosen to return a zero-length string.

Now for the Replace - has 3 parameters, What to look in, what to look for,
what to replace the "what to look for" with when it is found.

So in the end they'r saying take the information from rsData!Programs
(rsData recordset, Programs field) and look for semicolons in the data and
replace the semicolons with a comma and a space.

I imagine the reason they're using the Trim is to get rid of the space
following the comma-space combination if it turns out to be added to the end
of the data in field Programs.

Hope that helps.

 

[Guest]

thanks very much. I will try replacing the second comma with a "" null .
The program seems to put each product value on its own line in the split cell
so this might work. I already have a delimiter I don't need the comma.

 

[Steve Schapel]

So in the end they'r saying take the information from rsData!Programs
(rsData recordset, Programs field) and look for semicolons in the data and
replace the semicolons with a comma and a space.

No. I think you have mis-read the Replace function that Janis showed.
It says to replace the semi-colon with a "" (zero-length string).

 

[Steve Schapel]

Janis,

"" is not the same as Null, in fact far from it.

 

[Guest]

Thanks for catching that - you are absolutely correct, on both counts -
#1 - that I got my eyes crossed and misread the code, and
#2 - that "" is not same as null.