Criteria Problem

  • Thread starter Thread starter D
  • Start date Start date
D

D

Hi:

Can you please tell me why in the below qry. if I do not enter the date
(11/23/2007) in both SD and ED, I get the right result, but if I input
SD=11/23/2007 and ED=11/23/2007 I get nothinhg??

Thanks,

Dan

***

SELECT DISTINCT EDSF.TRANS_ORG_UNIT, EDSF.TRANS_ACCOUNT,
EDSF.TRANS_SUB_ACC_NMBR, EDSF.TRANS_PRODUCT, EDSF.TRANS_CUST_GROUP,
EDSF.TRANS_CCY_CODE, EDSF.SRC_SYS_TRANS_ID, EDSF.SRC_SYS_DEAL_ID,
EDSF.TRANS_CCY_AMT, EDSF.GLM_CEQ_CCY_AMT, EDSF.FEED_EFF_DATE
FROM EDSF INNER JOIN [AFFX_org units] ON EDSF.TRANS_ORG_UNIT = [AFFX_org
units].[Org Unit]
WHERE (((EDSF.FEED_EFF_DATE) Like (([EDSF].[FEED_EFF_DATE]) Between [Please
enter the Start Date] And [Please enter the End Date]) & "*"));
 
D said:
Can you please tell me why in the below qry. if I do not enter the date
(11/23/2007) in both SD and ED, I get the right result, but if I input
SD=11/23/2007 and ED=11/23/2007 I get nothinhg??

SELECT DISTINCT EDSF.TRANS_ORG_UNIT, EDSF.TRANS_ACCOUNT,
EDSF.TRANS_SUB_ACC_NMBR, EDSF.TRANS_PRODUCT, EDSF.TRANS_CUST_GROUP,
EDSF.TRANS_CCY_CODE, EDSF.SRC_SYS_TRANS_ID, EDSF.SRC_SYS_DEAL_ID,
EDSF.TRANS_CCY_AMT, EDSF.GLM_CEQ_CCY_AMT, EDSF.FEED_EFF_DATE
FROM EDSF INNER JOIN [AFFX_org units] ON EDSF.TRANS_ORG_UNIT = [AFFX_org
units].[Org Unit]
WHERE (((EDSF.FEED_EFF_DATE) Like (([EDSF].[FEED_EFF_DATE]) Between [Please
enter the Start Date] And [Please enter the End Date]) & "*"));
Click to expand...


Get rid of the Like part:

WHERE EDSF.FEED_EFF_DATE Between [Please
enter the Start Date] And [Please enter the End Date]

If the Like part was supposed to deal with the date field
having a time part, then use:

WHERE DateValue(EDSF.FEED_EFF_DATE) Between [Please
enter the Start Date] And [Please enter the End Date]
 
Hi:

Can you please tell me why in the below qry. if I do not enter the date
(11/23/2007) in both SD and ED, I get the right result, but if I input
SD=11/23/2007 and ED=11/23/2007 I get nothinhg??

Thanks,

Dan

***

SELECT DISTINCT EDSF.TRANS_ORG_UNIT, EDSF.TRANS_ACCOUNT,
EDSF.TRANS_SUB_ACC_NMBR, EDSF.TRANS_PRODUCT, EDSF.TRANS_CUST_GROUP,
EDSF.TRANS_CCY_CODE, EDSF.SRC_SYS_TRANS_ID, EDSF.SRC_SYS_DEAL_ID,
EDSF.TRANS_CCY_AMT, EDSF.GLM_CEQ_CCY_AMT, EDSF.FEED_EFF_DATE
FROM EDSF INNER JOIN [AFFX_org units] ON EDSF.TRANS_ORG_UNIT = [AFFX_org
units].[Org Unit]
WHERE (((EDSF.FEED_EFF_DATE) Like (([EDSF].[FEED_EFF_DATE]) Between [Please
enter the Start Date] And [Please enter the End Date]) & "*"));
Click to expand...

Assuming [FEED_EFF_DATE] is a DateTime datatype field (not text),
then:

WHERE EDSF.FEED_EFF_DATE) Between [Please enter the Start Date] And
[Please enter the End Date];

Only records between the entered Start and End dates will be returned.

If you also wish to return ALL records if nothing is entered, then:

WHERE EDSF.FEED_EFF_DATE) Between [Please enter the Start Date] And
[Please enter the End Date] Or [Please enter the Start Date] is null;

which will return all records if nothing is entered as the Start Date.
 
Hi Marshall:

Thanks! But, now if if I leave the dates blank I get nothing; I would like
the options to enter the dates or leave them blank?

Thanks,

Dan

Marshall Barton said:
D said:
Can you please tell me why in the below qry. if I do not enter the date
(11/23/2007) in both SD and ED, I get the right result, but if I input
SD=11/23/2007 and ED=11/23/2007 I get nothinhg??

SELECT DISTINCT EDSF.TRANS_ORG_UNIT, EDSF.TRANS_ACCOUNT,
EDSF.TRANS_SUB_ACC_NMBR, EDSF.TRANS_PRODUCT, EDSF.TRANS_CUST_GROUP,
EDSF.TRANS_CCY_CODE, EDSF.SRC_SYS_TRANS_ID, EDSF.SRC_SYS_DEAL_ID,
EDSF.TRANS_CCY_AMT, EDSF.GLM_CEQ_CCY_AMT, EDSF.FEED_EFF_DATE
FROM EDSF INNER JOIN [AFFX_org units] ON EDSF.TRANS_ORG_UNIT = [AFFX_org
units].[Org Unit]
WHERE (((EDSF.FEED_EFF_DATE) Like (([EDSF].[FEED_EFF_DATE]) Between [Please
enter the Start Date] And [Please enter the End Date]) & "*"));
Click to expand...


Get rid of the Like part:

WHERE EDSF.FEED_EFF_DATE Between [Please
enter the Start Date] And [Please enter the End Date]

If the Like part was supposed to deal with the date field
having a time part, then use:

WHERE DateValue(EDSF.FEED_EFF_DATE) Between [Please
enter the Start Date] And [Please enter the End Date]
Click to expand...
 
Hi Fred:

Thanks! that works if you do not have any other criteria; but for example,
you have an [Account] criteria before the dates and enter a specific acct.
and no dates, you get all the accounts?

Thanks,

Dan

fredg said:
Hi:

Can you please tell me why in the below qry. if I do not enter the date
(11/23/2007) in both SD and ED, I get the right result, but if I input
SD=11/23/2007 and ED=11/23/2007 I get nothinhg??

Thanks,

Dan

***

SELECT DISTINCT EDSF.TRANS_ORG_UNIT, EDSF.TRANS_ACCOUNT,
EDSF.TRANS_SUB_ACC_NMBR, EDSF.TRANS_PRODUCT, EDSF.TRANS_CUST_GROUP,
EDSF.TRANS_CCY_CODE, EDSF.SRC_SYS_TRANS_ID, EDSF.SRC_SYS_DEAL_ID,
EDSF.TRANS_CCY_AMT, EDSF.GLM_CEQ_CCY_AMT, EDSF.FEED_EFF_DATE
FROM EDSF INNER JOIN [AFFX_org units] ON EDSF.TRANS_ORG_UNIT = [AFFX_org
units].[Org Unit]
WHERE (((EDSF.FEED_EFF_DATE) Like (([EDSF].[FEED_EFF_DATE]) Between [Please
enter the Start Date] And [Please enter the End Date]) & "*"));
Click to expand...

Assuming [FEED_EFF_DATE] is a DateTime datatype field (not text),
then:

WHERE EDSF.FEED_EFF_DATE) Between [Please enter the Start Date] And
[Please enter the End Date];

Only records between the entered Start and End dates will be returned.

If you also wish to return ALL records if nothing is entered, then:

WHERE EDSF.FEED_EFF_DATE) Between [Please enter the Start Date] And
[Please enter the End Date] Or [Please enter the Start Date] is null;

which will return all records if nothing is entered as the Start Date.
Click to expand...
 
WHERE (EDSF.FEED_EFF_DATE >= [Please enter the Start Date] Or [Please enter
the Start Date] is Null) And
(EDSF.FEED_EFF_DATE <=[Please enter the End Date] OR [Please enter the End
Date] IS Null)

That gets
--all records between the start and end date parameters if both are entered
--All records in both start and end date parameters are left blank
--Records after the start date if start date parameter is entered and end
date is left blank
-- Records before the end date if start date parameter is left blank and end
date parameter is entered.

NOTE that Access will restructure this when you close the query. And if you
have other criteria the query can get "TOO COMPLEX" to run because of the
restructuring.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Hi John:

Thanks! I think I have open(ed) a 'big can of worms'!

Yes, this is working if not other criteria; if I add the [acct] criteria for
example, and enter one acct. and LEAVE all the dates blank I get all the
accounts??? is this beacuse ACCESS?

Any other ideas would be greately appreciated!!

Thanks,

Dan



John Spencer said:
WHERE (EDSF.FEED_EFF_DATE >= [Please enter the Start Date] Or [Please enter
the Start Date] is Null) And
(EDSF.FEED_EFF_DATE <=[Please enter the End Date] OR [Please enter the End
Date] IS Null)

That gets
--all records between the start and end date parameters if both are entered
--All records in both start and end date parameters are left blank
--Records after the start date if start date parameter is entered and end
date is left blank
-- Records before the end date if start date parameter is left blank and end
date parameter is entered.

NOTE that Access will restructure this when you close the query. And if you
have other criteria the query can get "TOO COMPLEX" to run because of the
restructuring.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

D said:
Hi:

Can you please tell me why in the below qry. if I do not enter the date
(11/23/2007) in both SD and ED, I get the right result, but if I input
SD=11/23/2007 and ED=11/23/2007 I get nothinhg??

Thanks,

Dan

***

SELECT DISTINCT EDSF.TRANS_ORG_UNIT, EDSF.TRANS_ACCOUNT,
EDSF.TRANS_SUB_ACC_NMBR, EDSF.TRANS_PRODUCT, EDSF.TRANS_CUST_GROUP,
EDSF.TRANS_CCY_CODE, EDSF.SRC_SYS_TRANS_ID, EDSF.SRC_SYS_DEAL_ID,
EDSF.TRANS_CCY_AMT, EDSF.GLM_CEQ_CCY_AMT, EDSF.FEED_EFF_DATE
FROM EDSF INNER JOIN [AFFX_org units] ON EDSF.TRANS_ORG_UNIT = [AFFX_org
units].[Org Unit]
WHERE (((EDSF.FEED_EFF_DATE) Like (([EDSF].[FEED_EFF_DATE]) Between
[Please
enter the Start Date] And [Please enter the End Date]) & "*"));
Click to expand...
Click to expand...
 
Watch your parentheses and this should work. It just gets tricky -
especially when Access adds lots of extra parentheses to the clause.



WHERE (EDSF.FEED_EFF_DATE >= [Please enter the Start Date]
Or [Please enter the Start Date] is Null) And
(EDSF.FEED_EFF_DATE <=[Please enter the End Date]
OR [Please enter the End Date] IS Null)
AND Acct = "Your Account value"




--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John Spencer said:
WHERE (EDSF.FEED_EFF_DATE >= [Please enter the Start Date] Or [Please
enter the Start Date] is Null) And
(EDSF.FEED_EFF_DATE <=[Please enter the End Date] OR [Please enter the End
Date] IS Null)

That gets
--all records between the start and end date parameters if both are
entered
--All records in both start and end date parameters are left blank
--Records after the start date if start date parameter is entered and end
date is left blank
-- Records before the end date if start date parameter is left blank and
end date parameter is entered.

NOTE that Access will restructure this when you close the query. And if
you have other criteria the query can get "TOO COMPLEX" to run because of
the restructuring.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

D said:
Hi:

Can you please tell me why in the below qry. if I do not enter the date
(11/23/2007) in both SD and ED, I get the right result, but if I input
SD=11/23/2007 and ED=11/23/2007 I get nothinhg??

Thanks,

Dan

***

SELECT DISTINCT EDSF.TRANS_ORG_UNIT, EDSF.TRANS_ACCOUNT,
EDSF.TRANS_SUB_ACC_NMBR, EDSF.TRANS_PRODUCT, EDSF.TRANS_CUST_GROUP,
EDSF.TRANS_CCY_CODE, EDSF.SRC_SYS_TRANS_ID, EDSF.SRC_SYS_DEAL_ID,
EDSF.TRANS_CCY_AMT, EDSF.GLM_CEQ_CCY_AMT, EDSF.FEED_EFF_DATE
FROM EDSF INNER JOIN [AFFX_org units] ON EDSF.TRANS_ORG_UNIT = [AFFX_org
units].[Org Unit]
WHERE (((EDSF.FEED_EFF_DATE) Like (([EDSF].[FEED_EFF_DATE]) Between
[Please
enter the Start Date] And [Please enter the End Date]) & "*"));
Click to expand...
Click to expand...
 
Hi John:

This looks like is working with/ or without entering criteria! Oohh...

THANK YOU Everybody!

SELECT DISTINCT EDSF.TRANS_ORG_UNIT, EDSF.TRANS_ACCOUNT,
EDSF.TRANS_SUB_ACC_NMBR, EDSF.TRANS_PRODUCT, EDSF.TRANS_CUST_GROUP,
EDSF.TRANS_CCY_CODE, EDSF.SRC_SYS_TRANS_ID, EDSF.SRC_SYS_DEAL_ID,
EDSF.TRANS_CCY_AMT, EDSF.GLM_CEQ_CCY_AMT, EDSF.FEED_EFF_DATE
FROM EDSF INNER JOIN [AFFX_org units] ON EDSF.TRANS_ORG_UNIT = [AFFX_org
units].[Org Unit]
WHERE (((EDSF.TRANS_ACCOUNT)=[Enter AccountNo]) AND ((EDSF.FEED_EFF_DATE)
Like (([EDSF].[FEED_EFF_DATE]) Between [Please enter the Start Date] And
[Please enter the End Date]) & "*")) OR (((EDSF.TRANS_ACCOUNT)=[Enter
AccountNo]) AND ((EDSF.FEED_EFF_DATE) Like "*"));





John Spencer said:
Watch your parentheses and this should work. It just gets tricky -
especially when Access adds lots of extra parentheses to the clause.



WHERE (EDSF.FEED_EFF_DATE >= [Please enter the Start Date]
Or [Please enter the Start Date] is Null) And
(EDSF.FEED_EFF_DATE <=[Please enter the End Date]
OR [Please enter the End Date] IS Null)
AND Acct = "Your Account value"




--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John Spencer said:
WHERE (EDSF.FEED_EFF_DATE >= [Please enter the Start Date] Or [Please
enter the Start Date] is Null) And
(EDSF.FEED_EFF_DATE <=[Please enter the End Date] OR [Please enter the End
Date] IS Null)

That gets
--all records between the start and end date parameters if both are
entered
--All records in both start and end date parameters are left blank
--Records after the start date if start date parameter is entered and end
date is left blank
-- Records before the end date if start date parameter is left blank and
end date parameter is entered.

NOTE that Access will restructure this when you close the query. And if
you have other criteria the query can get "TOO COMPLEX" to run because of
the restructuring.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

D said:
Hi:

Can you please tell me why in the below qry. if I do not enter the date
(11/23/2007) in both SD and ED, I get the right result, but if I input
SD=11/23/2007 and ED=11/23/2007 I get nothinhg??

Thanks,

Dan

***

SELECT DISTINCT EDSF.TRANS_ORG_UNIT, EDSF.TRANS_ACCOUNT,
EDSF.TRANS_SUB_ACC_NMBR, EDSF.TRANS_PRODUCT, EDSF.TRANS_CUST_GROUP,
EDSF.TRANS_CCY_CODE, EDSF.SRC_SYS_TRANS_ID, EDSF.SRC_SYS_DEAL_ID,
EDSF.TRANS_CCY_AMT, EDSF.GLM_CEQ_CCY_AMT, EDSF.FEED_EFF_DATE
FROM EDSF INNER JOIN [AFFX_org units] ON EDSF.TRANS_ORG_UNIT = [AFFX_org
units].[Org Unit]
WHERE (((EDSF.FEED_EFF_DATE) Like (([EDSF].[FEED_EFF_DATE]) Between
[Please
enter the Start Date] And [Please enter the End Date]) & "*"));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top