combo box that has a value list

[icemonster]

ok.

i have an sql statement, i have managed to do a date range, even a search text.

but how do i exactly write the filter for a combo box that is filtering a calculated expression? whether. here's my sql

Function setReviewList()
'set starting sql statement
    StrStartSql1 = "SELECT qryHomemakerReviewDueList.ID, qryHomemakerReviewDueList.HomemakerReviewID, " _
                    & "qryHomemakerReviewDueList.[Homemaker Name], qryHomemakerReviewDueList.HireDate, " _
                    & "qryHomemakerReviewDueList.DateofReview, qryHomemakerReviewDueList.Notes, " _
                    & "qryHomemakerReviewDueList.Supervisor, qryHomemakerReviewDueList.InitialReview, " _
                    & "qryHomemakerReviewDueList.NextReview, qryHomemakerReviewDueList.ReviewDate, " _
                    & "qryHomemakerReviewDueList.ReviewStatus FROM qryHomemakerReviewDueList "

If Not IsNull(Me.txtSearch1) Then
    strWhereSql1 = "WHERE qryHomemakerReviewDueList.[Homemaker Name] Like '*" & Me.txtSearch1 & "*'"
Else
    strWhereSql1 = ""
End If

If Me.cboStatus1 > "" Then
   strWhereSql1 = "Where qryHomemakeReviewDueList.ReviewStatus Like '*" & Me.cboStatus1 & "*'"
Else
   strWhereSql1 = strWhereSql1 & "Where qryHomemakeReviewDueList.ReviewStatus Like '*" & Me.cboStatus1 & "*'"
End If

If Not IsNull(Me.txtStartDate1) And Not IsNull(Me.txtEndDate1) Then
    'read the dates selected in the variables
    dtStartDate1 = Me.txtStartDate1
    dtEndDate1 = Me.txtEndDate1
    If strWhereSql1 = "" Then
        strWhereSql1 = " WHERE ReviewDate Between #" & dtStartDate1 & "# " _
                    & "And #" & dtEndDate1 & "# "
    Else
        strWhereSql1 = strWhereSql2 & "AND ReviewDate Between #" & dtStartDate1 & "# " _
                    & "And #" & dtEndDate1 & "# "
    End If
End If

strSortOrderSql1 = " ORDER BY qryHomemakerReviewDueList.ReviewDate;"

strSQL1 = StrStartSql1 & strWhereSql1 & strSortOrderSql1
With Me.lstHomemakerReview
    .RowSource = strSQL1
    .Value = Null
End With

End Function

when i run this, i get a parameter value, help me please.

 

[pbaldy]

What does the prompt say? It will tell you what it can't find. Likely that is spelled wrong.

 

[icemonster]

no, there's no error. it just says parameter value, it's asking for the value. confused and dazed.

 

[pbaldy]

I know, but the parameter prompt should say the name of the field it can't find (right above the input box).

 

[icemonster]

the reviewstatus field, but it's the same though.

 

[pbaldy]

How about the name of the query preceeding it?

 

[icemonster]

i think my question was wrong, what i meant was, how do i add a WHERE clause on this VBA using a combo box that has a value list for a row source?

here's what i have done so far and it's not working:

Function setReviewList()
'set starting sql statement
    StrStartSql1 = "SELECT qryHomemakerReviewDueList.ID, qryHomemakerReviewDueList.HomemakerReviewID, " _
                    & "qryHomemakerReviewDueList.[Homemaker Name], qryHomemakerReviewDueList.HireDate, " _
                    & "qryHomemakerReviewDueList.DateofReview, qryHomemakerReviewDueList.Notes, " _
                    & "qryHomemakerReviewDueList.Supervisor, qryHomemakerReviewDueList.InitialReview, " _
                    & "qryHomemakerReviewDueList.NextReview, qryHomemakerReviewDueList.ReviewDate, " _
                    & "qryHomemakerReviewDueList.ReviewStatus FROM qryHomemakerReviewDueList "

If Not IsNull(Me.txtSearch1) Then
    strWhereSql1 = "WHERE qryHomemakerReviewDueList.[Homemaker Name] Like '*" & Me.txtSearch1 & "*'"
Else
    strWhereSql1 = ""
End If

[COLOR="Red"]If Me.cboStatus1 > 0 Then
   strWhereSql1 = "Where qryHomemakeReviewDueList.ReviewStatus Like " & Me.cboStatus1 & ""
Else
   strWhereSql1 = strWhereSql1 & "Where qryHomemakeReviewDueList.ReviewStatus Like " & Me.cboStatus1 & ""
End If[/COLOR]

If Not IsNull(Me.txtStartDate1) And Not IsNull(Me.txtEndDate1) Then
    'read the dates selected in the variables
    dtStartDate1 = Me.txtStartDate1
    dtEndDate1 = Me.txtEndDate1
    If strWhereSql1 = "" Then
        strWhereSql1 = " WHERE ReviewDate Between #" & dtStartDate1 & "# " _
                    & "And #" & dtEndDate1 & "# "
    Else
        strWhereSql1 = strWhereSql2 & "AND ReviewDate Between #" & dtStartDate1 & "# " _
                    & "And #" & dtEndDate1 & "# "
    End If
End If

strSortOrderSql1 = " ORDER BY qryHomemakerReviewDueList.ReviewDate;"

strSQL1 = StrStartSql1 & strWhereSql1 & strSortOrderSql1
With Me.lstHomemakerReview
    .RowSource = strSQL1
    .Value = Null
End With

End Function

 

[pbaldy]

I should have been clear instead of cute. The parameter prompt is coming because the name of the query is misspelled. This should make it clearer, working and non-working:

qryHomemakerReviewDueList.ID
qryHomemakeReviewDueList.ReviewStatus

 

[icemonster]

its not prompting parameter anymore, sorry. but it's not returning anything meaning the WHERE clause for the combo box is either wrong or something. the ReviewStatus is a calculated expression that goes something like this:

ReviewStatus: IIf([ReviewDate]<Date(),"Over Due","Review Soon")

what would be the best way to filter that in my sql code? combo box?

 

[icemonster]

also the combo box is value list and not table/query for its row source.

 

[pbaldy]

Since the field is text it would have to be:

Where qryHomemakeReviewDueList.ReviewStatus = '" & Me.cboStatus1 & "'"

I changed Like to = since you're not using a wildcard.

 

[pbaldy]

By the way, I think you have a logical flaw here:

If Me.cboStatus1 > 0 Then
   strWhereSql1 = "Where qryHomemakeReviewDueList.ReviewStatus Like " & Me.cboStatus1 & ""
Else
   strWhereSql1 = strWhereSql1 & "Where qryHomemakeReviewDueList.ReviewStatus Like " & Me.cboStatus1 & ""
End If

Based on what you're doing, I would think you'd want to test whether strWhereSql1 had anything in it yet, not the combo.

 

[pbaldy]

Sorry for the flood of posts, but I keep seeing things. The other problem with that code is that in the Else clause, you don't want the word "Where" again. You'd want " AND ".

 

[icemonster]

wow. i dont mind the flood post, that worked. cannot believe i missed the WHERE and the AND i have been copy pasting it. sorry it worked now. thanks again!

 

[pbaldy]

Ah good, glad we got it sorted out.