Tempvars_Sql Help

[ria4life]

I got this code online and im trying to modify it to suite my needs.

i would like to execute a filter where:
Title_Number in QRY_Calender_Large = [Tempvars]![TempTitleNo]
Any help is appreciated

Public Sub DisplayMeetings()
    
'   Add any meetings that occur this month to the proper day box on the form
    
    Dim strSQL As String
    Dim intTemp As Integer
    Dim intDays(37) As Integer
    Dim strDays(31) As String
    Dim strTitle As String
    Dim strApptStartTime As String
    Dim strName As String
    Dim strDate_Selected As String
    Dim r As Integer
    Dim rst
    
'   To start, clear all meetings from every day
    For r = 1 To 37
        Me("Day" & Trim$(r)) = ""
    Next r

'   Grab this month's meetings from the tblAppointments table
'   Sorted by TIME, but not by DATE for some reason
strSQL = "SELECT QRY_Calender_Large.* " & _
             "FROM QRY_Calender_Large " & _
             "WHERE Month([Date_Selected])= " & intPubMonth & " AND Year([Date_Selected]) = " & intPubMyYear & _
            " ORDER BY ApptStartTime;"
             

    Set rst = CurrentDb.OpenRecordset(strSQL)

 

[The_Doc_Man]

Where your are building your SQL string, add another AND sub-clause to make the WHERE clause look like...

"WHERE Month([Date_Selected])= " & intPubMonth & " AND Year([Date_Selected]) = " & intPubMyYear & " AND [Title_Number] = " & [Tempvars]![TempTitleNo] & _

As long as you are separately building the SQL string, that step is in Access context (i.e. not yet sent to the DB engine), which means you have access to the programming context where the Tempvars were stored. So you can just build the string to include values therefrom.

Note that depending on data format, you MIGHT need quotes like

 " AND [Title_Number] = [COLOR="Red"]'[/COLOR]" & [Tempvars]![TempTitleNo] & "[COLOR="red"]'[/COLOR]" & ...

 

[ria4life]

This worked like a charm.

thank you soo much

 

[The_Doc_Man]

Always glad to help.