Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Problem with Date (Access 2000)

    I have a database table named tblStock that accepts output from a VB form, The data is written to the tblStock and I have an extra field that is in my tblStock that is not sent by the VB form action...it is fldDate, which I formatted in Access as ShortDate, and the default value is NOW(), so that when the tblStock is populated by the write action of the VB command button the fldDate populates with that day's date because of the NOW() command in the fldDate Default property.

    Well, I thought everything was going to work out fine, however...when I try to query the tblStock for products sold on todays date, using the NOW() or even the #03/11/04# in the criteria section of the property grid for fldDate, it will not give me results. I can get results if I type: Day([fldDate])=11

    But..........that would give me anything sold on the 11th of any month. Why won't it accept my criteria? In the table I am querying called tblStock, the format of the fldDate is Short Date, and default value is NOW(), but out front when I click on the field fldDate, of course it shows Date and Time....and I think this is where my woes begin. When I use this field in my query, it must still be seeing a Date and Time, and of course nothing was sold after the store closes and I'm tallying up the days totals...so is it seeing the "current date and time" of my computer and nothing was sold today because it would be listed of course as the same date, but not the same time as the sale occured...

    How do I get the date/time format off the table fld called fldDate. I do not want the time in it. I have not asked for date/time in the format...just short date. Even in my query properties for that field, I've asked just for Short Date, no time...why won't it go away...Is it because I'm using the Now() in my table as default for the date field...what else could I use instead to get todays date to automatically populate the field without the time...Access doesn't accept the Today() command, nor does typing Date() seem to take the time off the field, because when clicked on in datasheet view, it still shows date and time together...

    I hope this doesn't sound confusing...if you can help, I'd sure appreciate it.

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query Problem with Date (Access 2000)

    Hi again,

    I think your problem stems from using Now() rather than Date() - the Now is actually a timestamp accurate down to a few milliseconds, where date is a date with the time set to midnight (12:00am or 0:00 depending on your time standard). Thus when you put in #3/11/2004# you are really saying March 11, 2004 00:00:00, and the field doesn't match with something you did at 10:11:12 AM. If you use Date (in VB you don't need the parens) you avoid those problems. The bottom line is that Date/Time fields always have a time associated with them and if you are trying to do equality matches, you have to have a consistent treatment. Another way to solve your problem is to use a criteria that says Between #3/11/2004# And #3/11/2004 23:59:59.9# but it's lots of trouble. Hope this clarifies things a bit.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Problem with Date (Access 2000)

    Setting Short Date format only determines how the date is displayed on your screen, or on paper, but it does not alter the underlying data.

    The Now() function returns the date + time, as you have found. The Date() function returns the current date, without the time.

    So you will have to do several things:
    1. Set the default value to Date() instead of Now().
    2. Use Date() instead of Now() in the criteria line.
    3. Use an update query to get rid of the time part in existing records:
    - Create a query based on tblStock.
    - Add the fldDate field to the query design grid.
    - Select Query | Update Query.
    - In the 'Update to' line, enter Int([fldDate])
    - Select Query | Run, and confirm that you want to update the records.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem with Date (Access 2000)

    WendellB

    Hi back to you!
    Thank you...That was it. What I failed to do was to check the default line. After I changed the properties in the tblStock field of fldDate to Date() I was still clicking on existing records and seeing the Date turn into Date/Time, however, on the default add new record line, the date was staying in DATE only format...so it does work, and once I did a query using the Date() command as my criteria, it works like a charm. I had assumed it would convert existing records to Date() format in my table, and it only converted from the add a new record line downward...now that I have it, it will work from here on out just fine...right now it's in the project stage, so....I just deleted old records and in all new records written from VB form to the table...well they query beautiful...and even changed some of the dates once they were established just to see if it gave errors in query, but it did not...Thank you Wendell, as always...
    I salute you! <img src=/S/salute.gif border=0 alt=salute width=15 height=20>
    Thanks bunches and bunches
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem with Date (Access 2000)

    Hi Hans!

    Thank you. If these were pertinant records that mattered I would walk through these steps to alter existing records and make it work, however thankfully this is still in the project stage, so I did what Wendell had said and just used the Date() and deleted test data already written to the table...works great.
    Thank you to all you guru's who answer so quickly, and so completely...As always

    A sharp salute to you all!!! <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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