Results 1 to 5 of 5

Thread: .accdb Date()

  1. #1
    Join Date
    May 2008
    Posts
    5

    Unanswered: .accdb Date()

    hello-

    I appreciate the help on an older problem that I posted several days ago. Perhaps you can help out again.
    my company is using Sharepoint Services. It has an auto generated field 'Created' that takes a time and date stamp of every addition of new records.
    on sharepoint, im able to filter by date using [Today]-1 and i get yesterdays results.
    When I link the table to my access database, i try to do a query on 'Completed' using the Date()-1 and get no results. I tried Now()-1 and no results, I think I was yelled at by ACCESS.
    I've dug around help screens and can't find an example of how to query a date & time based on the system date.
    I hope I made sense.
    Any help is appreciated.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    There is nothing wrong with the expressions Date()-1 or Now()-1. They are both valid Access expressions meaning yesterday and this time yesterday respectively.

    You first mentioned "Created" and then subsequently referred to "Completed". Could this be the problem? Perhaps Access is not recognising "Created" (or Completed) as a date field? You might have to use CVDate([Created]) - 1 or something similar to coerce the field to a date.

    Not entirely sure.

    Cheers

    ST
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2008
    Posts
    5

    try again...have come down

    yeah...so if i could live in any country in the world, I would be in the Netherlands! So, now that I'm sober, lemme restate the problem.

    I'm trying to make a query that uses a field that has TIME and Date in it. This field is automatically completed by Sharepoint..and was built by Sharepoint.
    I want to use that field as a critera for a query. I want all of yesterdays new records.
    In Query design, under critera: =date()-1 and I get NO Results. But there are 40+. I've also used =Now()-1 and get no results.
    I'm thinking i don't understand something about the Time stamp in the field.
    When I use Sharepoint, I try to filter on that field and am only able to sort on a date AND time...not just date. I don't give a damn about the time, but since Sharepoint makes me use it, I have no choice.
    Thanks for your time.
    Last edited by beuferd; 05-16-08 at 13:35.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    = date()-1 will only find records with time 00:00:00
    do you have any?
    = Now()-1 will only find records with time exactly 18:26:33 (happened to be my local time as i typed that)
    do you have any?

    have a go with
    Between date()-1 and Date()

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    May 2008
    Posts
    5

    Talking tha'ts the one!

    bam!

    man, u guys saving my life!

    thanks so much.

Posting Permissions

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