Formatting date gives bad results in query

Discussion in 'Microsoft Access Queries' started by Jeff Beveridge, Nov 3, 2010.

  1. I have the results of a query that I am just trying to export into
    Excel. However when you export from Access it INSISTS on adding "time
    information" to a "date/time" field even when you have the field set
    as "short date" at the table level. So I tried using the "Format"
    function by changing the field in the query grid to: myfield:
    format([field],"mm/dd/yyyy") .

    Now when I export the query it DOES remove the "time" information from
    the field like its suppose to. However now the results of the query
    are WRONG! I should mention that the two date fields in my query have
    criteria in them. The criteria is basically ">=#1/1/2009# and
    <=#12/31/2009# " in one field and the same in the other date field in
    the query grid but one line down (making it an "OR" statement). So the
    SQL in the WHERE clause looks like this:

    (Format([StartDate],"mm/dd/yyyy")>=#1/1/2009# And
    Format([StartDate],"mm/dd/yyyy")<=#12/31/2009#)) OR
    (Format([EndDate],"mm/dd/yyyy")>=#1/1/2009# And Format([EndDate],"mm/
    dd/yyyy")<=#12/31/2009#)

    As I said, this seems to screw up the results of the query by
    returning records outside of this date range in the results. The
    results are correct if I remove the format function but then it will
    export the two date fields with time information that Excel doesn't
    know what to do with.

    Anyone have any ideas on why this is happening or how to fix it?
    Thanks.
     
    Jeff Beveridge, Nov 3, 2010
    #1
    1. Advertisements

  2. Jeff Beveridge

    Rob Parker Guest

    Hi Jeff,

    Your problem comes from the fact that, when you apply a Format to a
    date/time field, the result is no longer a date/time (which is actually a
    number under the covers); it is a text string. You can get around this by
    applying the Format to the field in the SELECT clause, but omit it in the
    WHERE clause. If you're wanting to do this in the query grid itself, you'll
    need to put the date/time fields in the grid twice, apply the Format
    function to one, and turn off the Show checkbox for the other where you set
    the criteria.

    HTH,

    Rob

    "Jeff Beveridge" <> wrote in message
    news:...
    >I have the results of a query that I am just trying to export into
    > Excel. However when you export from Access it INSISTS on adding "time
    > information" to a "date/time" field even when you have the field set
    > as "short date" at the table level. So I tried using the "Format"
    > function by changing the field in the query grid to: myfield:
    > format([field],"mm/dd/yyyy") .
    >
    > Now when I export the query it DOES remove the "time" information from
    > the field like its suppose to. However now the results of the query
    > are WRONG! I should mention that the two date fields in my query have
    > criteria in them. The criteria is basically ">=#1/1/2009# and
    > <=#12/31/2009# " in one field and the same in the other date field in
    > the query grid but one line down (making it an "OR" statement). So the
    > SQL in the WHERE clause looks like this:
    >
    > (Format([StartDate],"mm/dd/yyyy")>=#1/1/2009# And
    > Format([StartDate],"mm/dd/yyyy")<=#12/31/2009#)) OR
    > (Format([EndDate],"mm/dd/yyyy")>=#1/1/2009# And Format([EndDate],"mm/
    > dd/yyyy")<=#12/31/2009#)
    >
    > As I said, this seems to screw up the results of the query by
    > returning records outside of this date range in the results. The
    > results are correct if I remove the format function but then it will
    > export the two date fields with time information that Excel doesn't
    > know what to do with.
    >
    > Anyone have any ideas on why this is happening or how to fix it?
    > Thanks.
     
    Rob Parker, Nov 3, 2010
    #2
    1. Advertisements

  3. On Nov 3, 6:52 pm, "Rob Parker"
    <> wrote:
    > Hi Jeff,
    >
    > Your problem comes from the fact that, when you apply a Format to a
    > date/time field, the result is no longer a date/time (which is actually a
    > number under the covers); it is a text string.  You can get around thisby
    > applying the Format to the field in the SELECT clause, but omit it in the
    > WHERE clause.  If you're wanting to do this in the query grid itself, you'll
    > need to put the date/time fields in the grid twice, apply the Format
    > function to one, and turn off the Show checkbox for the other where you set
    > the criteria.
    >
    > HTH,
    >
    > Rob
    >
    > "Jeff Beveridge" <> wrote in message
    >
    > news:...
    >
    > >I have the results of a query that I am just trying to export into
    > > Excel. However when you export from Access it INSISTS on adding "time
    > > information" to a "date/time" field even when you have the field set
    > > as "short date" at the table level. So I tried using the "Format"
    > > function by changing the field in the query grid to: myfield:
    > > format([field],"mm/dd/yyyy") .

    >
    > > Now when I export the query it DOES remove the "time" information from
    > > the field like its suppose to. However now the results of the query
    > > are WRONG!  I should mention that the two date fields in my query have
    > > criteria in them. The criteria is basically ">=#1/1/2009# and
    > > <=#12/31/2009# " in one field and the same in the other date field in
    > > the query grid but one line down (making it an "OR" statement). So the
    > > SQL in the WHERE clause looks like this:

    >
    > > (Format([StartDate],"mm/dd/yyyy")>=#1/1/2009# And
    > > Format([StartDate],"mm/dd/yyyy")<=#12/31/2009#)) OR
    > > (Format([EndDate],"mm/dd/yyyy")>=#1/1/2009# And Format([EndDate],"mm/
    > > dd/yyyy")<=#12/31/2009#)

    >
    > > As I said, this seems to screw up the results of the query by
    > > returning records outside of this date range in the results. The
    > > results are correct if I remove the format function but then it will
    > > export the two date fields with time information that Excel doesn't
    > > know what to do with.

    >
    > > Anyone have any ideas on why this is happening or how to fix it?
    > > Thanks.



    Thanks Rob, that seems to have done the trick.
    --Jeff
     
    Jeff Beveridge, Nov 4, 2010
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    query betwwen dates gives duplicate results

    Guest, Nov 24, 2003, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    162
    Guest
    Nov 24, 2003
  2. Ken N

    Action Query run from VBA gives no results

    Ken N, Feb 16, 2004, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    496
    John Viescas
    Feb 17, 2004
  3. JM

    Query to Linked ODBC table gives wrong results

    JM, Jul 5, 2005, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    218
  4. Guest

    Update query gives wrong results

    Guest, Aug 30, 2006, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    145
    John Spencer
    Aug 31, 2006
  5. Dan
    Replies:
    3
    Views:
    177
Loading...

Share This Page