Results 1 to 3 of 3

Thread: 2 questions

  1. #1
    Join Date
    Aug 2004
    Location
    Cinci, OH
    Posts
    49

    Unanswered: 2 questions

    Hey all,

    I am still learning so please bare with me.

    Question 1:
    I have 17 select queries that I am running from the same table. they all run off of a 'between [start date] and [end date]'. Can I run all of them off of one report? If so how would I do that and still include the 'between [start date] and [end date]'?


    Question 2:
    When I run one of these queries I use the 'between [start date] and [end date]' so I will enter 03/01/2006 and 03/31/2006. The results will only provied dates to 03/30/2006. For me to grab the dates of 03/31 I have to use the end date of 04/01/2006. What would cause that? My other database does not do that.


    Thank you very much in advance for any answers, suggestions, or information that you can provide.

    Richard.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Question 1:

    17 queries off one report that all use the same criteria? Are all the queries structurally the same? Are they AND or are they OR? Either way, you can probably accomplish the same thing in one query, then you have no problem with the one report.

    You can also apply the "Where Clause" as a Filter to the report.

    For example:
    Code:
    Select * From tblSource Where
    [MyFirstDateField] Between #1/1/2003# And #1/31/2003# And
    [MySecondDateField] Between #1/15/2003# And #1/28/2003#;
    You can put as many ANDs as you want. If you throw in some ORs, you'll need to group with ( ), but the format remains.

    Question 2:
    Probably has to do with the time (whether or not the time is part of the actual data). Technically, a value is not BETWEEN the bounds if it is equal to one of the bounds.

    Hope this helps.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the reason for the between date failing is almost certainly that you cut off point is a date, whereas the value being comapred to is a date/time

    so when you specify a between 01/mar/xxxx and 31/mar/xxxx
    is effectively that you are asking the db engine to return all rows which match inbetween
    01/mar/xxxx 00:00:00 and 31/mar/xxxx 00:00:00
    whereas what you probably want is
    01/mar/xxxx 00:00:00 and 31/mar/xxxx 23:59:59
    so the soultion is to either change the colun to a date type, or specify a time with it.

    as regard the first question
    if you are running the queries using the same date banding then it would make sense to pull the vakues for the dates from a parameter form

    eg
    forms!frmparameters!txtStartDate
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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