Show if null or greater than

[CarlaInJax]

I have a table with date fields [Date Call Received] and [Date Notice
Received]. I would like for the query to show me only the calls that have no
[Date Notice Received] date (are null) or if the date is greater than one
day.
Please help!

 

[ghetto_banjo]

when you say "or if the date is greater than one day", what exactly do
you mean? The difference between those 2 dates is greater than 1 day?

if so, something like this would do it:

SELECT *
FROM yourTable
WHERE ([Date Notice Received] Is Null) OR (DateDiff("d", [Date Call
Received], [Date Notice Received]) > 1 );

 

[KARL DEWEY]

if the date is greater than one day.
A DateTime field records a point in time and therefore cannot be greater
than one day. Or maybe you mean greater than the current day.
WHERE [Date Notice Received] > Date() OR [Date Notice Received] Is Null

 

[CarlaInJax]

Sorry...perhaps I misphrased it. I need the query to show any records where
the difference between [Date Call Received] and [Date Notice Received] is
greater than one day. I can get it to calculate the number of days between
the two dates, but I only care if the difference is greater than 1 or if the
[Date Notice Received] is blank.

A DateTime field records a point in time and therefore cannot be greater
than one day. Or maybe you mean greater than the current day.
WHERE [Date Notice Received] > Date() OR [Date Notice Received] Is Null


--
Build a little, test a little.

I have a table with date fields [Date Call Received] and [Date Notice
Received]. I would like for the query to show me only the calls that have no
[Date Notice Received] date (are null) or if the date is greater than one
day.
Please help!

 

[Duane Hookom]

Try something like:
WHERE DateDiff("d",[Date Call Received],Nz([Date Notice Received],[Date Call
Received]) > 0

This assumes Date Call Received is never null.
--
Duane Hookom
Microsoft Access MVP

Sorry...perhaps I misphrased it. I need the query to show any records where
the difference between [Date Call Received] and [Date Notice Received] is
greater than one day. I can get it to calculate the number of days between
the two dates, but I only care if the difference is greater than 1 or if the
[Date Notice Received] is blank.

if the date is greater than one day.

A DateTime field records a point in time and therefore cannot be greater
than one day. Or maybe you mean greater than the current day.
WHERE [Date Notice Received] > Date() OR [Date Notice Received] Is Null


--
Build a little, test a little.

I have a table with date fields [Date Call Received] and [Date Notice
Received]. I would like for the query to show me only the calls that have no
[Date Notice Received] date (are null) or if the date is greater than one
day.
Please help!

 

[CarlaInJax]

Duane,

I get a message that says "the expression you entered contains invalid
syntax" and then it highlights the WHERE.

Any ideas?

Try something like:
WHERE DateDiff("d",[Date Call Received],Nz([Date Notice Received],[Date Call
Received]) > 0

This assumes Date Call Received is never null.
--
Duane Hookom
Microsoft Access MVP

Sorry...perhaps I misphrased it. I need the query to show any records where
the difference between [Date Call Received] and [Date Notice Received] is
greater than one day. I can get it to calculate the number of days between
the two dates, but I only care if the difference is greater than 1 or if the
[Date Notice Received] is blank.

if the date is greater than one day.
A DateTime field records a point in time and therefore cannot be greater
than one day. Or maybe you mean greater than the current day.
WHERE [Date Notice Received] > Date() OR [Date Notice Received] Is Null


--
Build a little, test a little.


:

I have a table with date fields [Date Call Received] and [Date Notice
Received]. I would like for the query to show me only the calls that have no
[Date Notice Received] date (are null) or if the date is greater than one
day.
Please help!

 

[CarlaInJax]

When I try that expression I get a message that says "the expression you
entered contains invalid syntax" and then it highlights the WHERE.

 

[ghetto_banjo]

Where are you placing Duane's expression? It is just the WHERE Clause
of an SQL statement. You would still need the SELECT/FROM parts of
the SQL as well. Something like my original post:

SELECT *
FROM yourTable
WHERE ([Date Notice Received] Is Null) OR (DateDiff("d", [Date Call
Received], [Date Notice Received]) > 1 );

 

[John W. Vinson]

On Thu, 8 Apr 2010 12:38:02 -0700, CarlaInJax

Are you perhaps getting tripped up by the word wrap? The SQL should all be on
one line with no line breaks in it.

When I try that expression I get a message that says "the expression you
entered contains invalid syntax" and then it highlights the WHERE.

when you say "or if the date is greater than one day", what exactly do
you mean? The difference between those 2 dates is greater than 1 day?

if so, something like this would do it:

SELECT *
FROM yourTable
WHERE ([Date Notice Received] Is Null) OR (DateDiff("d", [Date Call
Received], [Date Notice Received]) > 1 );

 

[CarlaInJax]

It is giving me the same message, but is now highlighting the word SELECT.

Any ideas?

 

[Duane Hookom]

You probably didn't fix my typos. Try
WHERE DateDiff("d",[Date Call Received],Nz([Date Notice Received],[Date Call
Received])) > 0


--
Duane Hookom
MS Access MVP

Duane,

I get a message that says "the expression you entered contains invalid
syntax" and then it highlights the WHERE.

Any ideas?

Try something like:
WHERE DateDiff("d",[Date Call Received],Nz([Date Notice Received],[Date
Call
Received]) > 0

This assumes Date Call Received is never null.
--
Duane Hookom
Microsoft Access MVP

Sorry...perhaps I misphrased it. I need the query to show any records
where
the difference between [Date Call Received] and [Date Notice Received]
is
greater than one day. I can get it to calculate the number of days
between
the two dates, but I only care if the difference is greater than 1 or
if the
[Date Notice Received] is blank.

:

if the date is greater than one day.
A DateTime field records a point in time and therefore cannot be
greater
than one day. Or maybe you mean greater than the current day.
WHERE [Date Notice Received] > Date() OR [Date Notice Received]
Is Null


--
Build a little, test a little.


:

I have a table with date fields [Date Call Received] and [Date
Notice
Received]. I would like for the query to show me only the calls
that have no
[Date Notice Received] date (are null) or if the date is greater
than one
day.
Please help!

 

[CarlaInJax]

In the SQL view I copied and pasted the expession removing all line breaks.
I now get an error that states CHARACTERS FOUND AFTER END OF SQL STATEMENT.
Aaarrrggghhh!

On Thu, 8 Apr 2010 12:38:02 -0700, CarlaInJax

Are you perhaps getting tripped up by the word wrap? The SQL should all be on
one line with no line breaks in it.

When I try that expression I get a message that says "the expression you
entered contains invalid syntax" and then it highlights the WHERE.

when you say "or if the date is greater than one day", what exactly do
you mean? The difference between those 2 dates is greater than 1 day?

if so, something like this would do it:

SELECT *
FROM yourTable
WHERE ([Date Notice Received] Is Null) OR (DateDiff("d", [Date Call
Received], [Date Notice Received]) > 1 );

 

[John Spencer]

Probable cause.

You failed to remove the semi-colon at the end of the existing SQL statement.
The semi-colon is the signal that this is the end of the query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

In the SQL view I copied and pasted the expession removing all line breaks.
I now get an error that states CHARACTERS FOUND AFTER END OF SQL STATEMENT.
Aaarrrggghhh!

Please post your actual code.