PC Review


Reply
Thread Tools Rate Thread

Formatting date gives bad results in query

 
 
Jeff Beveridge
Guest
Posts: n/a
 
      3rd Nov 2010
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.
 
Reply With Quote
 
 
 
 
Rob Parker
Guest
Posts: n/a
 
      3rd Nov 2010
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.


 
Reply With Quote
 
 
 
 
Jeff Beveridge
Guest
Posts: n/a
 
      4th Nov 2010
On Nov 3, 6:52*pm, "Rob Parker"
<(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
>
> news:(E-Mail Removed)...
>
> >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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Bad,bad,bad: NO Sync software can handle RENAMED and MOVED files ! Sebastian Kaist Windows XP General 1 14th Aug 2006 07:14 PM
Bad,bad,bad: NO Sync software can handle RENAMED and MOVED files ! Sebastian Kaist Windows XP Help 1 14th Aug 2006 07:14 PM
internet bad bad bad struth Windows XP General 1 1st May 2004 12:03 AM
{OT} Google is bad bad bad Freeware 13 28th Apr 2004 03:46 PM
Maxtor 200GB Harddrive -- clanking noise -- bad, bad, bad Tom Scales Storage Devices 15 2nd Mar 2004 09:07 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:39 PM.