Query selecting month only in date

Question

I have a query that will select all records from my database based on the month. For example, I want to select all records for the month of January. The month() function doesn't work for me. The ComboBox has a value of month name ("January", "February", etc.). I'm using VB 2010 and my database is Microsoft Access.

query = "SELECT empid, empname, department, empdate, timeinam, " & _
        "timeoutam, lateam, timeinpm, timeoutpm, latepm, thw " & _
        "FROM tbldtr where Month(empdate) =" & cmbMonth.Text

Does you combobox contains month numbers from 1 to 12? – Steve Jan 15 '16 at 11:16 — Steve, Jan 15 '16 at 11:16
no the combobox contains full month name. – ken abaki Jan 15 '16 at 11:17 — ken abaki, Jan 15 '16 at 11:17

Steve · Accepted Answer · 2016-01-15T12:03:22.617

Well, supposing that you have your combobox items sorted in a monthly order (Jan, Feb, March...) then you could write your query as

query = "SELECT empid, empname, department, empdate, timeinam, " & _
        "timeoutam,lateam, timeinpm, timeoutpm,latepm,thw " & _
        "FROM tbldtr where Month(empdate) =" & cmbMonth.SelectedIndex + 1

ComboBox.SelectedIndex property is an integer that tells you the index of the current selected item. This property starts at zero, so adding one, matches the number returned by the VBA Month function

Of course, this means that you have somewhere, before this line, a check that informs your user to select something from the combobox and the combobox itself should have DropDownStyle set to ComboBoxStyle.DropDownList to avoid user inputs its own 'month'

WARNING: While, in this context, (converting an integer to a string) there is no much concern about Sql Injection it is better to not indulge in these practices and use always a parameterized query.

score 1 · Answer 2 · answered Jan 15 '16 at 14:46

Admittedly this is no different than other responses but wanted to express checking the selected index before executing the query along with best to use parameters as shown below. This is OleDb provider, same applies for all managed data providers just change to the correct one e.g. SQL server use SqlClient etc.

Load ComboBox

cmbMonth.Items.AddRange(
(
    From M In System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.MonthNames
    Where Not String.IsNullOrEmpty(M)).ToArray
)

Sample to run statement

If cmbMonth.SelectedIndex > -1 Then
    Using cn As New OleDb.OleDbConnection With
        {
            .ConnectionString = "Your connection string"
        }
        Using cmd As New OleDb.OleDbCommand With
            {
                .Connection = cn,
                .CommandText =
                    "SELECT empid, empname, department, empdate, timeinam, timeoutam, lateam, timeinpm, timeoutpm, latepm, thw " &
                    "FROM tbldtr where Month(empdate) = @SelectedMonth"
            }
            cmd.Parameters.Add(New OleDb.OleDbParameter With
                {
                    .ParameterName = "@SelectedMonth",
                    .DbType = DbType.Int32,
                    .Value = cmbMonth.SelectedIndex + 1
                }
            )
            ' continue
        End Using
    End Using
End If

score 0 · Answer 3 · edited May 23 '17 at 11:52

0

Month() function returns the Month number ,so your where condition fails.

instead use like,

query = "SELECT empid, empname, department, empdate, timeinam, timeoutam,lateam, timeinpm, timeoutpm,latepm,thw FROM tbldtr where datename(month, empdate) =" & cmbMonth.Text

also try to use Like statement rather than equals in where condition,because you may encounter issues with Character casing.more discussion on Like vs (=).

hope this helps.

edited May 23 '17 at 11:52

Community

1
1

answered Jan 15 '16 at 11:52

akhil kumar

1,598
1
13
26

1

There is no _DateName_ function in Access/JET. – Gustav Jan 15 '16 at 13:29

Gustav · Answer 4 · 2016-01-15T13:34:18.323

0

You can also use:

"FROM tbldtr where MonthName(Month(empdate)) ='" & cmbMonth.Text & "'"

edited Jan 15 '16 at 13:34

answered Jan 15 '16 at 13:30

Gustav

53,498
7
29
55

I was having that doubt, but now I am pretty sure that while 'MonthName' is a valid VBA function, it is not available in a OleDb context.I get an ' Undefined function MonthName in expression' in a test code. [See this q/a](http://stackoverflow.com/questions/4733155/any-references-manuals-on-sql-in-excel-with-microsoft-ole-db-provider-for-jet-4) – Steve Jan 15 '16 at 13:48
1

@Steve: That may be true (I don't pass literal SQL). If so, you are left with the index of the combobox as you already have shown which method, by the way, I would prefer at any time. – Gustav Jan 15 '16 at 15:24

Query selecting month only in date

4 Answers4