Results 1 to 8 of 8

Thread: Query problem

  1. #1
    ewatson
    Guest

    Query problem

    Hi,

    I have a table with just dates and names. from there I made a query that has both fields and then creates an expression which subtracts 35, 45, 60, and 90 days from the date and checks to see if they fall between the user entered dates. My problem is I only want the expression field to display the calculated date if it falls within the user entered between dates i.e. if the date - 35 field doesn't meet the criteria then I want the field left blank (no date shown) if the -45 field meets it I want the date shown and then the -60 and -90 dates will be blank. I hope I explained this ok. If you look at the attached Database use the dates 6/5/01 and 6/17/01

    Thanks,
    Ed

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    Have you tried something like this?
    35: IIf([Week start date:]<=[Trip]![Trip date]-35,IIf([Week end date:]>= [Trip]![Trip date]-35,[Trip]![Trip date]-35,""),"")
    I did and I thought it would work but the results were not correct. Maybe someone can point out why?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query problem

    If you want to subtract a number of days from a date to return another date, use the DateAdd function. Anything else is iffy at best.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem

    That doesn't seem to be the problem Charlotte. I can't understand it - if you test the database supplied then the queries shows dates which should be ruled out by the query definition. It's weird.

  5. #5
    ewatson
    Guest

    Re: Query problem

    Dave,
    I have solved som of the problems with that date subtracting Database however when I run the "sort by date" query with the dates 6/1/01 and 6/17/01 it reads N/A under 60 days for Darcy and Adams when it should say "60 Days" (See minus Query to verify that dates pr 60 fall within the range) I don't think that the start and end date entered for 35 days is being used in 60 days. Is there another way I should refer to those items in the 60 expression under the Sort By Query other than [Start Date] And [End Date]?

    Thanks for help
    Ed

  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 problem

    Hi Ed,
    I'm attaching a version of your database with just one query in it. It has 2 date parameters specifically set up in it and seems to work OK. I confess I'm not a 100% sure what was happening with your original query - it seemed to work OK if I entered 01/06/2001 and 17/06/2001 as the dates but not if I entered 1/6/01 and 17/6/01.
    Try it out and let me know if it helps.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    ewatson
    Guest

    Re: Query problem

    rory,
    You the man!! I wonder why you can't use the 01 in the date though. If you ever find a way around it let me know. Thanks for all the help.

    Ed

  8. #8
    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 problem

    Ed,
    You can use 01 for the year in the query I put in the db (just tested it with 1/6/01 and 17/6/01 and it worked), it just didn't seem to work very well in the original query for some reason.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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