Results 1 to 13 of 13
  1. #1
    rockymtn is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2011
    Posts
    7

    Date() v. Now() Formatting with Dates m/d/yyyy h:nn

    I receive a CSV file from another company and take that data and upload it to my company's MS Access 2010 database. The upload and everything works fine, but when I try to run a query on the Date of Service I can never get the Now() or the Date() queries to generate any results. The format for the date field is:

    m/d/yyyy h:nn

    This is what automatically generated when I imported the data and I cannot change it through design view. I am guessing I could run an update query to change this, but was wondering if the format would be the reason I cannot get either the Now() or Date() queries to work.



    Ultimately, I wanted to create a simple query that generated the next day schedule: Now()+1

    Any help would be appreciated.

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, Date() returns the date only, Now() returns the current date and time. It's a guess, but I suspect what you want is

    WHERE DateValue(YourFieldName) = Date() + 1
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    rockymtn is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2011
    Posts
    7
    Whenever I use Date() or Now() I get absolute nothing for results. However if I enter the date in it as this for example: >#3/9/2011# I get the results needed for the future date which is the next day (the most recent records I have appended). However, if I put in =#3/9/2011# I get nothing.

    Would you think this has something to do with the formatting of the date?

  4. #4
    rockymtn is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2011
    Posts
    7
    If I enter =Date() I get nothing, but if I enter >Date() I do get the schedule I am looking for. Is there an explanation for that? I am sure there is, but it is just slipping from me. This also work with >Date()+1, but not =Date()+1

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you notice that I used the DateValue() function to convert your date/time value to date only, so it could be compared to a date criteria?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    rockymtn is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2011
    Posts
    7
    Would this change my field and get rid of the times? The times are necessary for scheduling.

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, it won't change data in the table. It just converts it for use in the query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    rockymtn is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2011
    Posts
    7
    When I still change the format in the query it does not give me any results unless I use the > or < instead of the =.

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you post the SQL of the query?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    rockymtn is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2011
    Posts
    7
    SELECT nm.[Case], nm.[Case Date], nm.tech, nm.[Facility], nm.[Type of Procedure], nm.[Remote]
    FROM nm
    WHERE (((nm.[Case Date])=Date()));

  11. #11
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You didn't use the DateValue function as I demonstrated. Try

    SELECT nm.[Case], nm.[Case Date], nm.tech, nm.[Facility], nm.[Type of Procedure], nm.[Remote]
    FROM nm
    WHERE DateValue(nm.[Case Date])=Date()
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    rockymtn is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2011
    Posts
    7
    Okay that did it....thanks!

  13. #13
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Conditional Formatting date due color
    By Desstro in forum Programming
    Replies: 1
    Last Post: 12-08-2010, 11:12 PM
  2. Formatting a date
    By NISMOJim in forum Queries
    Replies: 7
    Last Post: 09-17-2010, 05:44 PM
  3. Replies: 2
    Last Post: 08-17-2010, 01:10 PM
  4. Formatting dates
    By John Southern in forum Programming
    Replies: 2
    Last Post: 05-21-2010, 11:19 PM
  5. Replies: 1
    Last Post: 06-11-2009, 05:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums