Query Date issue

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

ryan.fitzpatrick3

I have a query where I have a date parameter where I do between [Start
date:] and [End Date:]. This works fine but when I dump this data into
excel the format comes up as 01/01/2008 02:31.07, how do I get the
format just to be 01/01/2008 only, no time stamp?

Ryan
 
For some reason the between [Start Date] and ([End Date] + 1) didn't
work. I get a data type mismatch error. for the format equation you
gave. I had

between [Start Date] and ([End Date] in the datefield and I put this
into a column next to it as an expression?

selectedDate: format([table/query name].[Field Name], "mm/dd/yyyy") 'I
put my field names in there'

Ryan



I have a query where I have a date parameter where I do between [Start
date:] and [End Date:]. This works fine but when I dump this data into
excel the format comes up as 01/01/2008 02:31.07, how do I get the
format just to be 01/01/2008 only, no time stamp?
Click to expand...
Ryan
Click to expand...

Without knowing your application better, it implies that NOW() is
being used to update this field and therefore the time is also being
maintained. You will either have to change the application to use
date() which may not be what you want or change the format of the
column on the spreadsheet. Or you may be able to set the criteria on
the date field and not output that column and create another column
that is output that has the following format:
selectedDate: format([table/query name].[Field Name], "mm/dd/
yyyy")

(I believe that is the way it would be expressed. Since it will be
going into excel, excel should treat it as a date just fine. (Air code
that has not been tested))

Another point. you you need to look into.
Since the datefield has time also in it, my guess is that you are
really NOT getting any records selected that have the date of the end
of your between criteria. Since you supply it 01/01/2008
00;00:00 as the criteria a record with a date of 01/01/2008 02:31.07
does NOT qualify as betwen.

If ALL records are ALWAYS updated with a true timestamp in the date
field you have the criteria on AND you never have anything posted as
of midnight then you could change the query criteria to the following
to get that last day included:
between [Start Date] and ([End Date] + 1)

This will get all of the records for that last date no matter what the
timestamp is.

Ron
Click to expand...
 
Ron,

I figured it out, I can't believe I forgot about this. I took date in
fieldA made new column in and did Left([fieldA],10), and that did the
trick. lol. Thank you, sorry about wasting your time.

Ryan


I have a query where I have a date parameter where I do between [Start
date:] and [End Date:]. This works fine but when I dump this data into
excel the format comes up as 01/01/2008 02:31.07, how do I get the
format just to be 01/01/2008 only, no time stamp?
Click to expand...
Ryan
Click to expand...

Without knowing your application better, it implies that NOW() is
being used to update this field and therefore the time is also being
maintained. You will either have to change the application to use
date() which may not be what you want or change the format of the
column on the spreadsheet. Or you may be able to set the criteria on
the date field and not output that column and create another column
that is output that has the following format:
selectedDate: format([table/query name].[Field Name], "mm/dd/
yyyy")

(I believe that is the way it would be expressed. Since it will be
going into excel, excel should treat it as a date just fine. (Air code
that has not been tested))

Another point. you you need to look into.
Since the datefield has time also in it, my guess is that you are
really NOT getting any records selected that have the date of the end
of your between criteria. Since you supply it 01/01/2008
00;00:00 as the criteria a record with a date of 01/01/2008 02:31.07
does NOT qualify as betwen.

If ALL records are ALWAYS updated with a true timestamp in the date
field you have the criteria on AND you never have anything posted as
of midnight then you could change the query criteria to the following
to get that last day included:
between [Start Date] and ([End Date] + 1)

This will get all of the records for that last date no matter what the
timestamp is.

Ron
Click to expand...
 
Back
Top