Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2010
    Posts
    39

    Unanswered: Date conversion & reports

    Hi guys,

    I'm trying to do a report on a set of data in access 2000. I need a selection of this data to pump out onto a report. On this report, I only want to view "today's" data.

    Now i've tried virtually everything I know, and it either list the entire data in date order, or give an empty record even though there is data for the current day (this is when it's not telling me there's various errors in the coding)

    I think my problem lies in the fact that my date field is a general date, therefore with the addition of the time aspect it is not functioning properly, I've tried introducing a convert to date from now format, but that does not seem to work either. I'm wondering if its coding I need, at which point i'm going to be a little out of my depth!

    Can anyone give me some ideas on what to do, as i've just both querying it and filtering the data after, and neither seems to be working.

    The idea of the report will be to show that day's data for a print at another site to check off against orders despatched.

    Thanks for any advice

    Colenzo

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how are you setting the date value
    are you using now() or date()?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2010
    Posts
    39
    The field is set as a Now() function. However, I just require today's date's worth of data.

    I feel a bit of a muppet, I'm afraid it's so cold here at work that my brain has frozen over along with my cup of coffee. It seems so obviously, but I just cant wrap my head around it!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you need the time element? if not then use date() not now()
    date is todays date
    now is the current date and time

    I'd suggest you fix your data before exploring work arounds
    assuming you don't want the time element you shoudl fix your forms, tabels and so on first
    then update the data

    update mytable set mydatevalue = cdate(format(mydatevalue,"mm/dd/yyyy")

    should do the dirty deed to esxisting data

    if you require the tuime element then chaneg your where clause to reflect that


    eg
    where format(mydatevalue,"yyyy/mm/dd") = format date(date(),"yyyy/mm/dd")

    you may need to tinker with the date formats, I'm always getting confused between the various mask characters used in VBA
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2010
    Posts
    39
    Unfortunately I do require the time element as the table is designed to monitor and extract data that is time and data dependant, hence the use of Now() in the table.

    What I need for the report however is just to show that day's worth of data, which even after trying the format.

    I'll give it another shot using what you have explained, however I'm fairly sure i've already been down this avenue.

    Failing this I'm just going to take the easy way out and split the date and time stamp into separate fields, for the purpose of our database, this will work just about fine... I just wanted to do it with a little more finesse!

    cheers m'dear!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in which case you are going to have to strip off the time element when using your where clause
    or
    where mydatecolumn between #datevalue1# and #datevalue2#
    or something similar
    bear in mind date literals in access should be us format dates (mm/dd/yyyy) encapsulated with the hash (#) symbol

    an alternative not very efficient approach would be to do a string comparison making certain the string dates (use format) is in ISO date sequence yyyy/mm/dd
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2010
    Posts
    39
    Thanks guys,

    In the end I just changed the field in the table to a date() which fixed the issue of the time. That has been changed into a different field (which actually previously held a date as well, so it was no major issues).

    Once this was set up it was just the simple matter of using Between [Enter Start Date] And DateAdd("d",1,[Enter End Date]) to set the dates up, and voila, it worked.

    That will teach me in future not to go over the top in what i'm trying to do and just keep it simple

    Thanks anyway

Posting Permissions

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