Re: Criteria Problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" wrote:

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" <D@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C9AF859A-BD73-4294-99E2-3775667D4B16@xxxxxxxxxxxxxxxx
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]) & "*"));




.

Relevant Pages

  • RE: help with criteria
    ... query that joined the memb table to the loan table. ... then used the code column from loan and the acct column ... The criteria is now [acct Not In (SELECT ...
    (microsoft.public.access.queries)
  • Re: Criteria referencing Form ComboBox
    ... IF you enter the following as criteria under your field, Access will restructure it when it is saved, but the query should work UNLESS you try too many of this type of criteria at one time ... University of Maryland Baltimore County ... I am trying to put in an IIF statement to alter the outcome in the query. ...
    (microsoft.public.access.queries)
  • Re: Query Parameters
    ... >I am having trouble with my query. ... I know how to request one piece of ... >[Enter Acct Number] ... >have the user end the criteria by just entering nothing. ...
    (microsoft.public.access.queries)
  • Re: query to pull between two dates
    ... You can enter that in query design view as criteria in one criteria "cell" ... Access will restructure that when you save it into something that looks more complex. ... I want to the query to pull all the records that have a closed date between the "to" and "from" dates the user has selected or to pull all the records if they leave it blank. ...
    (microsoft.public.access.queries)
  • Re: Parameter Query
    ... > Following is my SQL. ... >> include the datatypes of whatever fields are used in the criteria. ... If you wish to show only those records where the [Acct] is within the ... Number in this table (such as a LookUp field or from the bound column ...
    (microsoft.public.access.queries)