USE strSQL to define strField - noob

[Emblem1]

I am trying to have vba run sql and use a field from the query as the data for strwhere.

It is existing code from the net I amtrying to modify.

I used to have the strField date location named directly in an old table, but the new table has mulitple dates for the same INT_#.

The result is a report filtered by date range, and the sql needs to pull the most recent date for each record. I think I have the sql right, but no idea of the right way to define strField.

Here is the code.

Thanks a buunch in advance.

Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Dim strSql As String

Const conDateFormat = "\#mm\/dd\/yyyy\#"



'Defines the location of the date used
strSql = "SELECTDISTINCTROW Max(tblIntersectionPMHistory.PM_Date) AS MaxofPM_Date, tblIntersectionPMHistory.[INT_#] " & vbCrLf & _
"FROM tblIntersectionPMHistory " & vbCrLf & _
"GROUP BY tblIntersectionPMHistory.[INT_#] " & vbCrLf & _
"ORDER BY Max(tblIntersectionPMHistory.PM_Date) DESC;"

strField = MaxofPM_Date

' FramePMRecords is the Option group containing each of the report names
Select Case FramePMRecords.Value
Case 1
strReport = "rptPMHistoryTrunklineOnly"
Case 2
strReport = "rptPMHistoryCityOnly"
Case 3
strReport = "rptPMHistoryBoth"
End Select


If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere
On Error Resume Next
DoCmd.OpenReport strReport, acViewPreview, , strWhere
If Err = 2501 Then Err.Clear

 

[LonnieJohnson]

Maybe you can get rid of the strSQL and use a DMAX function like so...

strField = DMAX("PM_Date","tblIntersectionPMHistory","[INT_#] = " & Me("INT_#"))

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!