Null Value in date field

G

Guest

Hi All,

I have a table with a date field, the default vlaue is set to Null. When i
run a query that changes all the null values to 1 and not null vlaues to 0, i
recieve a compile error message. Can someone tell me what I'm doing wrong?

Expr1: Sum(IIf(IsNull([Processtbl]![Ship_Date]),1,0))
 
D

Douglas J. Steele

What's the error message? You might also show the SQL for the complete
query, just in case it's not directly related to that calculation.
 
G

Guest

I receive the compile error in query expression

SELECT Processtbl.Ship_Date, Sum(IIf(IsNull([Processtbl]![Ship_Date]),1,0))
AS Expr1
FROM Processtbl;

Douglas J. Steele said:
What's the error message? You might also show the SQL for the complete
query, just in case it's not directly related to that calculation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ram said:
Hi All,

I have a table with a date field, the default vlaue is set to Null. When i
run a query that changes all the null values to 1 and not null vlaues to
0, i
recieve a compile error message. Can someone tell me what I'm doing wrong?

Expr1: Sum(IIf(IsNull([Processtbl]![Ship_Date]),1,0))
Click to expand...
Click to expand...
 
D

Douglas J. Steele

And what's the error message?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


ram said:
I receive the compile error in query expression

SELECT Processtbl.Ship_Date,
Sum(IIf(IsNull([Processtbl]![Ship_Date]),1,0))
AS Expr1
FROM Processtbl;

Douglas J. Steele said:
What's the error message? You might also show the SQL for the complete
query, just in case it's not directly related to that calculation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ram said:
Hi All,

I have a table with a date field, the default vlaue is set to Null.
When i
run a query that changes all the null values to 1 and not null vlaues
to
0, i
recieve a compile error message. Can someone tell me what I'm doing
wrong?

Expr1: Sum(IIf(IsNull([Processtbl]![Ship_Date]),1,0))
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

Hi All,

I have a table with a date field, the default vlaue is set to Null. When i
run a query that changes all the null values to 1 and not null vlaues to 0, i
recieve a compile error message. Can someone tell me what I'm doing wrong?

Expr1: Sum(IIf(IsNull([Processtbl]![Ship_Date]),1,0))
Click to expand...

Try replacing the ! (used for form control references) with . (used for
table.field notation in SQL).

Compact the database too, just in case there's an error elsewhere. If it's a
VBA compilation message the problem lies elsewhere!


John W. Vinson [MVP]
 
D

Dale Fye

What exactly is it that you are trying to do? The SUM code you posted in
your first query doesn't "Change" [Ship_Date] to anything, it evaluates it
and basically counts the number of NULL values in that field.

If you want a list of ship dates, and a count of the number of entries that
have the same ship date, you need a group by clause:

SELECT Ship_Date, COUNT(*) as Freq
FROM Processtbl
GROUP BY Ship_Date

If all you want to do is count the number of NULL values in the Ship_Date
field, then try:

SELECT COUNT(*) as CountOfNulls
FROM Processtbl
WHERE Ship_Date IS NULL

HTH
Dale

ram said:
I receive the compile error in query expression

SELECT Processtbl.Ship_Date,
Sum(IIf(IsNull([Processtbl]![Ship_Date]),1,0))
AS Expr1
FROM Processtbl;

Douglas J. Steele said:
What's the error message? You might also show the SQL for the complete
query, just in case it's not directly related to that calculation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ram said:
Hi All,

I have a table with a date field, the default vlaue is set to Null.
When i
run a query that changes all the null values to 1 and not null vlaues
to
0, i
recieve a compile error message. Can someone tell me what I'm doing
wrong?

Expr1: Sum(IIf(IsNull([Processtbl]![Ship_Date]),1,0))
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks for your help

Douglas J. Steele said:
And what's the error message?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


ram said:
I receive the compile error in query expression

SELECT Processtbl.Ship_Date,
Sum(IIf(IsNull([Processtbl]![Ship_Date]),1,0))
AS Expr1
FROM Processtbl;

Douglas J. Steele said:
What's the error message? You might also show the SQL for the complete
query, just in case it's not directly related to that calculation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi All,

I have a table with a date field, the default vlaue is set to Null.
When i
run a query that changes all the null values to 1 and not null vlaues
to
0, i
recieve a compile error message. Can someone tell me what I'm doing
wrong?

Expr1: Sum(IIf(IsNull([Processtbl]![Ship_Date]),1,0))
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks for your help

John W. Vinson said:
Hi All,

I have a table with a date field, the default vlaue is set to Null. When i
run a query that changes all the null values to 1 and not null vlaues to 0, i
recieve a compile error message. Can someone tell me what I'm doing wrong?

Expr1: Sum(IIf(IsNull([Processtbl]![Ship_Date]),1,0))
Click to expand...

Try replacing the ! (used for form control references) with . (used for
table.field notation in SQL).

Compact the database too, just in case there's an error elsewhere. If it's a
VBA compilation message the problem lies elsewhere!


John W. Vinson [MVP]
Click to expand...
 
G

Guest

Thanks for your help


Dale Fye said:
What exactly is it that you are trying to do? The SUM code you posted in
your first query doesn't "Change" [Ship_Date] to anything, it evaluates it
and basically counts the number of NULL values in that field.

If you want a list of ship dates, and a count of the number of entries that
have the same ship date, you need a group by clause:

SELECT Ship_Date, COUNT(*) as Freq
FROM Processtbl
GROUP BY Ship_Date

If all you want to do is count the number of NULL values in the Ship_Date
field, then try:

SELECT COUNT(*) as CountOfNulls
FROM Processtbl
WHERE Ship_Date IS NULL

HTH
Dale

ram said:
I receive the compile error in query expression

SELECT Processtbl.Ship_Date,
Sum(IIf(IsNull([Processtbl]![Ship_Date]),1,0))
AS Expr1
FROM Processtbl;

Douglas J. Steele said:
What's the error message? You might also show the SQL for the complete
query, just in case it's not directly related to that calculation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi All,

I have a table with a date field, the default vlaue is set to Null.
When i
run a query that changes all the null values to 1 and not null vlaues
to
0, i
recieve a compile error message. Can someone tell me what I'm doing
wrong?

Expr1: Sum(IIf(IsNull([Processtbl]![Ship_Date]),1,0))
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

return a null value to date field 2
return null to date field 2
Aggregate Function Error 0
Hiding Labels in Reports if Field Blank 2
Null Value 1
selecting null with an iif expression in criteria access 2k3 13
date selection in vba vs query designer 5
Sum 4 Fields with conditions 4

Top