How to search record based on Month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have several records with Date Of Birth information. The Date Of Birth is
in Medium Date format : e.g. 1-Jan-1978

Is it possible for me to search record by Month?
e.g I type Jan or January it will displayed all Date Of Birth in January
regardless year

So far I can only search by Start Date : 1-Jan-1978
and End Date 31-Jan-1978. This will only display all record with Date Of
Birth in January 1978. I want all date on January regardless of year.

Please assist me. Thank you very much.
 
Suzie,

In your query, add a calculated field like:

BirthMonth: Month([Date Of Birth])

(where I have assumed your date field to be called Date Of Birth)

Function Month() will return the month of the date, so you can now
impose a month number as a filter and get what you want.

HTH,
Nikos
 
You can easily do what you want if you enter the month number using the
following;

SELECT Table1.DOB
FROM Table1
WHERE (((Month([Table1]![DOB]))=[Enter Month]));

There may be an easier way but if you want to enter the month name I
would probably create a custom function to turn the alpha month into
the month number and use that custom function in the query like this;

SELECT Table1.DOB
FROM Table1
WHERE (((Month([Table1]![DOB]))=MonthNbr([Enter Month])));

The custom function is MonthNbr and is something like this;

Public Function MonthNbr(strMonth As String) As Integer

Select Case strMonth
Case "Jan", "January"
MonthNbr = 1
Case "Feb", "February"
MonthNbr = 2
Case "Mar", "March"
MonthNbr = 3
Case "Apr", "April"
MonthNbr = 4
Case "May"
MonthNbr = 5
Case "Jun", "June"
MonthNbr = 6
Case "Jul", "July"
MonthNbr = 7
Case "Aug", "August"
MonthNbr = 8
Case "Sep", "September"
MonthNbr = 9
Case "Oct", "October"
MonthNbr = 10
Case "Nov", "November"
MonthNbr = 11
Case "Dec", "December"
MonthNbr = 12
Case Else
MonthNbr = 0
End Select

End Function

If anything other than the ENGLISH month or three letter abbreviation
is entered the MonthNbr returns 0 and will not find any matching dates.
 
Back
Top