Hi Everyone,
      I am using Access 2003 to input 2 dates from text boxes to display a report. The following is the code I'm trying to run:
Dim fromDate As Date
    Dim toDate As Date
    Dim BusProgIDs As String
    Dim IncAllOpen As Boolean
    Dim strWhere As String
    Dim ctl As control
    Dim i As Integer

    If ckAllOpen.Value = True Then
        IncAllOpen = True
    Else
        IncAllOpen = False
    End If

   If lstBusinessProgramsPF.ListIndex > 0 Then

        Set ctl = lstBusinessProgramsPF

        fromDate = Format(txtFromProdFail.Value, "mm/dd/yyyy")
        toDate = Format(txtToProdDate.Value, "mm/dd/yyyy")
        BusProgIDs = GetSqlBusinessProgram(ctl)

        strWhere = ""
        strWhere = strWhere & BusProgIDs
        strWhere = strWhere & " AND CAConfirmationDate BETWEEN #" & fromDate & "# AND #" & toDate & "#"
        If IncAllOpen = True Then
            strWhere = strWhere & " OR (" & BusProgIDs

            strWhere = strWhere & " AND (CAConfirmationDate Is NULL)"
            strWhere = strWhere & " AND (OpeningDate <= #" & toDate & "#))"
        End If

        DoCmd.OpenReport "ProgressReport", acViewPreview, , strWhere, , (fromDate & "," & toDate)
    Else
        MsgBox "One or more Business Programs must be selected to view a report.", vbOKOnly
    End If


However, when I attempt to run it it gives me this error:
     syntax error(missing operator) in query expression '(AND CAConfirmationDate BETWEEN #12/12/2000# AND #12/12/2005

Anyone have any idea what would work? Thanks
             -Todd

I would suggest runing the query by example to consruct a query that works
then cut and past into your code
or use your code to catenate the query into a field to see what it looks like.

Does the date format you are using match the date format in the records?

Why not just add criteria to your query like this:

Column Date
Criteria: Between [Forms]![frmYourFormName].[txtFromProdFail] And [Forms]![frmYourFormName].[txtToProdDate]

However, your real problem is here:

strWhere = ""
strWhere = strWhere & BusProgIDs

Should be:
strWhere = "[BusProgID] = " & BusProgIDs

Assuming it is a number. If it is a string, then:

strWhere = "[BusProgID] = '" & BusProgIDs & "'"

Did any of that help?


mmcdonal

Hi mmcdonal,
Thanks for helping me out. I like the idea of the criteria but the thing is that I'm taking my input from the two date text boxes and applying them to the name coming from the list box which is all done in a form then I want to display all off the information about the list name as applies to the criteria in a report. I also tried to switch my code as you stated but neither one would would, it would just give me the same error. I'm pretty sure my error is coming from the
BusProgIDs = GetSqlBusinessProgram(ctl) function which gets the control of the name within the list. the code is as follows:
Private Function GetSqlBusinessProgram(lstControl As control) As String
    On Error GoTo GetSqlBusinessProgramError

    Dim sqlBP As String
    Dim i As Integer

    For i = 0 To lstControl.Items.Count - 1
        If Len(sqlBP) > 0 Then
            sqlBP = sqlBP & ","
        End If
        sqlBP = sqlBP & lstControl.ItemData(lstControl.Items(i))
    Next
    sqlBP = "BusinessProgramID IN(" & sqlBP & ")"
    GetSqlBusinessProgram = sqlBP

ExitSqlBusinessPrograms:
    Exit Function

GetSqlBusinessProgramError:
    MsgBox Err.Description, vbOKOnly
    Err.Clear
    Resume ExitSqlBusinessPrograms

End Function

Would this help?

What are you trying to do with this last function? It looks like you are just concatenating a bunch of strings. Then what do you do with this? Are you trying to use a comma delimited string as criteria? That should work with your IN() statement.

However, your loop shouldn't work since you are not incrementing the counter (i) in each loop. It should go on forever.

This:
For i = 0 To lstControl.Items.Count - 1
        If Len(sqlBP) > 0 Then
            sqlBP = sqlBP & ","
        End If
    sqlBP = sqlBP & lstControl.ItemData(lstControl.Items(i))
Next
sqlBP = "BusinessProgramID IN(" & sqlBP & ")"

Should be this:

For i = 0 To lstControl.Items.Count - 1
        If Len(sqlBP) > 0 Then
            sqlBP = sqlBP & ","
        End If
    sqlBP = sqlBP & lstControl.ItemData(lstControl.Items(i))
i = i + 1
Next
sqlBP = "BusinessProgramID IN(" & sqlBP & ")"

Did that help?




mmcdonal