Query dates month from current date

[illdill]

Im trying to return results for a month from before the current date and indefinately into the future, but under operational circumstances, I should have no records in the future. For troubleshooting purposes, i do.

SELECT * FROM articles WHERE ((([ID] Mod 2)=True)) AND articledate > (date() - 30)

This is what I assumed, but I get strange results. I have records (in the DB) in the future and records in the past (within the 30 day range) however, only records within the current month are showing. Records within the 30 day range, but in the previous month are not showing.

Maybe (- 30) doesn't work against a date data type, but that's why I am asking.

Thanks for any help...

 

[boblarson]

Use Date Add instead:

articledate > DateAdd("m",-1,Date())

 

[illdill]

"m",-1 does this mean month -1? I ask because although it is very easy to cut and paste, I like to understand what is happening. Thanks so much for your input...

 

[boblarson]

The Date Add function is

DateAdd (The time frame as string, the number to add/subtract, the date to add/subtract to/from)

The "m" does mean month and you can use

"d" - days
"m" - months
"yyyy" - years
"w" - weeks
"h" - hours
"n" - minutes
"s" - seconds

and the -1 is subtracting instead of adding.

 

[illdill]

This is producing the same results. I am not getting results for dates such as 5/23/2008 which fall within the 30 day before current date period...

Thanks again for your descriptive response...

 

[boblarson]

can you post the db (or at least the applicable parts)?

 

[illdill]

articlesIDarticletitlearticletextarticlelinkarticledatearticleimagearticleold11Test 10Hello WorldHttp://illdill.org5/23/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
12Test 2Texthttp://illdill.org6/03/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
13Test 3Texthttp://illdill.org6/13/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
14Test 4Texthttp://illdill.org6/13/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
15Test 5Texthttp://illdill.org6/13/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
16Test 7Texthttp://illdill.org6/13/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
17Test 8Texthttp://illdill.org6/13/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
18Test 9Texthttp://illdill.org6/13/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
19Test 10Texthttp://illdill.org6/13/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
20Test 11Texthttp://illdill.org6/13/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
21Test 12Texthttp://illdill.org6/13/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
22Test 13Texthttp://illdill.org6/13/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
23Test 14Texthttp://illdill.org6/13/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
24Test 15Texthttp://illdill.org6/13/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
25Test 16Texthttp://illdill.org6/23/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
26Test 17Texthttp://illdill.org7/23/2008http://atheists.local/atheistsdb/news/images/aasymbol.gif
27Test 19Texthttp://illdill.org7/03/2008http://illdill.org/All/ildlgpcfad.gif
28Illdill . OrgGreat Websitehttp://illdill.org6/18/2008http://illdill.org/All/ildlgpcfad.gif
29Test 21What a great storyhttp://microsoft.com6/15/2008http://woodstockpreservation.org/_borders/flyaway51.gif030Test for low dateTexthttp://illdill.org5/5/2008http://illdill.org/All/ildlgpcfad.gif031Test for low dateTexthttp://illdill.org5/20/2008http://illdill.org/All/ildlgpcfad.gif032Test for low dateTexthttp://illdill.org6/12/2008http://illdill.org/All/ildlgpcfad.gif033Test for low dateTexthttp://illdill.org6/5/2008http://illdill.org/All/ildlgpcfad.gif0


Query I am using:

SELECT * FROM articles WHERE ((([ID] Mod 2)=True)) AND articledate > DateAdd("m",-1,Date())

link to results page:

http://illdill.org/AtheistsDB/News/results.asp

The Mod 2 is because I am providing two columns of results and I needed a way to provide different results for each column and I thought odd/even ID would be a good way. This has worked well, now I am stuck on the 1 month issue...

THANKS....

 

[boblarson]

Not sure unless you might need to use square brackets around articledate [articledate] and perhaps articledate is not a true date/time field?

 

[illdill]

Yes, article date is designated as a text field... I wish I knew how to bring up a calander to select a date from, but for now I am depending/requiring dates being entered in the short format...

ps.. I am using FP DRW against an access db

 

[boblarson]

How about this:

SELECT * FROM articles WHERE ((([ID] Mod 2)=True)) AND CDate([articledate]) > DateAdd("m",-1,Date())

 

[illdill]

Ok, have the recent query in effect, but am producing no different results..

 

[boblarson]

Well, I'm out of ideas.

 

[illdill]

Odd.. I included a screenshot of the query in FP and the results page is not reflecting it...

 

[illdill]

Well, thanks for your help, what is CDate..?

 

[boblarson]

Converts text to a date.

 

[illdill]

http://illdill.org/Atheists/News/result1.jpg

Here is the link showing the new query wich makes me wonder if the resultsw are not reflecting the changes as well as I have included this image on the results page and it is not loading... just the same results...

Thanks for the CDate....

 

[EMP]

Yes, article date is designated as a text field
..............
..............
ps.. I am using FP DRW against an access db

Suggestion:

In Access, change article date to Date/Time data type in table structure - this way you don't need any date conversion functions.

Test your SQL statements in Access to see which SQL statement works.


Hope this helps.
^