Another Where Statement in code

lryckman · October 16th, 2007, 07:39 AM

Hi everyone!
I am getting better at these Where statements, but I have stumped myself again.
The group of code is below that isn't working right. I think I traced it to how I wrote 'WhereStatement', which I have in bold text and is about 3/4 the way down in my post, but I'm not positive that I'm correct, so I included the whole code for the button.(Some items pass on to a function in a module.)
In the DoCmd.OpenReport statement a little further down, If I change 'WhereStatement' to 'WhereStatus', 'WhereType', or any of the other statements that are assembled to make up 'WhereStatement', the report opens just fine, but if I use 'WhereStatement' then I get a "Property Not Found" Error.

(I am 'piecing' the Where Statement together because my form has several fields for the user to specify different criteria for their report.)

Thank you in advance for any help!

Code:

Private Sub cmdContinue_Click()
On Error GoTo Err_cmdContinue_Click

Dim stDocName As String
Dim AuditorName As String
Dim WhereName As String

    ReportCancel = False

    If IsNull(Me.[AuditorNameForReport]) Or Me.AuditorNameForReport = "" Then

        [AuditorNameForReport] = "*"

    End If

    AuditorName = [AuditorNameForReport]

    WhereName = "[4-Auditor] = '" & AuditorName & "'"

    If [AuditorNameForReport] = "*" Then

        WhereName = "[4-Auditor] Like '*'"

    End If


Dim stType As String
Dim WhereType As String

    If IsNull(Me.[cboFindingType]) Or Me.cboFindingType = "" Or Me.cboFindingType = "All" Then

        cboFindingType = "*"

    End If

    stType = [cboFindingType]

    WhereType = "[4-FindingType] = '" & stType & "'"

    If cboFindingType = "*" Then

        WhereType = "[4-FindingType] Like '*'"

    End If

Dim stStatus As String
Dim WhereStatus As String

    If IsNull(Me.[cboStatus]) Or Me.cboStatus = "" Or Me.cboStatus = "All" Then

        cboStatus = "*"

    End If

    stStatus = [cboStatus]


    If cboStatus = "Open" Then

        WhereStatus = "[7-ActualCompletionDate] Is Null"

    End If

    If cboStatus = "Late" Then

        WhereStatus = "([7-ActualCompletionDate] Is Null) AND ([6-PlannedCompletonDate] < Date())"

    End If

    If cboStatus = "Closed" Then

        WhereStatus = "[7-ActualCompletionDate] Is Not Null"

    End If

    If cboStatus = "Closed/Not Verified" Then

        WhereStatus = "([7-ActualCompletionDate] Is Not Null) AND ([8-ActionVerified] Is Null)"

    End If

    If cboStatus = "Closed/Verified" Then

        WhereStatus = "([7-ActualCompletionDate] Is Not Null) AND ([8-ActionVerified] Is Not Null)"

    End If

    If cboStatus = "*" Then

        WhereStatus = "[7-ActualCompletionDate] Like '*'"

    End If


Dim stTimePeriod As String
Dim WhereTime As String
Dim stCurrentYear As String
Dim stAuditedYear As String

    If IsNull(Me.[cboTimePeriod]) Or Me.cboTimePeriod = "" Or Me.cboTimePeriod = "All" Then

        cboTimePeriod = "*"

    End If

    stTimePeriod = [cboTimePeriod]

    If cboTimePeriod = "Between Specified Dates" Then

        DoCmd.OpenForm "frmDateRange"

        WhereTime = "(([qryCICorrectiveActionSingleReport].[2-AuditDate]) Between [tblDateRange].[1-StartDate] And [tblDateRange].[2-EndDate])"

        stTimePeriod = "Between " & [tblDateRange].[1-StartDate] & " and " & [tblDateRange].[2-StartDate] & ""

    End If

    If cboTimePeriod = "Current Year" Then

        stCurrentYear = Format(Date, "yyyy", vbUseSystemDayOfWeek, vbUseSystem)

        stAuditedYear = Format([2-AuditDate], "yyyy", vbUseSystemDayOfWeek, vbUseSystem)

        WhereTime = "[qryCICorrectiveActionSingleReport].[Year] = '" & stCurrentYear & "'"

    End If

    If cboTimePeriod = "Last Year" Then

        stCurrentYear = ((Format(Date, "yyyy", vbUseSystemDayOfWeek, vbUseSystem)) - 1)

        WhereTime = "[qryCICorrectiveActionSingleReport].[Year] = '" & stCurrentYear & "'"

    End If

    If cboTimePeriod = "*" Then

        WhereTime = "[2-AuditDate] Like '*'"

    End If

Dim WhereStatement As String

    WhereStatement = "'" & WhereName & "' And '" & WhereType & "' And '" & WhereStatus & "' And '" & WhereTime & "'"    
    If AuditorName = "*" Then

        AuditorName = "Any Auditors"

    End If

    If stType = "*" Then

        stType = "Any Type"

    End If

    If stStatus = "*" Then

        stStatus = "Any Status"

    End If

    If stTimePeriod = "*" Then

        stTimePeriod = "Any Date"

    End If

    ReportBy = "Audited by: " & AuditorName & "; " & stType & "; " & stTimePeriod & "; " & stStatus & ""

    stDocName = "rptAuditFinding"

    DoCmd.Close acForm, "frmAuditorPass"

    DoCmd.OpenReport stDocName, acPreview, , WhereStatement, acWindowNormal

    If ReportCancel Then
        DoCmd.Close acReport, "rptAuditFinding"
        DoCmd.OpenForm "frmMenu"
        DoCmd.CancelEvent
        Exit Sub
    End If

Exit_cmdContinue_Click:
    Exit Sub

Err_cmdContinue_Click:
    MsgBox Err.Description
    Resume Exit_cmdContinue_Click

End Sub

Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

mmcdonal · October 16th, 2007, 11:14 AM

Change this:

WhereStatement = "'" & WhereName & "' And '" & WhereType & "' And '" & WhereStatus & "' And '" & WhereTime & "'"

To This:

WhereStatement = WhereName & "' And '" & WhereType & "' And '" & WhereStatus & "' And '" & WhereTime & "'"

Did that help?

mmcdonal

mmcdonal · October 16th, 2007, 11:15 AM

Sorry, to this:

WhereStatement = WhereName & "' And '" & WhereType & "' And '" & WhereStatus & "' And '" & WhereTime

mmcdonal

mmcdonal · October 16th, 2007, 11:16 AM

Well, you get the idea. You have to watch the opening and closing "'". I didn't have time to review all the code, but I know you don't want it at the beginning of the string, or at the end when using WhereTime since that has a closing "'" already.

mmcdonal

lryckman · October 16th, 2007, 01:36 PM

Well, I'm not sure if that helped or not. :(
I understand what you were getting at...I do still get challenged with the all the " and '. I made the change you posted, but if I put a break in the code, and step through it, I still get the "Property Not Found" Error. But if I do not have a break in the code, I get "The Microsoft Jet database engine could not find the object ". Make sure the object exists and that you spell its name and the path name correctly."

Now I'm not sure where the problem is exactly.

Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

mmcdonal · October 16th, 2007, 02:20 PM

Oh, you are adding too many "'". I think you should do this:

WhereStatement = WhereName & " And " & WhereType & " And " & WhereStatus & " And " & WhereTime

There are other ways to build this that remove parameters if they are empty instead of using "*", which may also help.

mmcdonal

lryckman · October 16th, 2007, 02:55 PM

Oh!!! We are so close!
That fixed that error, but it led to another one that is probably the same type of issue. I have tested every other report option and this one (By specified dated) is the only one still giving a fit. It is giving me the same error as one of the others, "The Microsoft Jet database engine..."
The problem has to be in this little section of code. I've changed it since my original posting, so I included it again. (I removed specifying the table name since these controls are also on the form that this on click event is for, and I tried changing the quotes around.)

Do you see what I have wrong here?

Thanks again!!!

Code:

    If cboTimePeriod = "Between Specified Dates" Then

        'DoCmd.OpenForm "frmDateRange"

        WhereTime = "(([qryCICorrectiveActionSingleReport].[2-AuditDate]) Between '" & [1-StartDate] & "' And '" & [2-EndDate] & "')"

        stTimePeriod = "Between " & [1-StartDate] & " And " & [2-EndDate]

    End If

Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

mmcdonal · October 17th, 2007, 06:31 AM

I think you might want (without being sure what "Between Specified Dates" indicates:

If cboTimePeriod = "Between Specified Dates" Then

        'DoCmd.OpenForm "frmDateRange"

        WhereTime = "[qryCICorrectiveActionSingleReport].[2-AuditDate] Between #" & [1-StartDate] & "# And #" & [2-EndDate] & "#"

        stTimePeriod = "Between " & [1-StartDate] & " And " & [2-EndDate]

    End If

You won't need parens in strings you pass to Jet. That is something Jet will supply as needed. Also, the # indicates that the value in the string is a date. Did that help any?

mmcdonal

lryckman · October 17th, 2007, 07:12 AM

mmcdonal,
That worked perfect! You are always a great help and asset to this forum...Thank you so much! (I would really like to have just half of the knowledge that you do on programming Access.) I had posted a reply about my header reports not printing...if you are able to figure that one out, I'll really owe you! :)

Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

mmcdonal · October 17th, 2007, 07:14 AM

Thanks. I always learn a lot from the forum since I see and research a lot more issues than I would be able to experience with my own set of applications. I have been researching on the books online at http://wrox.books24x7.com and have not found that issue with the report header. I will take another look and check out MSDN as well. There might be a post there.

mmcdonal