greater than and blank wih same field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi....the way I have this set up is not pulling the data correctly. I need
to pull records with close date greater than 2/28 and also those close date
fields are not populated. I also need to do that for end date fields. can
you help me edit this? thanks

SELECT
MDR.CASE.CASE_SID,
MDR.CASE_MV.CASE_TX_SETTING_DESC
FROM
MDR.CASE,
MDR.CASE_MV
WHERE
( MDR.CASE_MV.CASE_PID=MDR.CASE.CASE_PID )
AND ( MDR.CASE.ACTIVE_FLAG (+) = 1 )
AND (
MDR.CASE_MV.CASE_TX_SETTING_DESC IN ('CM- Medical', 'Non Complex CM')
AND MDR.CASE_MV.CASE_CLOSURE_DATE > '28-02-2005 00:00:00'
AND MDR.CASE_MV.CASE_END_DATE > '28-02-2005 00:00:00'
AND MDR.CASE_MV.CASE_CLOSURE_DATE IS NULL
AND MDR.CASE_MV.CASE_END_DATE IS NULL
)
 
JRS,

First of all, it would be easier to Join the two tables on the CASE_PID
field, rather than try and put it in the Where clause. As for the date
criteria, you need to use 'Or' rather than 'And', and assuming your date
fields are Date/Time data type you have the wrong delimiters, for example...

((MDR.CASE_MV.CASE_CLOSURE_DATE > #28-02-2005#) Or
(MDR.CASE_MV.CASE_CLOSURE_DATE Is Null)) AND
((MDR.CASE_MV.CASE_END_DATE > #28-02-2005#) Or
(MDR.CASE_MV.CASE_END_DATE Is Null))

This assumes I have correctly understood your intentions! Also, do you
mean to imply that MDR.CASE is the name of a table and MDR.CASE_MV is
the name of a table. I would recommend that you rename these so they do
not have a . in the table name. I have never seen this done before, but
I imagine this is asking for trouble as Access is likely to get confused
by this in some contexts.
 
Hi....the way I have this set up is not pulling the data correctly. I need
to pull records with close date greater than 2/28 and also those close date
fields are not populated. I also need to do that for end date fields. can
you help me edit this? thanks

SELECT
MDR.CASE.CASE_SID,
MDR.CASE_MV.CASE_TX_SETTING_DESC
FROM
MDR.CASE,
MDR.CASE_MV
WHERE
( MDR.CASE_MV.CASE_PID=MDR.CASE.CASE_PID )
AND ( MDR.CASE.ACTIVE_FLAG (+) = 1 )
AND (
MDR.CASE_MV.CASE_TX_SETTING_DESC IN ('CM- Medical', 'Non Complex CM')
AND MDR.CASE_MV.CASE_CLOSURE_DATE > '28-02-2005 00:00:00'
AND MDR.CASE_MV.CASE_END_DATE > '28-02-2005 00:00:00'
AND MDR.CASE_MV.CASE_CLOSURE_DATE IS NULL
AND MDR.CASE_MV.CASE_END_DATE IS NULL
Click to expand...

It seems that you're using ORACLE SQL syntax - the (+) operator for
instance. Different database programs have different dialects of SQL.
Access uses ANSI-92 JOINs in preference to WHERE clause joins, and
does not treat date/time values as text strings (I'm assuming that
your date fields are in an Access Date/Time field). I'm also guessing
that MDR is the SQL or ORACLE database name; that's not valid in a JET
database.

Try (guessing a bit here):

SELECT
CASE.CASE_SID,
CASE_MV.CASE_TX_SETTING_DESC
FROM
CASE LEFT JOIN
CASE_MV
ON
( CASE_MV.CASE_PID=CASE.CASE_PID )
WHERE
( CASE.ACTIVE_FLAG = 1)
AND (
CASE_MV.CASE_TX_SETTING_DESC IN ('CM- Medical', 'Non Complex CM')
AND (CASE_MV.CASE_CLOSURE_DATE > #02-28-2005 00:00:00#
AND CASE_MV.CASE_END_DATE > #02-28-2005 00:00:00#)
OR
(CASE_MV.CASE_CLOSURE_DATE IS NULL
AND CASE_MV.CASE_END_DATE IS NULL )

As stated you would only find those records where
CASE_MV.CASE_CLOSURE_DATE is a valid date greater than the 28th of
February and is also NULL - so of course no records will qualify. I'm
not quite certain what you actually want to find!

If your data is in Oracle or some other database engine, you may
prefer to use a Pass-Through query rather than converting to JET
syntax SQL.

John W. Vinson[MVP]
 
Back
Top