MonthRef criteria doesn't work in the code

[aman]

Hi Guys

The below code to filter a listbox which works fine if we use supervisor filter and ActivityType filter but it doesn't work for MonthRef filter. MonthRef field has datatype 'Date/Time'

Private Sub cmdSearch()
Dim db As Database
Dim rs As Recordset
Dim bCriteriaEntered As Boolean
Dim sSQL As String

bCriteriaEntered = False
Me.lblStandards.Visible = True
Me.lstAllocation.Visible = True
sSQL = "SELECT * FROM qry_RMS_MinStand_AllocationList WHERE "

If Me.cboSupervisor <> "" Then
    sSQL = sSQL & "[SupRef] = '" & Me.cboSupervisor & "' AND "
    bCriteriaEntered = True
End If

If Me.cboMonthDue <> "" Then
    sSQL = sSQL & "[MonthRef]=#" & [Forms]![frm_RMS_MinStand_UpdateAllocation]![cboMonthDue] & "# And "
    bCriteriaEntered = True
    MsgBox sSQL
End If

If Me.cboActivityType <> "" Then
    sSQL = sSQL & "[ActivityTypeID] = " & Me.cboActivityType & " AND "
    bCriteriaEntered = True
End If

If Right(sSQL, 5) = " AND " Then
    sSQL = Left(sSQL, Len(sSQL) - 5)
End If

If Right(sSQL, 7) = " WHERE " Then
    sSQL = Left(sSQL, Len(sSQL) - 7)
End If

Me.lstAllocation.RowSource = ""

Me.lstAllocation.RowSource = sSQL

If bCriteriaEntered = False Then
    MsgBox "No criteria was entered", vbExclamation, "RMS"
End If

Can anyone please help me in this?
Thanks

 

[CJ_London]

what does 'doesn't work' mean? you get no results? wrong results? an error of some sort?

what does your data look like - excluding all formatting? MonthRef implies a number not a date. Since it is a date, confirm that you are consistent (e.g. 1st of month), perhaps you are showing '201810' or 'Oct 18' but the day part is not the 1st.

assuming it is a date then your cboMonthDue needs to be in the format mm/dd/yyyy for SQL to interprete it correctly within the # chars

 

[aman]

It doesn't give me any result (doesn't filter using MonthRef). MonthRef field in the table has Date/Time data type. cboMonthDue is a 2 column drop down box (1st column Date and second column is date in format mmmm,yy).

 

[CJ_London]

sorry, you are not being clear. you say

It doesn't give me any result

and

doesn't filter using MonthRef

it sounds like it is filtering (so using MonthRef) and returning no results.

If this is what you are saying then the monthref field is not matching the values in your combo.

Dates are stored as numbers (today is 43402) - so you do not match on what it is formatted as (e.g. mmmm,yy), you match on the number

you could try matching on a formatted string

sSQL = sSQL & "[COLOR=red]format([MonthRef],'mmmm.yy')="[/COLOR] & [Forms]![frm_RMS_MinStand_UpdateAllocation]![cboMonthDue[COLOR=red]].column(1) & " And[/COLOR] "

 

[JHB]

Is form "frm_RMS_MinStand_UpdateAllocation" open, (it should be)?

 

[aman]

yes its open

Is form "frm_RMS_MinStand_UpdateAllocation" open, (it should be)?

 

[JHB]

Place a Debug.Print sSQL just before the code line Me.lstAllocation.RowSource = sSQL and see if sSQL is like you expected it to be.

 

[aman]

Just added single quotes in below statement and it worked

Thanks CJ_London

sSQL = sSQL & "[COLOR=red]format([MonthRef],'mmmm.yy')='"[/COLOR] & [Forms]![frm_RMS_MinStand_UpdateAllocation]![cboMonthDue[COLOR=red]].column(1) & "' And[/COLOR] "

sorry, you are not being clear. you say

and it sounds like it is filtering (so using MonthRef) and returning no results.

If this is what you are saying then the monthref field is not matching the values in your combo.

Dates are stored as numbers (today is 43402) - so you do not match on what it is formatted as (e.g. mmmm,yy), you match on the number

you could try matching on a formatted string

sSQL = sSQL & "[COLOR=red]format([MonthRef],'mmmm.yy')="[/COLOR] & [Forms]![frm_RMS_MinStand_UpdateAllocation]![cboMonthDue[COLOR=red]].column(1) & " And[/COLOR] "