Date of Birth

[katieb]

I am trying to display a list of records from an MS Access database of people who are over 18. There is a column in the table called dateOfBirth. I have tried to generate this list, but no joy.

How do I write the ASP/VBScript code to do this?

Please help, I am a very new newbie and my head hurts!

Katie

 

[mattygriff]

Without seeing all your code, I don't know what stage you're at but this might help...

Set rsNameList=Server.CreateObject("ADODB.Recordset")
strSQL_names = "SELECT * FROM names ORDER BY dateofbirth"
rsNameList.Open strSQL_names, [COLOR=red]yourconnectionstring[/color]

do while not rsNameList.EOF
response.write rsNameList("name") & " was born on "
response.write rsNameList("dateofbirth") & "<BR><BR>"
rsNameList.movenext
loop

You'll need to replace my table and field names with those from your database and I'm assuming you're using a DSN-less connection to your database.

Hope that helps. If you need more, please post your code.

 

[katieb]

Hi,

My Code is as follows - I have just put in a SELECT * FROM for now, but this will need to calculate age as of today, and then only display records for people over 18.

Dim db
Set db = Server.CreateObject("ADODB.Connection")
db.provider = "Microsoft.Jet.OLEDB.4.0"

db.Open "C:\Documents and Settings\Administrator\My Documents\230CSP\residents.mdb", "", "", 0

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")

Dim strSelect

strSelect = "SELECT * from tblResident"

rs.open strSelect, db, 3, 3

if not rs.BOF then

rs.MoveFirst()
Do While Not rs.EOF

Dim str

str = rs("firstname").Value
%>
<%=str%>
<br>
<%
str = rs("surname").Value
%>
<%=str%>
<br>
<%
str = rs("dateofbirth").Value
%>
<%=str%>
<br>
<%
rs.MoveNext()
Loop
else
%>
There are no residents eligible to vote
<%
end if
%>

 

[katieb]

I've got a little further, but when I display the page it says: Expected end of statement
/residents/Electoral.asp, line 25, column 50
strSelect = "SELECT FirstName, Surname, DateDiff("yyyy",[dateofbirth],Date()) AS Age FROM tblResident WHERE age>19));"
-------------------------------------------------^

What have I done wrong?


Dim db
Set db = Server.CreateObject("ADODB.Connection")
db.provider = "Microsoft.Jet.OLEDB.4.0"

db.Open "C:\Documents and Settings\Administrator\My Documents\230CSP\residents.mdb", "", "", 0

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")

Dim strSelect

strSelect = "SELECT FirstName, Surname, DateDiff("yyyy",[dateofbirth],Date()) AS Age FROM tblResident WHERE age>18));"

rs.open strSelect, db, 3, 3

if not rs.BOF then

rs.MoveFirst()
Do While Not rs.EOF

Dim str

str = rs("firstname").Value
%>
<%=str%>
<br>
<%
str = rs("surname").Value
%>
<%=str%>
<br>
<%
str = rs("age").Value
%>
<%=str%>
<br>
<%
rs.MoveNext()
Loop
else
%>
There are no residents eligible to vote
<%
end if
%>

 

[amorous]

You dont need closing braces in the end.

Try this:

strSelect = "SELECT FirstName, Surname, DateDiff("yyyy",[dateofbirth],Date()) AS Age FROM tblResident WHERE age>19;"

hope that helps

VJ

 

[vongrunt]

Double nested quotes - try with 'yyyy' or ""yyyy"".

 

[Tarwn]

The problem is that DateDiff is going to round, so your going to be missing a bunch of results. We went through a 40 post thread on something similar once

Try just selecting where the birthdate is less than DateAdd("y",-18.Now). That should give you everyone who was born more than exactly 18 years from right this moment, so you shouldn't lose or gain any months due to using DateDiff year measurements.

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/