Compare String to Date using Datepart

[Xproterg]

It's me again, with what should be a simple problem. Using the following code, I'm trying to compare a drop down combobox value list (January, February, etc...) with a date in the database ( 1/24/2011 for example). The records that meet the requirements are then listed in a listbox, by case number, which is also derrived from the database.

The code is as follows;

Dim montha As String
Dim strSQLa As String
Dim i As Integer
ReDim Months(1 To 12)
ReDim Indexes(1 To 12)
Dim convert As String
    
montha = Text101
    Months = Array("January", "February", "March", "April", "May", "June", "July", "August", "Sepember", "October", "November", "Decemebr")
    Indexes = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12")
    For i = 0 To 11
    If montha = Months(i) Then convert = Indexes(i)
    Exit For
    Next
strSQLa = "SELECT case FROM caselog WHERE DATEPART(month, caselog.dateR) = '" & convert & "';"
List110.RowSource = strSQLa

I'm pretty sure there is a small error here - perhaps something I'm overlooking. Any help would be greatly appreciated.

 

[MarkK]

You can do it in one line...

List110.RowSource = _
   "SELECT case FROM caselog WHERE Month(dateR) = " & GetMonthNumberFromName(Me.Text101)

...if you call this function...

Function GetMonthNumberFromName(Name As String) As Long
   Dim i As Integer
   For i = 1 To 12
      If MonthName(i) = Name Then
         GetMonthNumberFromName = i
         Exit For
      End If
   Next
End Function

Does that work?

 

[Galaxiom]

I would do it differently to save the conversion. The listbox can return the month's number directly.

Setup the listbox with two columns, one for the MonthName and the other for the MonthNumber. ("Index" is a reserved word). Set the MonthNumber width to zero. Bound Column is MonthNumber. Then simply:

strSQLa = "SELECT case FROM caselog WHERE DATEPART("m", caselog.dateR) = " & Me.comboboxname & ";"

I would also change the field name "case". It looks especially distracting as "Select Case" in the sql. Select Case is a vba statement.

 

[vbaInet]

As an alternative to DatePart("m", [field]), you can use Month([field]).

Just fyi - MonthName() is also function which you use to get the month name from the field like so:

MonthName(Month([field]))

However, I would go with GalaxiomAtHome's two columns suggestion because it would be much more effecient comparing against Integers.

 

[Xproterg]

Thank you for the replies. With a few modifications of the code, it works perfectly. I ended up using the month() function along with the modified date conversion function.

Really appreciate the learning experience.

 

[vbaInet]

Glad to hear that you've resolved this.

... along with the modified date conversion function.

Nothing wrong with this approach but having an extra column with those numbers would be much more effecient. Not a great deal of saving but effeciency should be priority (where possible).

 

[Galaxiom]

The custom function does seem a rather clumsy solution.

A one line alternative:
Month(CDate("1 " & Me!Comboname & " 2000"))
Doesn't matter what Year is used.

However the two column combo is far more elegant.
Demo attached.