Show if null or greater than

  • Thread starter Thread starter CarlaInJax
  • Start date Start date
C

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!
 
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 );
 
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
 
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.

KARL DEWEY said:
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.


CarlaInJax said:
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!
Click to expand...
Click to expand...
 
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


CarlaInJax said:
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.

KARL DEWEY said:
if the date is greater than one day.
Click to expand...
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.


CarlaInJax said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Duane,

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

Any ideas?

Duane Hookom said:
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


CarlaInJax said:
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.

KARL DEWEY said:
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!
Click to expand...
Click to expand...
Click to expand...
 
When I try that expression I get a message that says "the expression you
entered contains invalid syntax" and then it highlights the WHERE.
 
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 );
 
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.


ghetto_banjo said:
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 );
Click to expand...
Click to expand...
 
It is giving me the same message, but is now highlighting the word SELECT.

Any ideas?
 
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

CarlaInJax said:
Duane,

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

Any ideas?

Duane Hookom said:
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


CarlaInJax said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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!

John W. Vinson said:
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.


ghetto_banjo said:
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 );
Click to expand...
Click to expand...
Click to expand...
 
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
 
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!
Click to expand...

Please post your actual code.
 
Back
Top