Display Projects by Month

[hotflash]

Hi All,

I am not trying to convert ASP to MS Access style but I used to have a MS Access database that will query completed projects (not NULL), by month. Let's say, if I click on the query, I have to enter the begining of the month such as 1/1/2008 and the end of the month 1/31/2008 and it will give me a list of projects completed within the month of January.

Can someone please advise if this is a DOABLE feature using ASP? Thanks for your help.

[markrawlingson]

This is doable.

For this I would have a field in the database table, called dCompleted which will represent the date the project was completed.

The second part focuses on the SQL query itself using the BETWEEN keyword.

sSQL = "SELECT * FROM tblProjects WHERE dCompleted BETWEEN 1/1/2008 AND 1/31/2008 OR dCompleted = 1/1/2008 OR dCompleted = 1/31/2008;"
 

This will return all records where the date the project was completed is between the 1st and 31st of the month of January 2008.

Note the "OR dCompleted = 1/1/2008 OR dCompleted = 1/31/2008" - this is a fix because the SQL BETWEEN keyword will only return records literally BETWEEN the two values. So in the above example "BETWEEN 1/1/2008 AND 1/31/2008" records with a value of 1/1/2008 or 1/31/2008 will not be returned.

Hope this helps.
Sincerely,
Mark

[hotflash]

Hi Mark,

Thanks for your outstanding support. Question for you. I guess instead of using the FIXED month as listed above I can ENTER the month (example: field A for begining of the month and field B for the end of the month) that I want? Thanks.

[markrawlingson]

Yep.

I have two calender controls on one of the sites I work with that does just this. It's a tool so people can refine their search to results that were posted between 2 dates. EG: Search for posts from [date] to [date]

Just modify the code replacing the fixed 'fake' dates I put in with the information coming from your form submission...

sSQL = "SELECT * FROM tblProjects WHERE dCompleted BETWEEN " & Request.Form("dFrom") & " AND " & Request.Form("dTo") & " OR dCompleted = " & Request.Form("dFrom") & " OR dCompleted = " & Request.Form("dTo")
 

Sincerely,
Mark

[hotflash]

Hi Mark,

Can you please tell me if you see an issue with the following code? Keep getting Microsoft VBScript runtime error '800a01a8'
Object required: ''

Please note that CompleteDate is a Text field in the MS Access Database.
Thanks once again for your outstanding support.

<% 
Dim Conn
Dim strSQL
Dim RS
Dim strFDates
Dim strTDates
 
Dim strCount
strCount=0
 
strFDates         = trim(Request("FDates")) 
strTDates         = trim(Request("TDates"))
strEngineer     = trim(Request("Engineer"))
strWorkType     = trim(Request("WorkType"))
 
Set Conn = Server.CreateObject("ADODB.Connection") 
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Database.mdb")& "; Jet OLEDB:Database Password=yourpassword" 
 
strSQL = "SELECT * FROM TableProject WHERE CompleteDate BETWEEN " & strFDates & " AND " & strTDates & " OR CompleteDate = " & strFDates & " OR CompleteDate = " & strTDates & ""
 
RS.Open strSQL, Conn
 
If (RS.EOF) Then 
    Response.Redirect ("SearchReports.asp?error=Sorry ... Please try again.  Thanks.") 
End If 
%>

[markrawlingson]

Hmmm, that could be a problem. I'm not sure how the BETWEEN keyword works on strings, I wouldn't imagine it will at all :P

Why are you entering a date into a text field? This is really bad practice, I would strongly advice you to use the data types according to the type of information that will be inserted into them.

You need to wrap single quotes around any data that you pass through a text field in your database. So start with your SQL statement, but if that doesn't work I would also advise throwing in some ADO constants when you open your recordset..

RS.Open strSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText

OR

RS.Open strSQL, Conn, 3, 3 (If you don't have the ado constants defined anywhere.)

I would advise you to use ado constants though rather than the numbers. The numbers might look easier to type but - what do they mean? :P

Give that a shot, but if the between keyword works at all on text input - it will probably act eradically. If that's the case, change the field to datetime and see how the code works.

Also, whenever you get an error it's very difficult to diagnose unless we know the line which is throwing the error - so please identify the line which is throwing the error in the future, so that we can assist you better.

Sincerely,
Mark

Hi Mark,

Can you please tell me if you see an issue with the following code? Keep getting Microsoft VBScript runtime error '800a01a8'
Object required: ''

Please note that CompleteDate is a Text field in the MS Access Database.
Thanks once again for your outstanding support.

<%
Dim Conn
Dim strSQL
Dim RS
Dim strFDates
Dim strTDates

Dim strCount
strCount=0

strFDates = trim(Request("FDates"))
strTDates = trim(Request("TDates"))
strEngineer = trim(Request("Engineer"))
strWorkType = trim(Request("WorkType"))

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Database.mdb")& "; Jet OLEDB:Database Password=yourpassword"

strSQL = "SELECT * FROM TableProject WHERE CompleteDate BETWEEN " & strFDates & " AND " & strTDates & " OR CompleteDate = " & strFDates & " OR CompleteDate = " & strTDates & ""

RS.Open strSQL, Conn

If (RS.EOF) Then
Response.Redirect ("SearchReports.asp?error=Sorry ... Please try again. Thanks.")
End If
%>

[hotflash]

Hi Mark,

Thanks for your quick response. I changed the CompleteDate in the MS Access database to DateTime. Put single quote (') around like
strSQL = "SELECT * FROM TableProject WHERE CompleteDate BETWEEN '" & strFDates & "' AND '" & strTDates & "' OR CompleteDate = '" & strFDates & "' OR CompleteDate = '" & strTDates & "'"

GOT an ERROR on the line below:

Microsoft VBScript runtime error '800a01a8'

Object required: ''

RS.Open strSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText

Hope this helps and thanks once again for your help sir.

[DrBunchman]

Hi Hotflash,

Have you actually created your recordset object? I can't see this line in your code anywhere:

Set RS = Server.CreateObject("ADODB.RecordSet")

Try putting that line in before you open your recordset.

Hope this helps,

Dr B

[hotflash]

Hi Dr. B,

I did what you recommended and getting the following here.

Below is the code that I currently trying.

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/Reports/DisplayReports.asp, line 23

The line 23 here is RS.Open strSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText.

Any other recommendations? Thanks once again for your help.

<% 
Dim Conn
Dim strSQL
Dim RS
Dim strFDates
Dim strTDates
 
strFDates         = trim(Request("FDates")) 
strTDates         = trim(Request("TDates"))
strEngineer     = trim(Request("Engineer"))
strWorkType     = trim(Request("WorkType"))
 
Set Conn = Server.CreateObject("ADODB.Connection") 
Set RS = Server.CreateObject("ADODB.RecordSet")
 
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Database.mdb")& "; Jet OLEDB:Database Password=yourpassword" 
 
strSQL = "SELECT * FROM TableProject WHERE CompleteDate BETWEEN '" & strFDates & "' AND '" & strTDates & "' OR CompleteDate = '" & strFDates & "' OR CompleteDate = '" & strTDates & "'"
 
RS.Open strSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText
 
If (RS.EOF) Then 
    Response.Redirect ("SearchReports.asp?error=Sorry ... Please try again.  Thanks.") 
End If 
%>

[DrBunchman]

The reason for the error is you haven't defined the ADO constants that you are using (adOpenStatic, adLockOptimistic, adCmdText) anywhere.

These constants are defined in the file adovbs.inc which you need to include in your page. There is an excellent tutorial here: http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=123

For the time being though I suggest you replace the line

RS.Open strSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText

with this one

RS.Open strSQL, Conn, 3, 3

as MarkRawlingson suggested earlier in this thread. Give that a try and it should stop your page from erroring.

Let us know how you get on.

Dr B

[NeoPa]

...
Note the "OR dCompleted = 1/1/2008 OR dCompleted = 1/31/2008" - this is a fix because the SQL BETWEEN keyword will only return records literally BETWEEN the two values. So in the above example "BETWEEN 1/1/2008 AND 1/31/2008" records with a value of 1/1/2008 or 1/31/2008 will not be returned.

I think there must be some confusion here Mark. SQL "X Between A And B returns records with values of X such that A <= X <= B.
Dates can sometimes be confusing as they sometimes have time elements within them - giving the impression that they're actually equal to the upper limit in a Between construct, whereas they are actually above it.
Assume today is 22 Feb 2008.

WHERE [DateField] Between #2/1/2008# And #2/22/2008#

fails to return records for today if the field is populated using Now() as the date (and time) is actually greater than the date literal.

PS. HotFlash, please remember to use the [ CODE ] tags provided (the # button). You're a regular visitor and we should be able to expect you to follow the posting guidelines.