Access ADP Append Query with Conditions 1

[CarolCook]

Hi:
I am appending to a SQL table that has fields that do not accept NULLS - I also cannot change this property because it will eventually impact end users. I am using a 'from' table that sometimes has values and sometimes doesn't. So, for instance, if Address is blank, I want to append '' to the 'to' table and if not, I want to append the Address. There are 6 such fields and a virtually unlimited combination of which will be blank for any given row in my 'from' table. Is there any way to write my Select statement so that what appends can be driven by a formula? I have tried to create another table which makes the substitution but Access puts NULLS not empty strings back in the fields.
Thanks in advance,
Carol

 

[cmmrfrds]

In your select statement convert the nulls to whatever you want.

select isnull(fld1,0),
isnull(fld2,'to'),
isnull(fld3,99)
from yourtable

 

[CarolCook]

Thanks so much for your response.

I was looking for something like this -

iif(isnull([Address1]),'',[Address1]) as ADD1.

and of course, SQL is telling me that isnull needs 2 arguments.

Also, do I read isnull(fld1,0) as if fld1 is not null, insert it, 0 otherwise, insert a 0? Do I not need the as <expr> piece?

So I guess I need to rewrite my statement - just not sure of the syntax. I am getting closer thanks to you!

 

[cmmrfrds]

This is the syntax for sql server which uses transact_sql and since this is under ADP, I assume this is what you want.

isnull(fld1,0) as fld1

means if fld1 is not null then fld1 else 0
also, to get a meaningful name put in the alias.

Access mdb syntax. either nz or iif. I don't believe nz is supported in vb scripting which is used with ASP pages.

nz(fld1,0) as fld1
or
iif(fld1 is null,0,fld1) as fld1

 

[CarolCook]

The T-SQL stuff is exactly what I needed, works beautifully and solves the problem I wrestled with for quite a while yesterday.
You are a star!
Thanks.