Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Question Unanswered: querying a datetimestamp field

    I have an access database where i need to create a daily report that collates all the data and creates a summary report.

    I have tried to query using a form which if only one days date works fine.

    However when i try to query with more than say yesterday in the table i get a null return

    i have tried to extract the data into another table to create the report but nothing is returned even although there are over 22k items in the table.

    any suggestions gratefully received.


    ps i am using calander control to search the date



    JIm

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Does the Date/Time field include the time part ? If so 11/23/2009 is different from 11/23/2009 11:35:19. You can use Format([Field],"mm/dd/yyyy") to keep only the date part for your computations.
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Posts
    2

    Thumbs down Querying a Datetimestamp field

    I have tried as you suggested and it formats the date into dd/mm/yy which should under the circumstances be sufficient to enable a query to be run.


    Attached is the sql of the query but it still returns nothing


    SELECT Format([Active Date],"dd/mm/yy") AS [Date], [tblWinterPlanning-Import].[Case Number], [tblWinterPlanning-Import].[Final Outcomes], [tblWinterPlanning-Import].Location
    FROM [tblWinterPlanning-Import]
    WHERE (((Format([Active Date],"dd/mm/yy"))=#11/17/2009#));


    Any help gratefully received

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Notice that Sinndho said: mm/dd/yyyy (note the 4 y's). You only set it up for 2 y's so it doesn't jive. 11/17/09 is a big difference than 11/17/2009 (for MSAccess) and probably why you aren't getting returns.

    Be careful on date details. Little things like that will get you every time (for example: if you made it a user entry date and the user only entered 09 (which would currently work they way you have it) But you'll need to spell out the format they should use in the prompt. (but maybe MSAccess 2007 fixed this.)

    AND
    This:
    WHERE (((Format([Active Date],"dd/mm/yy"))=#11/17/2009#));

    should be:
    WHERE (((Format([Active Date],"dd/mm/yy"))=#" & 11/17/2009 & "#))"
    Last edited by pkstormy; 11-27-09 at 03:46.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why are you telling it to format the date ad dd/mm/yy, I agree its the one true date format but you are comparing a string value to a #date# value
    its doenst' make sense to me.

    as you have rightly guessed its the time element from the timestamp which is causing problems.

    Code:
    WHERE (((Format([Active Date],"dd/mm/yyyy"))="17/11/2009"));
    an alternative may be to build the SQL and use the BETWEEN clause
    and use the dateadd function to build your date bracket

    if you need multiple dates then you will have to use the BETWEEN clause and use a date comparison rather than a string comparison, but to get round the time element you need to be a bit smarter
    The database time runs from 00:00:00 to 23:59:59 then it starts a new day
    so not specifying the time element means that you are cutting off records posted on the same day posted after midnight in the morning
    so you need to specify the time bit as 23:59:59
    one dodge is to add one day to the end day using the dateadd function, of if you are feeling macho add 23 hours, 59 minutes and 59 seconds or 0.99998842593 of a day
    Last edited by healdem; 11-27-09 at 04:22.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Everything else aside, you need to pay attention the change Healdem made but didn't really mention:

    WHERE (((Format([Active Date],"dd/mm/yyyy"))="17/11/2009"));

    You're formatting your date as dd/mm/yyyy, and then using

    WHERE (((Format([Active Date],"dd/mm/yy"))=#11/17/2009#));

    and there is no month represented by the number 17 as you're trying to do with this code.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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