Set value if it is NULL in sql - Access 2007

Question

Is it possible to set a value to values who is NULL?

The sql I use now is this:

SELECT date
FROM Activity
WHERE date BETWEEN [Forms]![Search]![fromDate] AND [Forms]![Search]![toDate]

But the problem is when there is no value for fromDate or toDate the result is zero rows. So what I want to do is when fromDate is NULL, change the fromDate value to 1800-01-01 and when toDate is NULL, change it to 2300-01-01.

Adriaan Stander · Accepted Answer · 2009-12-03 12:08:10Z

2

You should be able to use the NZ function

Nz(variant [, valueifnull ] )

NZ link

answered Dec 3, 2009 at 12:08

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jojo · Accepted Answer · 2009-12-03 12:11:20Z

0

Try this:

SELECT date  
FROM Activity
WHERE date BETWEEN 
    ISNULL([Forms]![Search]![fromDate], '1800-01-01') AND 
    ISNULL([Forms]![Search]![toDate], '2300-01-01')

answered Dec 3, 2009 at 12:11

Jojo

1,9433 gold badges29 silver badges30 bronze badges

7 Comments

Johan Over a year ago

I have tried that, but it does not work for me. Are you sure Access support that expression?

David-W-Fenton Over a year ago

This is T-SQL, not Jet/ACE SQL. the IsNull() function in Access/Jet/ACE only returns a Boolean if the expression evaluates to Null. The corresponding function is Nz(), i.e., NullToZero (which is not limited to numeric values, despite the name).

David-W-Fenton Over a year ago

@Jojo: posting answers for the wrong dialect of SQL costs you reputation points. -1.

onedaywhen Over a year ago

@David W. Fenton: Nz() is a Access object model function, not a Jet/ACE SQL function. The Jet/ACE SQL equivalent is IIF(expression_here IS NULL, true_condition_here, false_condition_here). Note this construct is superior to Nz() because it respects data types (IIRC Nz() coerces to 'string' too early).

David-W-Fenton Over a year ago

The context is Access, so the answer is correct, as it applies to THE PRESENT CONTEXT. Now, it's nice to have your comment pointing out that in other contexts other methods are required, Indeed, it would seem to me that you ought to post your own answer pointing this out, as it's a different approach than anyone else has offered and could be helpful to those trying to solve the same problem, but not in a specifically-Access context.

|

Collectives™ on Stack Overflow

Set value if it is NULL in sql - Access 2007

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related