jonnyboyo
Posts: 15
|
| Posted: 06/28/2004, 11:58 AM |
|
I'm building an online magazine and I need to show the current issue on the default page. I'm trying to display only articles and news for the current month and as yet I'm unsuccesfull in displaying just articles and news items submitted for the month.
The table is Articles and the field is ArtDate, being the issue date. I've tried various criterea as expressions but so far can't pull the month out of the date field to get the page to display just items for the current month.
The idea is to automatically display the current month as it changes.
I'm working in ASP and using a MySQL database.
If anybody can give me the criterea or suggest a method of doing this I'd be very gratefull.
|
 |
 |
peterr
Posts: 5971
|
| Posted: 06/28/2004, 12:11 PM |
|
I don't have MySQL on the machine where I have ASP, but I tested couple methods with ASP and MS Access:
1. In the Data Source of the grid add a parameter, change its "Condition Type" to Expression, then enter the following expression:
Month(date_add) = Month(Date())
I'm not sure if this will work with MySQL and possibly MySQL documentation can provide some clues on how they recommend extracting the month from a date.
2. Do not use any parameters in the DataSource, but add such "Before Build Select" event to your grid:
articles.DataSource.Where = "Month(date_add)=" & Month(Date)
('articles' is the name of my sample form)
Here again, you may need to replace Month(date_add) with some MySQL function (refer to their docs), but Month(Date) is ASP code and should work without changes.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Tuong Do
|
| Posted: 06/28/2004, 4:41 PM |
|
Hi Jonnyboyo,
Do not use any parameters in the DataSource, but add a "Before Build Select"
event to your grid:
<%
Function ToSQLDate(adate)
' If the date is 01 Jul 2004 then it will return '2004-07-01'
ToSQLDate = "'" & Year(adate) & "-" & Month(adate) & "-" & Date(adate)
& "'"
End Function
Dim FirstdateofMonth, LastDateofMonth
FirstDateofMonth = DateSerial( Year(Date) , Month(Date), 1 )
LastDateofMonth = DateSerial( Year(Date) , Month(Date) + 1 , 0 )
articles.DataSource.Where = "ArtDate >= " & ToSQLDate(FirstDateofMonth) & "
AND ArtDate <= " & ToSQLDate(LastDateofMonth)
%>
|
|
|
|
DonB
|
| Posted: 06/29/2004, 8:15 PM |
|
Why don't you store the month when the article is filed? This will be far
and away the most efficient approach, as the lookup becomes "indexable" by
the database. Probably not an issue now but consider 20 years from now when
the magazine has 10 million subscribers 
Seriously, though - storing the month makes the solution trivial.
--
DonB
logging at http://www.gotodon.com/ccbth, and blogging at
http://ccbth.gotodon.net
"jonnyboyo" <jonnyboyo@forum.codecharge> wrote in message
news:640e06a5101205@news.codecharge.com...
> I'm building an online magazine and I need to show the current issue on
the
> default page. I'm trying to display only articles and news for the current
> month and as yet I'm unsuccesfull in displaying just articles and news
items
> submitted for the month.
>
> The table is Articles and the field is ArtDate, being the issue date. I've
> tried various criterea as expressions but so far can't pull the month out
of
> the date field to get the page to display just items for the current
month.
>
> The idea is to automatically display the current month as it changes.
>
> I'm working in ASP and using a MySQL database.
>
> If anybody can give me the criterea or suggest a method of doing this I'd
be
> very gratefull.
>
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
johnnyboyo
|
| Posted: 08/06/2004, 8:46 AM |
|
Quote peterr:
2. Do not use any parameters in the DataSource, but add such "Before Build Select" event to your grid:
articles.DataSource.Where = "Month(date_add)=" & Month(Date)
('articles' is the name of my sample form)
Here again, you may need to replace Month(date_add) with some MySQL function (refer to their docs), but Month(Date) is ASP code and should work without changes.
Petterr,
Sorry to take so long on this, now turned into two magazines and a company website.
Your option 2 works a treat. I've changed the publication date field to PubDate and providing a date is entered with every record then at midnight at the end of every month everything changes to the current month.
The code I'm using in the "Before Build Select" is:
b]articles.DataSource.Where = "Month(PubDate)=" & Month(Date) where articles is the name of the grid. It also workd on the menu grids as well.
Thanks again for your help, I'll publish the Url's when it's all online.
Jonnybyo
|
|
|
|
|
