Asked By Rich
26-Oct-09 03:53 PM
Hi,
I have an access database that contains 5 years worth of data. The
table names are in the format of 4 digit year and tags, i.e. 2007tags,
2008tags, 2009tags, etc. 99.9% of the work will be querying against
the current year. However there is the occasional needed to view past
years. So I wrote some code that defaults to the current and asks the
user if they want to change it. The problem is I cannot get to the
older data. The code always defaults to 2009. I really do not want to
create a page of buttons and the user chooses 2009 because that would
be extra work.
The goal is to have the system default to the current year. When the
user selects 2008 and clicks on submit the queries should run again
against the 2008tags table.
What did I do wrong?
TR/html4/strict.dtd">
html, body {
height: 100%;
min-height: 100%;
}
body{
border:0;
margin:20px;
background-color:white;
color:black;
text-align:center;
}
select {
width:200px;
}
p {
width:200px;
display: inline;
}
Dim objconn,objRS,strSQL1
Dim year1
Dim i
Dim dogtagarray, rabiesarray, ownerstarray, ownerlastarray,
breedarray, pettypearray
If Not Request.form("year2") Is Nothing Then
year1 = year(date)
else
year1 = Request.Form("year1")
end if
Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "DRIVER=Microsoft Access Driver
(*.mdb);DBQ=" & Server.MapPath("tags.mdb")
objconn.Open
Set objRs = Server.CreateObject("ADODB.Recordset")
strSQL1 = "SELECT Tagno FROM " & year1 & "Tags ORDER BY TagNO ASC"
' response.write "dd " & year1
objRS.Open strSQL1, objconn
if not objrs.eof then
dogtagarray = objrs.getrows()
else
response.write "<h1>No records returned!</h1>"
response.end
end if
objrs.close
strSQL1 = "SELECT DISTINCT RabiesTagNo FROM " & year1 & "Tags ORDER BY
RabiesTagNO ASC"
objRS.Open strSQL1, objconn
if not objrs.eof then
rabiesarray = objrs.getrows()
else
response.write "<h1>No RabiesTagno records returned!</h1>"
response.end
end if
objrs.close
strSQL1 = "SELECT OwnerStName FROM Streets ORDER BY OwnerStName ASC"
objRS.Open strSQL1, objconn
if not objrs.eof then
ownerstarray = objrs.getrows()
else
response.write "<h1>No Street Name records returned!</h1>"
response.end
end if
objrs.close