Date Retrieving Error

[toddw607]

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

[MMcCarthy]

Hi Todd

What is this function doing..

BusProgIDs = GetSqlBusinessProgram(ctl)

[toddw607]

Hi Todd

What is this function doing..

BusProgIDs = GetSqlBusinessProgram(ctl)

This function searches through a list box in order to place each ID of each record into the report.

[MMcCarthy]

This function searches through a list box in order to place each ID of each record into the report.

But it's adding it to the where criteria in the code.

[toddw607]

But it's adding it to the where criteria in the code.

It places the control from the list box into the seperate function

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

[MMcCarthy]

OK it looks like the problem is in the use of brackets. Try this ...

 
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
 

Mary

[toddw607]

OK it looks like the problem is in the use of brackets. Try this ...

 
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
 

Mary

Hi Mary,
It gace me the same error. I think my problem is with the function that this function calls, the GetsqlBusinessProgram maybe?

[MMcCarthy]

Hi Mary,
It gace me the same error. I think my problem is with the function that this function calls, the GetsqlBusinessProgram maybe?

You need to see what's actually in the criteria.

Put

Debug.Print strWhere

before the open report command and open the immediate window to see what's being printed

Then copy and paste it here.

Mary

[NeoPa]

Don't forget to use Copy / Paste Todd.
Your first post was obviously not done using the clipboard (there were typos in it) so it was devoid of most of its information.