This is all the code for the button.
 The SQL code is at the bottom.

    Dim stDocName As String
    Dim str20 As String
    Dim str40 As String
    Dim sLink As String

    stDocName = "Floor Requests"

    If Me.chk20.Value <> -1 And Me.chk40.Value <> -1 Then
        MsgBox """Select a JITL""", vbOKOnly
    Else
        If Me.chk20 = -1 Then
            str20 = "Y"
            Else
            str20 = "N"
        End If
        If Me.chk40 = -1 Then
            str40 = "Y"
            Else
            str40 = "N"
        End If
    End If
    sLink = "[20] = " & "'" & str20 & "' AND [40] = " & "'" & str40 & "'"
DoCmd.OpenReport stDocName, acPreview, , sLink
Exit_btnPreviewReport_Click:
    Exit Sub
Err_btnPreviewReport_Click:
    MsgBox Err.Description
    Resume Exit_btnPreviewReport_Click
End Sub


SELECT T_New_Orders.id, T_New_Orders.Dsn_Nbr, T_New_Orders.twenty, T_New_Orders.forty, T_New_Orders.Emp_Nbr, T_New_Orders.Req_Date
FROM T_New_Orders
WHERE (((T_New_Orders.Req_Date) Between [Forms]![frm_Report].[cboStartDate] And [Forms]![frm_Report].[cboEndDate]));


-dave

I can tell you right off that this code won't work because you are passing "Y" and "N" to the query, and it needs "Yes" or -1, or "No" or 0. So since this condition can't be true, it won't return any records.

Otherwise this should work.

mmcdonal

I changed what you said, but the same thing happens.
An enter parameter value pop up comes up. It wants a value for 20. If I enter anything, another pop-up comes up thats wants parameter value for 40. If I enter a value there the report comes up with the title and labels, but nothing else.
I'm not sure what else to try. I thought this was going to be the quick part of the program, but its not cooperating.

thanks

-dave

Take out the sLink from the OpenReport line and see if that solves anything first. Is the query asking for the paramters, or is the report?

mmcdonal

I took out the sLink and it worked, as far as, using the date criteria.
I think the parameter value pop up is coming from the report b/c if I hit the cancel button on it another pop up comes up saying the OpenReport was cancelled.

dave

Try taking out the conditionals and sending this to the report:

str20 = Me.chk20
str40 = Me.chk40
sLink = "[20] = " & str20 & "' AND [40] = " & str40

Also, perhaps you are using too many parameters. You said earlier that if 20 is checked, 40 is not. So that means you can do 20 and 40 with one checkbox. Yes for 20, no for 40. Perhaps if you just send one condition (20 or 40) and see what happens there.

Also, you could send me the database if the data is not sensitive, and I could take a look at it.


mmcdonal

Well, I commented out the If statements and put that code in. It came back with an error "invalid us of Null".

I can send you the db if it will help.
I don't work for the gov or wal-mart so its not too sensitive.

dave

Maybe I'm not explaining this good enough.

There are two seperate fields in my table.
One field is twenty the Other is forty.
the entries in these fields are either "Y" or "N"
Every row has 1 "Y" and 1 "N", b/c the product is at either Production line twenty or Prod. line forty.

There are 2 checkboxes(not in a group) on my form.
If chk20 is ticked allrecords from field 20 that are Y should show.
If chk40...... 40
If Both....... 20,40
That is after the dates have been narrowed down.

I'm thinking I might can put this criteria in the query.


dave

Okay, that is different from what I thought.

You are passing these with code, and the query is further limiting the results by date.

Is the date part working first?

Once the date part is working by itself, we can work on the others.

The reason you got the invlaid use of Null is that you still used the sLink in the DoCmd line. You need to remove that from the line and send the OpenReport without the where clause, like this:

DoCmd.OpenReport stDocName, acPreview

If that works and is limited by date, then we can work on layering in the Y/N criteria. The problem here is that your criteria are exclusive. If 20 is Y, and 40 is N, then it will ONLY show instances where 20 is Y AND 40 is N, meaning it won't show cases where 20 is Y and 40 is Y. And if yo want to show cases where 20 is Y OR 40 is Y, then you need to write the WHERE clause with an OR, not an AND. So perhaps your user can select AND/OR? Is that too much for them to do?

Can you send me the database?


mmcdonal

Yes I can send it to you.
I zipped it up, but I don't see anyway to attach it to your e-mail.

The date part is working, but its showing a syntax error after the start date is selected.

dave