Need Help creating a report

# Thread: Need Help creating a report

1. Member
Join Date
Dec 1969
Posts
59

## Need Help creating a report

I am trying to create a report for users of my application. I am having problems displaying the proper data. I have satred at this code so long I think my head will explode :) It is probably something simple too. Anyway you can view the page and source at<BR><BR>http://24.248.187.212/CopySSRealty/Admin.asp<BR><BR>Thanks

2. Senior Member
Join Date
Dec 1969
Posts
607

.

3. Member
Join Date
Dec 1969
Posts
59

## RE: and what is the problem?<eop>

I was just wondering if someone with more skill than myself could think of a more effiecient way of accomplishing this task.<BR><BR>While NOT rsTest2.EOF<BR> intExpDate = rsTest2.Fields.Item("EXPIRE_DATE")<BR> intDaysLeft = DateDiff("d", intToday, intExpDate)<BR> <BR> Ag_TotalListingsCount = Ag_TotalListingsCount + 1<BR> <BR> if intDaysLeft&#060;=1 then Ag_ExpCount = Ag_ExpCount + 1<BR> if intDaysLeft=2 then Ag_Exp2Count = Ag_Exp2Count + 1<BR> if intDaysLeft=7 or intDaysLeft&#062;=3 and intDaysLeft&#060;=6 then Ag_Exp7Count = Ag_Exp7Count + 1<BR> if intDaysLeft&#062;=8 then Ag_Exp30Count = Ag_Exp30Count + 1 <BR> <BR> rsTest2.MoveNext<BR> Wend

4. Member
Join Date
Dec 1969
Posts
59

## Please click on the "Testing"

Please click on the "Testing" link to view the report I am working on.

5. Senior Member
Join Date
Dec 1969
Posts
404

## RE: Please click on the

Function CountProperties(iDaysTillExp, iDaysToExclude)<BR><BR>CountProperties = 0<BR><BR>if iDaysTillExp = -1 Then<BR> expClause = ""<BR>Else<BR> If iDaysToExclude = -1 Then<BR> expClause = " AND EXPIRE_DATE &#060;=&#039;" & DateAdd("d",iDaysTillExp,Date()) & "&#039;"<BR> Else<BR> expClause = " AND EXPIRE_DATE BETWEEN &#039;" & DateAdd("d", iDaysTillExp, Date())<BR> & "&#039; AND &#039;" & DateAdd("d", iDaysToExclude + 1, Date()) & "&#039;"<BR> End If<BR>End If<BR><BR>sSQL = "Select Count(*) From Property WHERE AGENT = &#039;" & CurrentAgent & "&#039;" & expClause<BR><BR>Set rst = Server.CreateObject("ADODB.Recordset")<BR>rst.Open sSQL, conn<BR><BR>if Isnull(rst(0)) Then<BR>CountProperties = 0<BR>else<BR>CountProperties = rst(0)<BR>end if<BR><BR>rst.Close<BR>Set rst = Nothing<BR><BR>End Function<BR><BR>expired = CountProperties(0,-1)<BR>2days=CountProperties(2,0)<BR>7days=CountPro perties(7,2)<BR>30days=CountProperties(30,7)<BR>al l=CountProperties(-1,-1)<BR>userEnteredVal=countProperties(userVal, -1)<BR><BR>You may have to fool around with the &#062;= & the &#060;= depending on when you say that a property expires. That is on the date or day after the date.

6. Senior Member
Join Date
Dec 1969
Posts
96,118

...and we could improve on it some if performance was an issue. I might point out that, if this is a SQL Server DB, you don&#039;t have DATE() available to you and GetDate() returns an annoying time component, so you might be better off doing the date calculations in VBS. Oh, wait. Ned did that, didn&#039;t he. But then why DateAdd instead of just arithmetic? Well, anyway...<BR><BR><BR>SELECT 1,"&#060;FONT Color=Red&#062;Expired&#060;/FONT&#062;", Count(*) FROM ... AND EXPIRE_DATE &#060;= #2/1/2002#<BR>UNION<BR>SELECT 2,"2 Days", Count(*) FROM ... AND EXPIRE_DATE BETWEEN #2/2/2002# AND #2/3/2002#<BR>UNION<BR>SELECT 3,"7 Days", Count(*) FROM ... AND EXPIRE_DATE BETWEEN #2/4/2002# AND #2/8/2002#<BR>UNION<BR>SELECT 4,"30 Days", Count(*) FROM ... AND EXPIRE_DATE BETWEEN #2/9/2002# AND #3/3/2002#<BR>UNION<BR>SELECT 5,"Total Listings", Count(*) FROM ...<BR>ORDER BY 0<BR><BR>Execute that query, and you&#039;ll get back 5 records. Complete with titles in RS(1) and the value wanted in RS(2).<BR><BR>Only one query. Kind of an ugly query, but only one of them!<BR><BR>The #...# become &#039;...&#039; if not using Access, of course.<BR><BR>Ehhh...after doing all that stuff, I&#039;m not sure it&#039;s worth it. Ned&#039;s suggestion is probably better.<BR><BR>**************************<BR><BR>* * MINOR BUGS IN NED&#039;S CODE ***<BR><BR>(1) He names his variables "2days" and "7days", etc. You can&#039;t start a variable name with a number.<BR><BR>(2) He uses IF ISNULL(rst(0)) to see if he got a count. Instead, you should use <BR>IF rst.EOF Then CountProperties = 0 Else CountProperties = rst(0)<BR>*BUT* I don&#039;t think it&#039;s possible to get an EOF from a COUNT() query, so just do<BR>CountProperties = rst(0)<BR>and don&#039;t worry about it.<BR><BR><BR><BR>

7. Member
Join Date
Dec 1969
Posts
59

I knew there was a much better way of doing this. I think you are on the right path Ned. When I run the code I get the following error:<BR>Error Type:<BR>Microsoft JET Database Engine (0x80040E07)<BR>Data type mismatch in criteria expression.<BR>/CopySSRealty/Admin/test/index.asp, line 27<BR><BR>Changed Code-----------------------------------------------<BR><BR>&#039;----------------------------------------------------------------------------<BR> Function CountProperties(iDaysTillExp, iDaysToExclude)<BR>&#039;<BR>&#039;-----------------------------------------------------------------------------<BR><BR>CountProperties = 0 <BR><BR>if iDaysTillExp = -1 Then <BR> expClause = "" <BR>Else <BR> If iDaysToExclude = -1 Then <BR> expClause = " AND EXPIRE_DATE &#060;=&#039;" & DateAdd("d",iDaysTillExp,Date()) & "&#039;" <BR> Else <BR> expClause = " AND EXPIRE_DATE BETWEEN &#039;" & DateAdd("d", iDaysTillExp, Date()) & "&#039; AND &#039;" & DateAdd("d", iDaysToExclude + 1, Date()) & "&#039;" <BR> End If <BR>End If <BR><BR>sSQL = "Select Count(*) From Property WHERE AGENT = &#039;" & CurrentAgent & "&#039;" & expClause <BR>Response.Write sSQL<BR>Dim conn<BR>Set conn=Server.CreateObject("ADODB.Connection")<BR>co nn.Open Application("RealEstate_ConnectionString")<BR>Set rst = Server.CreateObject("ADODB.Recordset") <BR>rst.Open sSQL, conn <BR><BR>if Isnull(rst(0)) Then <BR>CountProperties = 0 <BR>else <BR>CountProperties = rst(0) <BR>end if <BR>%&#062;<BR><BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
404