from form to report

[icemonster]

hello, i posted this on another part of this forum and sorry if am doing it again. but i really cannot figure out how to send the criteria of this vba to my report. any ideas is greatly appreciated. pbadly told me something bout removing the where but it doesnt seem to work.

this is the sql statement for the listbox with the underlying filters, what i want is, to send the result of this sql to the report.

Function setVisitDueList()
'set starting sql statement
        strStartSql2 = "SELECT qryVisitListVBA2.ID, qryVisitListVBA2.SupervisoryVisitID, qryVisitListVBA2.[Homemaker Name], " _
                    & "qryVisitListVBA2.HireDate, qryVisitListVBA2.InitialVisit, qryVisitListVBA2.SupervisoryVisitDate, " _
                    & "qryVisitListVBA2.ClientID, qryVisitListVBA2.TypeofHomemaker1, qryVisitListVBA2.TypeofHomemaker, " _
                    & "qryVisitListVBA2.NextVisitOn, qryVisitListVBA2.VisitDate, qryVisitListVBA2.supervisor FROM qryVisitListVBA2 "

If Not IsNull(Me.txtSearch2) Then
    strWhereSql2 = "WHERE qryVisitListVBA2.[Homemaker Name] Like '*" & Me.txtSearch2 & "*'"
Else
    strWhereSql2 = ""
End If
    

If Not IsNull(Me.txtStartDate2) And Not IsNull(Me.txtEndDate2) Then
    'read the dates selected in the variables
    dtStartDate2 = Me.txtStartDate2
    dtEndDate2 = Me.txtEndDate2
    If strWhereSql2 = "" Then
        strWhereSql2 = " WHERE VisitDate Between #" & dtStartDate2 & "# " _
                    & "And #" & dtEndDate2 & "# "
    Else
        strWhereSql2 = strWhereSql2 & "AND VisitDate Between #" & dtStartDate2 & "# " _
                    & "And #" & dtEndDate2 & "# "
    End If
End If

strSortOrderSql2 = " ORDER BY qryVisitListVBA2.VisitDate;"

strSQL2 = strStartSql2 & strWhereSql2 & strSortOrderSql2
With Me.lstSupervisoryVisit
    .RowSource = strSQL2
    .Value = Null
End With

End Function

but somehow, when i did what pbaldy told me, i did it like this:

DoCmd.OpenReport "rptReport", acPreview, , SDate = strWhereSql2

it doesnt work.

 

[pbaldy]

Don't accuse me of giving you that!

It would look like this:

DoCmd.OpenReport "rptReport", acPreview, , strWhereSql2

which presumes that the variable still has a value (you didn't answer that on the other thread), and that you've either not included the word "WHERE" in the first place, or stripped it out prior to using it here.

 

[icemonster]

that's what exactly i did. lol but it wont go through, i have a question on the report, is the control source for that alsothe same as the sql statement on my VBA?

 

[boblarson]

that's what exactly i did. lol but it wont go through, i have a question on the report, is the control source for that alsothe same as the sql statement on my VBA?

No, that's NOT exactly what you did. For the Report you would need this:

strWhereSql2 = "WHERE qryVisitListVBA2.[Homemaker Name] Like '*" & Me.txtSearch2 & "*'"

to be changed to this (withOUT the red part):

strWhereSql2 = "qryVisitListVBA2.[Homemaker Name] Like '*" & Me.txtSearch2 & "*'"

See - No WHERE word there.


And qryVisitListVBA2 would need to be part of the report's record source.

 

[icemonster]

am doing it, and there's no more query error but its returning nothing lols. what am i doing wrong.

 

[boblarson]

am doing it, and there's no more query error but its returning nothing lols. what am i doing wrong.

I think we're at a point that it would be helpful to get an upload of the database now (along with the description of what to look at).

 

[icemonster]

it's 33mb, when i shrink it, nothing.

someone really mentioned to me awhile back that the sql statement that am using for the listbox i can use is to filter the report as well. argh forgot how to use it.