Member Avatar for me_j

I've a 'Birthday' form wherein when i click on Search command button, an input box is displayed. In this input box, i type in a month and when i click on OK i want details of all those students whose birthday falls in that month.i'm using ADO Data Connection.

  • 4 Contributors
  • 3 Replies
  • 84 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by choudhuryshouvi

Dani AI

Generated

Summary and practical next steps for the birthday-by-month search (ties to posts by , and ).

A reliable solution has three parts: parse and validate the month input, use a parameterized query (do not concatenate user input into SQL), and prefer a date-range filter so the database can use an index and so time-of-day parts do not break matches. rightly points to a start/end date approach; shows the idea but the example needs input validation and parameterization.

Recommended pattern (VB6/ADO): convert the typed month into an integer (accept "4", "Apr", "April" by parsing e.g. Month(CDate("1 " & input & " 2000")) with error handling), compute startOfMonth = DateSerial(Year(Date), monthNum, 1) and startOfNextMonth = DateAdd("m", 1, startOfMonth), then run a parameterized query that tests BirthDate >= startOfMonth AND BirthDate < startOfNextMonth. This half-open range avoids time-component pitfalls that BETWEEN can trigger and is index-friendly. Parameterized commands also avoid SQL injection and localization surprises.

Example snippet (adapt connection setup to the existing form):

' conn is an open ADODB.Connection
Dim cmd As ADODB.Command, rs As ADODB.Recordset
Dim monthNum As Integer, startOfMonth As Date, startOfNext As Date

' Convert/validate input (monthInput obtained earlier)
monthNum = CInt(monthInput) ' or use Month(CDate("1 " & monthInput & " 2000")) with error handling

startOfMonth = DateSerial(Year(Date), monthNum, 1)
startOfNext = DateAdd("m", 1, startOfMonth)

Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = conn
  .CommandType = adCmdText
  .CommandText = "SELECT * FROM Students WHERE BirthDate >= ? AND BirthDate < ? ORDER BY Month(BirthDate), Day(BirthDate)"
  .Parameters.Append .CreateParameter("p1", adDate, adParamInput, , startOfMonth)
  .Parameters.Append .CreateParameter("p2", adDate, adParamInput, , startOfNext)
  Set rs = .Execute()
End With

Notes: choose adOpenForwardOnly/adLockReadOnly for simple reads, handle parse errors for nonstandard input, and adjust the SQL function name if the target server expects MONTH() vs Month().

Recommended Answers

All 3 Replies

Member Avatar for debasisdas

simply u need to trap the start and end date of the month and run a BETWEEN query in the database using ADO to get the output.

Member Avatar for kehar

email your programme at unless one see the programe one c annot give you the idea because how can one know where you are.
Bye.

Member Avatar for choudhuryshouvi

cmdsearch_click()
dim str as string
dim rs as new adodb.connection

str=inputbox("enter month","search")
if str<>"" then
rs.open "select * from <tablename> where month='" & str & "'"
if rs.recordcount>0 then
<display data>
else
msgbox "no record found"
endif
else
msgbox "input the month plz."
endif
end sub

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.