By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,020 Members | 609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,020 IT Pros & Developers. It's quick & easy.

Unable to extract data between two dates

P: 1
Hi All,
I am unable to get the excel open when user select two dates from my code. Previously i was successfully extracting values by selecting year and month values from the webpage. Could you help me out in writing the Access 2003 SQL query to resolve my issue. Looking forward to your help and support:) I am giving the snapshot of my code:

Expand|Select|Wrap|Line Numbers
  1. <%@Language=VBScript%>
  2.  
  3. <%
  4.     Dim TmplXls
  5.     Dim DestXls
  6.     Dim DB
  7.     Dim DBConn
  8.     Dim DestXlsConn
  9.     Dim FSO
  10.     Dim XlsRS
  11.     Dim DBRS
  12.     Dim sqlText
  13.     Dim strMnth
  14.     Dim strYear
  15.     Dim strlte10Pcnt
  16.     Dim cnt1
  17.     Dim cnt2
  18.     Dim cnt3
  19.  
  20.  
  21.  
  22.     If Request.Form("Fst").Count = 0 Then
  23.     strStartDate = "(Fst = Format([date],'ddmmyyyy'))"
  24.     Else
  25.         For cnt2 = 1 to Request.Form("Fst").Count
  26.             If cnt2 = 1 Then
  27.                 strStartDate = "(Fst = " & Request.Form("Fst")(cnt2)
  28.             Else
  29.                 strStartDate = strStartDate & " OR Fst = " & Request.Form("Fst")(cnt2)
  30.             End If
  31.     Next
  32.         strStartDate = strStartDate & ")"
  33.     End If
  34.  
  35.     If Request.Form("Lst").Count = 0 Then
  36.         strLastDate = "(Lst = Format([date],'ddmmyyyy'))"
  37.     Else
  38.         For cnt3 = 1 To Request.Form("Lst").Count
  39.         If cnt3 = 1 Then
  40.                 strLastDate = "( Lst = " & Request.Form("Lst")(cnt3)
  41.             Else
  42.                 strLastDate = strLastDate & " OR Lst = " & Request.Form("Lst")(cnt3)
  43.             End If
  44.         Next
  45.         strLastDate = strLastDate & ")"
  46.     End If
  47.  
  48.  
  49.  
  50.  
  51.     'Response.Write strYear
  52.  
  53.     TmplXls = Server.MapPath(".") & "\Memory_Use_Sample.xls"
  54.     DestXls = Server.MapPath(".") & "\Memory_Use.xls"
  55.     DB = Server.MapPath(".") & "\Server_Monitoring.mdb"
  56.  
  57.     Set FSO = Server.CreateObject("Scripting.FileSystemObject")
  58.     FSO.GetFile(TmplXls).Copy DestXls
  59.  
  60.  
  61.     Set FSO = Nothing
  62.  
  63.     Set DestXlsConn = Server.CreateObject("ADODB.Connection")
  64.     DestXlsConn.Open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & DestXls & "; " & "Extended Properties=""Excel 8.0;HDR=NO;"""
  65.  
  66.     Set XlsRS = Server.CreateObject("ADODB.Recordset")
  67.     'XlsRS.Open "Select * from Report_Range", DestXlsConn, 1, 3
  68.     XlsRS.Open "Select * from Memory_Use", DestXlsConn, 1, 3
  69.  
  70.     Set DBConn = Server.CreateObject("ADODB.Connection")
  71.     Set DBConn = Server.CreateObject("ADODB.Connection")
  72.     DBConn.Open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & DB
  73.  
  74.     Set DBRS = Server.CreateObject("ADODB.Recordset")
  75.     sqlText = "Select date, Time_Hour, ServerName, MemoryUse from Q1 where date between" & strStartDate
  76.  
  77.     'strSQL = "SELECT * " & _ 
  78.         ' "FROM [RECORDS] " & _ 
  79.          '"WHERE (CDate(Date_Pur) Between #" & _ 
  80.         ' Format(Date_From,"m/d/yyyy") & "# And #" & _ 
  81.         ' Format(Date_To,"m/d/yyyy") & "#)" 
  82.  
  83.    'Response.Write strText
  84.   ' Response.End
  85.         If Request.Form("Lst").Count > 0 Then
  86.             sqlText = sqlText & " AND "& strLastDate 
  87.         End If
  88.  
  89.     'Response.Write sqlText
  90.     'Response.End
  91.  
  92.     DBRS.Open sqlText, DBConn, 1
  93.      'Response.Write sqlText    
  94.      'Response.End    
  95.  
  96.            If DBRS.Recordcount > 0 Then
  97.         DBRS.MoveFirst
  98.  
  99.         Dim x, y
  100.         x = 1
  101.         Do While Not (DBRS.EOF)
  102.             XlsRS.AddNew
  103.             XlsRS.Fields(0).Value = x
  104.  
  105.             For y = 1 To 4
  106.                 XlsRS.Fields(y).Value = DBRS.Fields(y-1).Value
  107.             Next
  108.             XlsRS.Update
  109.             DBRS.MoveNext
  110.             x = x + 1
  111.         Loop
  112.  
  113.         Response.Write "<META HTTP-EQUIV=""REFRESH"" Content=""0;URL=Memory_Use.xls"">"
  114.         'Response.Write "Matching records found"
  115.  
  116.     Else
  117.         Response.Write "No matching records found"
  118.     End If    
  119.  
  120.  
  121.     DBRS.Close
  122.     Set DBRS = Nothing
  123.  
  124.     DBConn.Close
  125.     Set DBConn = Nothing
  126.  
  127.     DestXlsConn.Close
  128.     Set DestXlsConn = Nothing
  129.  
  130.     'Response.Write "<META HTTP-EQUIV=""REFRESH"" Content=""0;URL=Memory_Use.xls"">"
  131.     'XlsRS.Close
  132.  
  133.     'Set XlsRS = Nothing
  134. %>
Nov 4 '08 #1
Share this Question
Share on Google+
1 Reply

jhardman
Expert 2.5K+
P: 3,405
Hi All,
I am unable to get the excel open when user select two dates from my code. Previously i was successfully extracting values by selecting year and month values from the webpage. Could you help me out in writing the Access 2003 SQL query to resolve my issue. Looking forward to your help and support:) I am giving the snapshot of my code:
So what part doesn't work? Are you getting a syntax error when you query the db? Try doing a response.write of the query from the working version and from the current broken version, I bet you will see an obvious difference. If you don't please post both versions of the query (like it looks when finished).

Jared
Nov 5 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.