Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Criteria (Access2000)

    how do u write a criteria which means Not = input_date in a query
    I've tried the above as well as Not "input_date"
    but it doesn't seem to work.
    Please <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Query Criteria (Access2000)

    Hi,
    Try:
    <> input_date
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (Access2000)

    That works but there is only one problem Rory, The difference between [created] feild and [input_date] feild is in seconds. What I want it to do is pick up records which have been modified a day or more days later.
    The criteria u gave me picks up data that are with seconds difference. Any suggestions??

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Query Criteria (Access2000)

    Ah, you didn't mention that there were time values involved as well. In that case you could use:
    crdate: datevalue([created])
    which should only return the date part of your [created] field.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (Access2000)

    It still thinks that the dates have time values and returns records that are entered in seconds difference.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Query Criteria (Access2000)

    Hmm, datevalue definitely should not return time values too. Are your dates displaying as 4 digit years? If so, are you entering your input_date using 4 digit years - you probably need to.
    If none of the above applies, can you post the SQL for your query?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (Access2000)

    SELECT FormTradeEntry.tnum, IIf([fund]="SFM_1" Or [fund]="QP01_06" Or [fund]=" QP01_07","QP-01","QP-02") AS Account, FormTradeEntry.id, FormTradeEntry.cusip, FormTradeEntry.isin, FormTradeEntry.sedol, FormTradeEntry.clr2, tblTransactionType.SFMTT, FormTradeEntry.td, FormTradeEntry.settles, FormTradeEntry.q, FormTradeEntry.tccy, FormTradeEntry.tp, FormTradeEntry.tc, FormTradeEntry.tai, FormTradeEntry.tax, FormTradeEntry.moneyspot, FormTradeEntry.notes, FormTradeEntry.cancel, FormTradeEntry.fund, DateValue([created]) AS crdate, DateValue([input_date]) AS IPdate
    FROM FormTradeEntry LEFT JOIN tblTransactionType ON FormTradeEntry.tt = tblTransactionType.OrigTT
    WHERE (((FormTradeEntry.fund)="SFM_1" Or (FormTradeEntry.fund)="SFM_2" Or (FormTradeEntry.fund)="QP01_06" Or (FormTradeEntry.fund)="QP01_07" Or (FormTradeEntry.fund)="QP02_06" Or (FormTradeEntry.fund)="QP02_07") AND ((DateValue([created]))<>[IPdate]));

  8. #8
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (Access2000)

    Rory it kind of works coz it asks me to enter the date and displays all records except for those entered on that day (entered date) where it should compare it without me entering the date.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Query Criteria (Access2000)

    Sorry, I had assumed for some reason that input_date was a parameter you were entering rather than a field in your table! You can add a field something like:
    delay: datevalue(input_date)-datevalue(created)
    and specify a criteria for this field of >= 1.
    Note: I've assumed that [created] will be earlier than [input_date]; if it's not, you'll need to either swap the two fields over or specify <=-1 as the criteria.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (Access2000)

    Thanx Rory!
    <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •