Join() and Array with Null

[!TG]

I recently learned of Join
response.write join(ThisArray," ")

I am having trouble with it, however and it appears to be caused by null
characters.

Is there anyway around this join problem with Arrays containing Null?

 

[Ray Costanzo [MVP]]

What trouble? What? Can you give us code and errors?

Ray at work

 

[!TG]

What trouble? What? Can you give us code and errors?

Ray at work

I am very sorry!

Error is Type mismatch: 'join'

Code example is:

Fields = Array("Name","Address","HomePhone",Null)
Dim FieldList
FieldList = cstr(join(Fields,","))

I also tried it without the cstr

 

[Ray Costanzo [MVP]]

It seems that Join cannot handle nulls as you expected. Where is the null
value coming from? Can you get rid of it when you're creating your array?
How is your array being built. In this particular sample code, you could
do:

Fields = Array("Name", "Address", "HomePhone", Null & "")
Dim FieldList
FieldList = CStr(Join(Fields, ","))

Ray at work

 

[!TG]

It seems that Join cannot handle nulls as you expected. Where is the null
value coming from? Can you get rid of it when you're creating your array?
How is your array being built. In this particular sample code, you could
do:

Fields = Array("Name", "Address", "HomePhone", Null & "")
Dim FieldList
FieldList = CStr(Join(Fields, ","))

Ray at work

The array is built fromform fields submitted to an ASP which loads
fields into arrays to update a database.

There are about 100 fields from various databases with the html forms
across 7 or 8 pages, that use that function.
I built a system for my company that keeps track of employee information
and I am trying to create a database of changes when they are made, by
whom, and to what users, so I can guard against or at least track rogue
users down, should the need arrise.

 

[Bob Barrows [MVP]]

The array is built fromform fields submitted to an ASP which loads
fields into arrays to update a database.

So, when you populate the array, concatenate an empty string to the values
coming from the database.

Bob barrows

 

[!TG]

So, when you populate the array, concatenate an empty string to the values
coming from the database.

Bob barrows

There are too many different places that Null could be loaded into an array.
Can I replace Null with empty in arrays after the fact?
I've tried For Next loops before to do something similar but never had
any luck with arrays.

 

[Bob Barrows [MVP]]

There are too many different places that Null could be loaded into an
array. Can I replace Null with empty in arrays after the fact?

Yes, but then you'll have the same problem: "too many places"

I've tried For Next loops before to do something similar but never had
any luck with arrays.

You should consider rewriting your queries that retrieve the data to be used
for these arrays to prevent the retrieval of nulls.Most versions of sql
include functions to allow you to replace nulls with empty strings or
whatever. In SQL Server's Transact-SQL you can use COALESCE or ISNULL. With
Access you can use the VBA "immediate if" function: iif()

Bob Barrows