Where statement problem

lryckman · June 27th, 2007, 02:07 PM

Can anyone help me with my where statements in my case statements (# 1-3 specifically)? I'm not real great at these, and it's probably something real simple.
The queries that I'm using as filters all have the same fields as the report, just one has the criteria of field [8-DateComplete] as null and the other as not null.
The error message I am getting is as follows:
On case # 1 is "Invalid use of '.', '!', or '()'. in query expression '(((((tblDesignLog.[8-DateComplete]) Is Null)))) AND ([15-Designer] = C. Cranfill)'."
On case # 2 is "Invalid use of '.', '!', or '()'. in query expression '(((((tblDesignLog.[8-DateComplete]) Is Not Null)))) AND ([15-Designer] = C. Cranfill)'."
On case # 3 is "Invalid use of '.', '!', or '()'. in query expression '([15-Designer] = C. Cranfill)'."

Here is my code:

Code:

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

    Dim stDocName As String

    Dim PrintOption As String

    Dim Name As String

    Dim CompDate As Date

    CompDate = "01/01/05"

    stDocName = "rptDesignLog"

    PrintOption = InputBox("Which do you want to print?  " & _
    "Please enter the corresponding number only." & _
    vbCrLf & "1 - Open Design log for only you" & _
    vbCrLf & "2 - Completed design log for only you" & _
    vbCrLf & "3 - All of the Design log for only you" & _
    vbCrLf & "4 - Open Design log for all designers" & _
    vbCrLf & "5 - Completed design log for all designers" & _
    vbCrLf & "6 - All of the Design log for all designers", "Printing Options")

    Select Case PrintOption

        Case 1

            Name = InputBox("Please enter you name in the format of 'F. Last'", "Name Please")

            DoCmd.OpenReport stDocName, acViewPreview, "qryDesignLogOpen", "" & _
            "[15-Designer] = " & Name, acWindowNormal

        Case 2

            Name = InputBox("Please enter you name in the format of 'F. Last'", "Name Please")

            DoCmd.OpenReport stDocName, acViewPreview, "qryDesignLogComplete", "" & _
            "[15-Designer] = " & Name, acWindowNormal

        Case 3

            Name = InputBox("Please enter you name in the format of 'F. Last'", "Name Please")

            DoCmd.OpenReport stDocName, acViewPreview, , "" & _
            "[15-Designer] = " & Name, acWindowNormal

        Case 4

            DoCmd.OpenReport stDocName, acViewPreview, "qryDesignLogOpen", , acWindowNormal

        Case 5

            DoCmd.OpenReport stDocName, acViewPreview, "qryDesignLogComplete", , acWindowNormal

        Case 6

            DoCmd.OpenReport stDocName, acViewPreview, "qryDesignLog", , acWindowNormal

        Case Else

    End Select

Exit_cmdPrint_Click:
    Exit Sub

Err_cmdPrint_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrint_Click

End Sub

Thanks!!!

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!

robzyc · June 28th, 2007, 02:41 AM

Hi Laura,

For string variables in SQL WHERE clauses you need to encase in single quotes '.

This will stop the Access from trying to treat it as a seperator.
e.g.
"[15-Designer] = '" & Name & "'"

I hope this helps,

Kind Regards,
Rob

mmcdonal · June 28th, 2007, 06:46 AM

Also, in this phrase:

DoCmd.OpenReport stDocName, acViewPreview, "qryDesignLogComplete", "" & _
            "[15-Designer] = " & Name, acWindowNormal

Why do you have the "" before the &? That won't add anything - not even a space. Are you missing a comma here?

DoCmd.OpenReport stDocName, acViewPreview, "qryDesignLogComplete", "", & _
            "[15-Designer] = " & Name, acWindowNormal

And yes, it Name is a string, then is should be this:

DoCmd.OpenReport stDocName, acViewPreview, "qryDesignLogComplete", "" & _
            "[15-Designer] = '" & Name & "'", acWindowNormal

However, Name is a reserved word. So perhaps you should change that variable name to "sName" (for String Name).

Did that help?

mmcdonal

lryckman · June 28th, 2007, 07:15 AM

robzyc & mmcdonal,
Thank you both for your help! That fixed my issue and it works great now.

mmcdonal - you asked...

Quote:

quote: Also, in this phrase:

DoCmd.OpenReport stDocName, acViewPreview, "qryDesignLogComplete", "" & _
"[15-Designer] = " & Name, acWindowNormal

Why do you have the "" before the &? That won't add anything - not even a space. Are you missing a comma here?

I did this because I first just had ...
DoCmd.OpenReport stDocName, acViewPreview, "qryDesignLogComplete", & _
"[15-Designer] = " & Name, acWindowNormal
Access didn't like the & sign, so I put the "" in front of it...I forgot that I didn't even need the & sign unless I continued the line in the middle of what I was listing for the different requirements and it was actually the cause of that error. Your comment jarred my memory that I didn't need it...so thank you for that also!

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!

SerranoG · June 29th, 2007, 08:46 AM

Two things: You declare CompDate As Date but then you assign a string to it.

CompDate = "01/01/05"

That shoud read

CompDate = #01/01/05#

Second, you just have to clean up your DoCmd statements to properly address strings.

DoCmd.OpenReport stDocName, acViewPreview, , "[15-Designer] = '" & Name & "'", acWindowNormal

I'm not sure why you have a filter name AND a where clause in your OpenReport statement. The report itself is based on a query, so I find that redundant.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division