Click here to Skip to main content
15,072,051 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I want to filter age range based on two (2) textboxes value I have two (2) textboxes in a form, the first textbox is for beginning of range and second is for ending of range (e.g. 45(first textbox) to 60(second textbox)).A report is containing student's fullname, date of birth, and age column. The age column is not from the database but from the expression:
round(DateDiff("m",fields!DOB.Value,Now() )/12)


My problem is I can't filter age because the age result is from the expression and not from the database field. How to achieve such filtering?

What I have tried:

Me.studentprofile_RecordsTableAdapter.Fill(Me.studentDataSet.studentprofile_Records,"Select * studentfullname, dateofbirth, age from studentprofile_Records Where (Textbox1.value >= ?) AND (Textbox2.value <= ?)")


The code above did not work. It results in filtering to zero (0) result.
Posted
Updated 13-Feb-21 1:47am

1 solution

Putting Textbox1.value inside an SQL query string does not make any sense: the query gets executed by the database, which can't access your textboxes.

The logic I would suggest goes as follows (in pseudo-code!):
DateTime now=DateTime.Now;
DateTime minDOB=now.AddYears(-textboxMaxAge.Value);
DateTime maxDOB=now.AddYears(-textboxMinAge.Value);
string query="SELECT ... WHERE DOB between ? AND ?";
AddParamWithValue("?", minDOB);
AddParamWithValue("?", maxDOB);
execute(query);


:)
   
v2
Comments
kyrons 14-Feb-21 3:49am
   
Here is my contructed code but not working.

Dim connstring As String
connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=studentrecords.accdb"
Dim conn As OleDbConnection = New OleDbConnection(connstring)
Dim displaystr As String = "SELECT studentfullname, dateofbirth, Round(DateDiff('m',studenprofile_records.Date_of_Birth,Date())/12,2) AS Age FROM studentprofile_records WHERE Age BETWEEN'" & CInt(txtagefrom.Text) & "' AND Age <='" & CInt(txtageto.Text) & "'"
Dim cmddisplay As New OleDbCommand(displaystr, conn)
conn.Open()
cmddisplay.ExecuteNonQuery()
conn.Close()

Me.ReportViewer1.SetDisplayMode(Microsoft.Reporting.WinForms.ZoomMode.PageWidth)
Me.ReportViewer1.RefreshReport()
Luc Pattyn 14-Feb-21 10:20am
   
You're still using DateDiff where there is no need for it, you can calculate MinDOB and MaxDOB in VB.NET as I showed you, then just use SQL to compare Age to those limits.

Your query has multiple problems (missing space, incorrect use of BETWEEN).
I suggest you build your query string into a variable and have a look at it before you feed it to the database.

Also, you should put all of it inside a try=catch and look at the exception when you get one.

:)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900