Month Name query in Access 2007

[pop_cola]

Hi guys,

Just want to ask if there's a way to search in a date/time field in access and display only the month that match the string from a textbox. The query below.

sql = "SELECT * FROM tblName WHERE fldDate LIKE '%" & textbox1.text & "%'"

So if I typed in "ma" from the textbox, it will display all the months with "ma" on it (e.g. March, May)

Cheers,

Marcus

[Nutster]

Not Tested

strSQL = _
    "SELECT * FROM tblName WHERE Format(fldDate, ""Mmmm"") LIKE '%" & _
    textbox.text & "%'"

This kind of construction was often valid in VB6. I have not tried it in VB.Net.

[Reverend Jim]

Dates are not stored as string so you can't check for "Ma" or any other part of a month. You would have to use the DATEPART function to extract the mont number and comare it to 3 (for March). For example (and I may not have the exact syntax right here)

SELECT * FROM myTable WHERE DATEPART('m',dateField) = 3

should retrieve all records for March of any year.

[G_Waddell]

this would work in SQL Server - not sure about ACCESS....

sql ="SELECT *  FROM tblname  WHERE (Datename(month, fldDate) LIKE '%'" & textbox1.Text & "'%')"

[TnTinMN]

Unfortunately, Access does not support the DateName function. However, you can get the same functionality from the "FORMAT" function.

Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & "D:\My Documents\temp\Play.mdb" & "'")
Dim cmd As New OleDb.OleDbCommand()
cmd.Connection = conn


TextBox1.Text = "ma"
cmd.CommandText = "SELECT *, FORMAT([DateEntered], 'MMMM') as [Month] FROM FilterOnDate WHERE UCASE(FORMAT([DateEntered], 'MMMM')) LIKE '" & TextBox1.Text & "%'"

conn.Open()
Dim dt As New DataTable
dt.Load(cmd.ExecuteReader(CommandBehavior.Default))
conn.Close()
DataGridView1.DataSource = dt

[Reverend Jim]

Verified, the following statement works in Access

"select * from table1 where datepart('m',myDateField) = 6"

[G_Waddell]

I thought you wanted the users to type "Jan"or "January" and the items for January would appear?

If suggestion from TnTinMN works then great, if not, how about giving them combobox to pick the month and pass the month value into Rev Jims query?

You could create a month item class:

Public Class MonthItem
Private Property _MonthName as string
Private Property _MonthValue as Integer

Public ReadOnly Property MonthName As String
 Get
    Return _MonthName
 End Get
End Property

Public Property MonthValue as Integer
    Get 
        Return _MonthValue
    End Get
    Set (ByVal value as Integer)
       If value <= 12 AndAlso value > 0 then
        _MonthValue = value
        _MonthName = MonthName(value,false)
       else
        _MonthValue =0
        _MonthName ="Invalid"
       end if
     End Set
End Property

Public Overrides Function ToString() As String
    Return MonthName
End Function

Public Sub New (ByRef iMonth as Integer)
    me.MonthValue = iMonth
End Sub
End Class

Then on your Form Have a combobox configured as a dropdown list and do something like this:

'populate combo
dim myMonth as MonthItem
for i as integer =1 to 12
    MyMonth = New MonthItem(i)
    MonthCombo.Items.Add(MyMonth)
next

Finally to get the month to put into the statement from Jim:

dim MyMonth as MonthItem

MyMonth = Monthcombo.selectedItem

sql="select * from table1 where datepart('m', mydatefield) =" &myMonth.MonthValue

[Nutster]

Why make an entire class, when you could just call MonthName in the loop?

For I As Integer = 1 To 12
    MonthCombo.Items.Add(MonthName(I, False))
Next

The SelectedIndex property of the combo box tells you the numeric month, but you have to add one.

strSQL = "SELECT * FROM table1 WHERE DatePart('m', MyDateField) = " & _
    (MonthCombo.SelectedIndex + 1)

Just make sure you have the Sorted property off for the combobox.

[G_Waddell]

True but, if he is using dates through out his app, he can reuse the class...
Actually it was just what popped into my head at the time.