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 CAConfirmationD ate 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 GetsqlBusinessP rogram maybe?

[MMcCarthy]

Hi Mary,
It gace me the same error. I think my problem is with the function that this function calls, the GetsqlBusinessP rogram 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.