Hi ladies and gentlemen,

I have encountered a problem regarding Drop-Down Box on Date (Month). For example, "Jan - 2004", "Feb - 2004", "Mar - 2004", etc...

When the user click on a particular Month, a series of data will be retrieve from the access database (according to the Month selected) and show up in asp page...

I have everything in place, except for the coding/implementation on the Drop-Down box... The dates in the database is in daily event, but my Drop-down box is in Monthly selection... So, how can I call up the particular dates (and gather them nicely in Month format) that I want from the database?

Sorry for my poor English, really appreciate bros who can help me here... Urgent case... Thanks in advance...

AFAICU, your requirement is to get those events whose month is chosen month. You can use the Month function of access for this. Here is a sample query.

SELECT EventName, EventDate FROM tblEvents WHERE Month(EventDate)=" & Request.Form("cboEventMonth")

Where cboEventMonth is the name of the combo where you place months.

Hope this would help.

Hi madhukp,

May I know whether I need to add any ASP coding on the drop down box? How shall I go about in the option value("??")? What shall I put?

<select name="ViewDate" size="1">
<%
if ViewDate <> "" then %>
<option selected value="<%= ViewDate %>"><%= ViewDate %></option>
<% end if %>
<option value="">Please choose one</option>
<option value="????????????">Oct 2004</option>
</select>
<input type="submit" value="Submit">

You may put

01-2004 for Jan 2004
02-2004 for feb 2004
03-2004 for mar 2004
.....
.....
01-2005 for Jan 2005

etc. etc.

Then at the processing page you can split the value of Request.Form("ViewDate") at the character - (hyphen) to get month value and year value. Please refer the std. function Split.

Hi HelpMe, (Trying to help you;))

Is that dropdown box a static or dynamic? How many values are you looking to show there in that( min and max limit)?

If static, I would say you better split that dropdown into 2 (Month and Year)Check out this code. Just copy paste this in an empty asp file and browse that to see how it behaves. Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Thanks madhukp and happygv for the prompted reply :)

So happygv, does that mean that I need to change my Month, Day and Year to 3 seperate Field Names (on my Access Database) if I apply the above coding? As currently I am using Date() format, and the database recorded is on the MM-DD-YYYY format (one Field Name)...

Can the ASP coding determine the "Year(DATECOLUMN)" and "Month(DATECOLUMN)" and pull up the required data I need, just on the Date() Field Name I've created?

Thanks

Sorry to trouble you, happygv...

I have received this error "Data type mismatch in criteria expression." when applying your coding to my ASP script...

<%
    MM=Request.Form("MM")
    yyyy=Request.Form("yyyy")

    mon = Month(date())
    If len(MM)=0 then MM=mon

    Yr = Year(date())
    MinYr = Yr - 2
    MaxYr = Yr + 2
    If len(yyyy)=0 then yyyy=Yr
%>

<Form name="frm" method="Post" action="">


<p align="center">Month : <Select name="MM">
<%
    For i = 1 to 12
%>
        <Option Value="<%=i%>" <%If cint(i) = cint(MM) then%> Selected <%End If%>><%=MonthName(i)%></option>
<%
    Next
%>
</Select>


Year : <Select name="yyyy">
<%
    For i = MinYr to MaxYr
%>
        <Option Value="<%=i%>" <%If cint(i) = cint(yyyy) then%> Selected <%End If%>><%=i%></option>
<%
    Next
%>
</Select>

<input type="Submit" value="Submit"> </p>
</Form>
<%
MM=Request.Form("MM")
yyyy=Request.Form("yyyy")
if MM<>"" then
accessdb="Green"
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT Field1,Field2,Field3,Field4 FROM Table1 WHERE MyMonth = " & MM & " and MyYear = " & yyyy
rs.Open sql, cn
%>

My Field Names in Access Database are in Text format... Not in Date/Time format... May I know where went wrong?

Thanks

Thanks, I got it myself... :-p

I forgotten to put "'" for my text format... :-D

A thousand apologies... And the end result works perfectly...

Thanks again.. madhukp and happygv :)

 Yes it can. But actually in that statement, it is the database that is going to parse the YEAR() and MONTH() functions, not the ASP code. It very well works with ASP code too as the same functions are available in VBScript.

_________________________
- Vijay G
Strive for Perfection

Hey HelpMe,

Glad that you got that solved (helped yourself;)). I was not able to find time for posting replies here yesterday.

Is that you have split the date values in the database into three columns? I wouldn't have suggested that. It is best that you have them as datetime type into a single column. As suggested in my other post which had the SELECT statement posted, it would very well pull data based on YEAR(DATECOLUMN) and MONTH(DATECOLUM). So you dont have to worry about that. Later on when you really wanted to do operations based on date, you wouldn't be feeling confortable if you have those values (month, day, year) all in separate columns. That would put you in trouble asking to look for work arounds. It is better you split them and operate only in the frontend(for convenience), but let that be the datetime type in your database.

Hope that helps.
Cheers!

Cheers!

_________________________
- Vijay G
Strive for Perfection