Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Criteria for Dates (Office 2000)

    Hello,
    I have a Contract database which is used to send out contracts to be signed. I have created a query to merge with Word to send letters to those that have not returned their signed contracts yet. My problem is with the criteria.

    I have a ContractStart field, a ContractEnd field and ContractSigned field (yes/no). The ContractStart field is always the first of a particular month and the ContractEnd is always the last day of the month a year later. I currently have my query to have the ContractStart field "Is not Null" and the ContractSigned field is "No".

    Since we send out contract renewals every month, I would like to add a date criteria to the ContractStart criteria to be more than 20 days, and less than 30 days to the Contract End criteria so that the Reminder letters won't include those contracts recently sent and those that will be sent shortly.

    I hope this all makes sence!

    Thanks!
    Louise

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria for Dates (Office 2000)

    For the ContractSttart field, set the criteria to

    <Date()-20

    and for the ContractEnd field, use

    >Date()+30

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria for Dates (Office 2000)

    Hi Hans,

    I'm probably all wrong on this, (I haven't tried it yet) but doesn't the "Date()" refer to today's date? If so, I need the results to be from whatever date is in the Contract Start and End fields.

    I'll try it though and let you know!

    Thanks Hans!
    Louise

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria for Dates (Office 2000)

    Date() indeed refers to the current date. Perhaps I haven't understood your description. I thought you wanted to select records whose ContractStart date is more than 20 days ago, and whose ContractEnd date is more than 30 days in the future.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria for Dates (Office 2000)

    Hi Hans,

    Thank you so much. It works beautifully. I should have known better than to question you. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    My assumption was indeed incorrect. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    Thanks again!
    Louise

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria for Dates (Office 2000)

    Hello again,
    Maybe it works correctly because of today's date, but yes, I meant for it to calculate the days from whatever date is in those fields from today's date.
    So if a contract had not been returned as of today and the contract start date was October 1st a letter needs to go out, but if the contract start date was only November 1st, we want to give them a little more time and not send a reminder letter. Forget the Contract End date, now that I think about it, it doesn't matter what that date is.

    Thanks Hans,
    Louise

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria for Dates (Office 2000)

    Hi Hans,

    Never mind. It works as it should with your original reply. I guess I'm just having one of my stupid days. <img src=/S/doh.gif border=0 alt=doh width=15 height=15> <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    Thanks again,
    Louise

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria for Dates (Office 2000)

    The criteria <Date()-20 for ContractStart will make the query select only records whose ContractStart is more than 20 days before the current date (i.e. the date the query is run). So today (November 10, 2006), records with a ContractStart of October 1, 2006 or before would be included, but records with a ContractStart of November 1, 2006 will be excluded.
    If you run the same query on November 24, 2006, records with a ContractStart of November 1, 2006 will be included because the start date is over 20 days ago by then.

Posting Permissions

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