query question

[rkferri]

I'm trying to display information from my access database by only displaying fields that are in the database for 90 days. I have a creation date which is the field I'm doing the 90 day compare on. Is there a quick and easy way to do this?

Thanks in advance,
Kelly

 

[philhege]

I think you can do math with the date() function, like

WHERE MyDateField > date()-90

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[Bammy217]

hi,

alternatively in ACCESS you can use the now() for the same as has been shown above e.g.:

  SELECT col1, col2, col3
  FROM MyTable
  WHERE MyDate >= now()-90

or you can compute the date using coldfusion and then query using that date e.g.:

 <cfset MyDateVariable = DateAdd("d", -90. now()) />
 <cfquery name="MyQuery" datasource="MyDB">
  SELECT col1, col2, col3
  FROM MyTable
  WHERE MyDate >= #MyDateVariable#
 </cfquery>

We never fail, we just find that the path to succes is never quite what we thought...

 

[imstillatwork]

I don't know about access, doing math on a date, assumes days or what?

mysql has date function like data add, that work very similar to coldfusions dateAdd. Anything that can be done with SQL probably should be.

the only time I use CFML to insert or update a date field is when they HAVE to be the same.

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[philhege]

Adding to or subtracting from now() assumes days.

So, if now() is Thursday, now()-3 is Monday.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[imstillatwork]

just o I know, because a lot of the sites i maintain use access, that i did't build, how would you make it years or months?

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[Bammy217]

Kevin,

you just work out how many days you would like to go back. as far as i'm aware in ACCESS there is no function for doing months or years ... but then i could be wrong as I'm a MySQL / MS SQL user myself.

We never fail, we just find that the path to succes is never quite what we thought...

 

[r937]

Access has -- wait for it -- a DateAdd function!!

http://www.bristol.ac.uk/is/learning/documentation/acc-r4/acc-r4.html#toc0440

r937.com | rudy.ca

 

[imstillatwork]

I figured it would, and I've probably used it in the past, but the way they showed the date math above made my wonder...

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!