my code is not working please halp

[icemonster]

can anyone point out what's wrong with my code? it's supposed to filter the enddate of a record from a unbound date field.

Function setRateList()
'set starting sql statement
        strStartSql = "SELECT tblCareGiverCompensation.CAREGIVERCOMPID, tblCareGiverCompensation.COMPRATE, " _
                    & "tblCareGiverCompensation.COMPDATEADDED, tblCareGiverCompensation.COMPENDDATE, " _
                    & "tblCareGiverCompensation.COMPEMPLOYEEID, tblCareGiverCompensation.COMPCLIENTID, " _
                    & "tblCareGiverCompensation.COMPSERVICETYPE, tblCareGiverCompensation.COMPRATETTYPE, " _
                    & "tblCareGiverCompensation.COMPCOMMENT FROM tblCareGiverCompensation "

If Me.cboEmployee > 0 Then
    lngEmployeeID2 = Me.cboEmployee
    'determine if there is any existing value in the "strwheresql" variable
    If strWhereSql = "" Then
        strWhereSql = "Where tblCareGiverCompensation.COMPEMPLOYEEID = " & lngEmployeeID2 & " "
    Else
        strWhereSql = strWhereSql & "And tblCareGiverCompensation.COMPEMPLOYEEID = " & lngEmployeeID2 & " "
    End If
End If

'check for values entered for start and ending date parameters
If Not IsNull(Me.txtEndDate) Then
    'read the dates selected in the variables
    dtStartDate2 = Me.txtStartDate
    If strWhereSql = "" Then
        strWhereSql = "WHERE tblCareGiverCompensation.COMPENDDATE > #" & dtStartDate2 & "# "
    Else
        strWhereSql = strWhereSql & "AND tblCareGiverCompensation.COMPENDDATE > #" & dtStartDate2 & "# "
    End If
End If

strSortOrderSql = " ORDER BY tblCareGiverCompensation.COMPDATEADDED DESC;"

strSql2 = strStartSql & strWhereSql & strSortOrderSql
With Me.txtRate
    .RowSource = strSql2
    .Value = Null
End With

End Function

 

[pbaldy]

What does "is not working" mean? What type of control is txtRate? Add this line after the string is built:

Debug.Print strSql2

which will print the finished SQL out to the VBA Immediate window. If you don't spot the problem, post the SQL here.

 

[icemonster]

my bad, it's actually working i got a typo in, at

If Not IsNull(Me.txtEndDate) Then
    'read the dates selected in the variables
    dtStartDate2 = Me.txtStartDate

as you may see, the Me.txtEndDate should also be Me.txtStartDate, but a question though, how do i add a line that will run the sql but return 0?

 

[Galaxiom]

I do hope you are using Option Explicit and declaring your variables and just left this out of the post. Working with undeclared variables is asking for trouble and can result in very hard to find errors.

Note that when a single table is used in a query there is no need to qualify the fieldname with tablename. Leaving it out makes the code much more readable particularly with such long tablenames.

Readability is what good coding is all about and while on this subject:

'determine if there is any existing value in the "strwheresql" variable
If strWhereSql = "" Then

'read the dates selected in the variables
dtStartDate2 = Me.txtStartDate

Avoid commenting the obvious. The instruction itself says it better.

Comments are supposed to help the reader to understand the flow of the procedure but stuff like like this is clutter that does exactly the opposite. White space would do more for readabiliy in those particular lines.

 

[pbaldy]

What do you mean return 0? You can open a recordset on the SQL if you want to see if it returns records or do something else with the records.