Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Too much information (Access 2000)

    I have a database with last year data information. I need all of the data but I only want to see a specific range of data and when I go to create a query or generate a report from the data it give me this years and last years data.

    I am using the following because the date is has the date and time.

    Field - Date: Format(DateCreate([GATE_SUCCESS_TIME]),"mm/dd/yyyy")
    Between [Start Date] and [End Date]

    I have also use the criteria Between #07/01/2002# and #07/16/2002#


    Function DateCreate(datex) As Date
    Dim hourx As Integer
    hourx = Hour(datex)
    'MsgBox hourx
    If hourx >= 20 And hourx <= 23 Then
    DateCreate = datex
    ElseIf hourx >= 0 And hourx < 7 Then
    DateCreate = DateAdd("d", -1, datex)
    End If

    It gives me info for 2001 and 2002

    I appreciate your help <img src=/S/butterfly.gif border=0 alt=butterfly width=15 height=15>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much information (Access 2000)

    In the query, remove the format part.
    Field - Date: DateCreate([GATE_SUCCESS_TIME])
    Right click somewhere in this column and select Properties.
    In the General tab, set the format property to mm/dd/yyyy
    Dont forget that if you want to include the dates with the day of End Date, you have to use :
    Between [Start Date] and [End Date]+1
    Francois

  3. #3
    New Lounger
    Join Date
    May 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much information (Access 2000)

    It did not work. When I took away the format in the formula and changed the criteria No data returned.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much information (Access 2000)

    I'm curious about your statement: "Dont forget that if you want to include the dates with the day of End Date, you have to use :
    Between [Start Date] and [End Date]+1". I don't really understand why ... I thought the Between operator was inclusive. It's not?

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much information (Access 2000)

    As the field is a date/time field with time stamp.
    Between #07/01/2002# and #07/16/2002# returns the dates between 07/01/2002 00:00 and 07/16/2002 00:00
    So every date with 07/16/2002 + some hours and minutes is greater then 07/16/2002 00:00
    You can use between #07/01/2002# and #07/16/2002# + 1
    or between #07/01/2002# and #07/16/2002 23:59#
    Francois

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much information (Access 2000)

    Attached a little database with a sample of what you ask.
    Look at query1, maybe you'll have to change the Between function in US dates as my system use dd/mm/yyyy
    Attached Files Attached Files
    Francois

  7. #7
    New Lounger
    Join Date
    May 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too much information (Access 2000)

    It worked I forgot to take the word FORMAT out and I was able to use the between without +1 and got the data I needed. Thanks so much for your help. I greatly appreciate it.

    Evette <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Too much information (Access 2000)

    You wrote:
    >>Dont forget that if you want to include the dates with the day of End Date, you have to use :
    Between [Start Date] and [End Date]+1<<

    Francois,

    I'm sorry, but I don't agree with this. I'm assuming you are trying to handle those date values which contain a time component. In that case, you would need to use "[dateField]>=[Start Date] AND [dateField]< [End Date] + 1".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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