Microsoft Query?

[Mark J]

i am using the following criteria in the [DateTime] field;

=#3/9/2007# And <=#3/12/2007 11:59:59 PM#

i am using microsft query and excel to create pivot tables, i created an
odbc file to connect to my local server


[DateTime] [TagName]
[Value]
2007-03-09 00:00:00.000 PS002_Pump1.Daily_Runtime 1.1833188533782959
2007-03-09 00:00:00.000 PS002_Pump2.Daily_Runtime 1.4499822854995728
2007-03-09 04:59:59.947 PS002_Pump1.Daily_Runtime 0.16666463017463684
2007-03-09 04:59:59.947 PS002_Pump2.Daily_Runtime 0.13333170115947723
2007-03-09 09:59:59.893 PS002_Pump1.Daily_Runtime 0.51666033267974854
2007-03-09 09:59:59.893 PS002_Pump2.Daily_Runtime 0.48332741856575012
2007-03-09 14:59:59.843 PS002_Pump1.Daily_Runtime 0.86665606498718262
2007-03-09 14:59:59.843 PS002_Pump2.Daily_Runtime 0.86665606498718262
2007-03-09 19:59:59.790 PS002_Pump1.Daily_Runtime 1.083320140838623
2007-03-09 19:59:59.790 PS002_Pump2.Daily_Runtime 1.1333194971084595
2007-03-10 00:59:59.737 PS002_Pump1.Daily_Runtime 3.3332925289869308E-2
2007-03-10 00:59:59.737 PS002_Pump2.Daily_Runtime 3.3332925289869308E-2
2007-03-10 05:59:59.683 PS002_Pump1.Daily_Runtime 0.18333110213279724
2007-03-10 05:59:59.683 PS002_Pump2.Daily_Runtime 0.18333110213279724
2007-03-10 10:59:59.630 PS002_Pump1.Daily_Runtime 0.49999389052391052
2007-03-10 10:59:59.630 PS002_Pump2.Daily_Runtime 0.56665974855422974
2007-03-10 15:59:59.577 PS002_Pump1.Daily_Runtime 0.83332318067550659
2007-03-10 15:59:59.577 PS002_Pump2.Daily_Runtime 0.93332195281982422
2007-03-10 20:59:59.527 PS002_Pump1.Daily_Runtime 1.1166530847549438
2007-03-10 20:59:59.527 PS002_Pump2.Daily_Runtime 1.2833176851272583
2007-03-11 01:59:59.473 PS002_Pump1.Daily_Runtime 3.3332925289869308E-2
2007-03-11 01:59:59.473 PS002_Pump2.Daily_Runtime 6.6665850579738617E-2
2007-03-11 07:59:59.420 PS002_Pump1.Daily_Runtime 0.14999817311763763
2007-03-11 07:59:59.420 PS002_Pump2.Daily_Runtime 0.24999694526195526
2007-03-11 12:59:59.367 PS002_Pump1.Daily_Runtime 0.56665974855422974
2007-03-11 12:59:59.367 PS002_Pump2.Daily_Runtime 0.69999146461486816
2007-03-11 17:59:59.317 PS002_Pump1.Daily_Runtime 0.81665670871734619
2007-03-11 17:59:59.317 PS002_Pump2.Daily_Runtime 0.98332130908966064
2007-03-11 22:59:59.263 PS002_Pump1.Daily_Runtime 1.1833188533782959
2007-03-11 22:59:59.263 PS002_Pump2.Daily_Runtime 1.3666499853134155
2007-03-12 03:59:59.210 PS002_Pump1.Daily_Runtime 0.11666524410247803
2007-03-12 03:59:59.210 PS002_Pump2.Daily_Runtime 0.08333231508731842
2007-03-12 08:59:59.157 PS002_Pump1.Daily_Runtime 0.23333048820495605
2007-03-12 08:59:59.157 PS002_Pump2.Daily_Runtime 0.28332987427711487
2007-03-12 13:59:59.107 PS002_Pump1.Daily_Runtime 0.66665852069854736
2007-03-12 13:59:59.107 PS002_Pump2.Daily_Runtime 0.76665729284286499
2007-03-12 18:59:59.053 PS002_Pump1.Daily_Runtime 0.89998900890350342
2007-03-12 18:59:59.053 PS002_Pump2.Daily_Runtime 1.0166542530059814
2007-03-12 23:59:59.000 PS002_Pump1.Daily_Runtime 1.1999853849411011
2007-03-12 23:59:59.000 PS002_Pump2.Daily_Runtime 1.3833163976669312


what i would like to do is not hard code the date for the data i need, is
there a way to code the dates so that microsoft query
would pull those date from sql...ie;

[DateTime]

= CONVERT(VARCHAR(20), GETDATE()-5, 100) 12:00:00 AM and <=

CONVERT(VARCHAR(20), GETDATE(), 100) 11:59:59 PM

any ideas?

 

[gimme_this_gimme_that]

Ideas, create a view that fetches the data based upon the current date
and use MS Query to fetch from the view instead of from the table.

 

[Ami Patricia]

Ideas, create a view that fetches the data based upon the current date
and use MS Query to fetch from the view instead of from the table.

I have been trying to find a way to only bring the dates I have using MS
Query based on the current date. I was wondering how would I accomplish that
when the date field I have is already grouped (max). This is what I've
tried...

=TODAY()-120

UNDER NEATH THE DATE'S CRITERIA

=TODAY()-120

IN THE NEXT COLUMN NEXT TO THE GROUPED DATE COLUMN

I'd appreciate a push in the right direction! Thanks for any tips.
Ami