Formatting date gives bad results in query

[Jeff Beveridge]

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]

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]

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

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