Using form to enter criteria for a query 3

[shart00]

The query "qrySummary" has the field [CC] that is currently being updated by each user by criteria that always start with:
Between xxxx and xxxxx
what I need to do is allow the user to enter data on the form "frmEntry" that fills xxxx xxxx
the only thing is that it would be helpful to have a few extra places where they could enter a few or's.
I.E. if they ultimately need
Between 50 and 55 or 65 or 75 or 103 or 115
the could use the form that would resemble:
Between [a control to allow them to enter] and [a control] or
[ control]
or
[ control]
etc....

Any suggestions?

 

[FancyPrairie]

It's should be doing what you want. With the OR clause if any one is true (not all) then the record will be shown. If you used AND then they would all have to be true.

You need to debug your code. Modify the procedure that contains the DoCmd.OpenReport...BuildWhere(Me, " OR ") method, so it looks something like this:

    Dim strSQL as String
    Dim strWhere As String

    <your existing code...>

    strSQL = "SELECT tblcomplaints.ComplaintID, tblcomplaints.CustID, tblcomplaints.ProdCode, tblcomplaints.AcctNo, tblcomplaints.ActionTaken, tblcomplaints.DateResolved, tblcomplaints.Update, tblcomplaints.Cost, tblbestservice.bs_pooratt
FROM tblcomplaints INNER JOIN tblbestservice ON tblcomplaints.ComplaintID = tblbestservice.complaintid"

    strWhere = BuildWhere Me, " OR "

    If (len(strWhere) > 0) then strSQL = strSQL & " WHERE " & strWhere

    Debug.Print strSQL
    Stop

    Docmd.OpenReport ...BuildWhere(Me, " OR ")
    <rest of your code>

Now, before you run this code, when a module is open, select View | Immediate Window (or press Ctrl+G) to open debug's Immediate window. Now run your program. Access will stop execution of your code when it encounters the Stop statement. It will switch to the module view so that you can view your code. You should see the results of strSQL in debug's Immediate window. Highlight and copy the results. Now open the query builder (new) and switch to SQL view and paste the sql statement. Now run the query. It should show you what you would expect. If not, switch to design view and tweek it.

To use debug, place the word Stop prior to the line of code you want to examine. Or press F9 on the line of code you want to examine. F9 toggles breakpoint on/off. Now execute your code. Access will pause execution of your code when it encounters the word Stop or a breakpoint. To examine a variable, place the cursor on top of the variable name. A tooltip will show you the value of the variable. You could also examine its value by type ?YourVariablename within debug's Immediate window. To continue execution of your code 1 line at a time, press F8. To continue execution of your code until it encounters the next stop statement, breakpoint or ends, press F5.

 

[Vogue]

This is the what the immediate window is showing:

SELECT tblcomplaints.ComplaintID, tblbestservice.acctno, tblbestservice.bs_pooratt, tblbestservice.bs_mislead, tblbestservice.bs_lackinfo, tblbestservice.bs_pooretiquette, tblbestservice.bs_ackcust, tblbestservice.bs_lackassistance, tblbestservice.bs_excellentservice, tblbestservice.bs_lengthyonhold, tblbestservice.bs_indefinitewait FROM tblcomplaints INNER JOIN tblbestservice ON tblcomplaints.ComplaintID = tblbestservice.complaintid WHERE (tblbestservice.bs_pooratt = True) AND (tblbestservice.bs_mislead = True) AND (tblbestservice.bs_lackinfo = True) AND (tblbestservice.bs_pooretiquette = True) AND (tblbestservice.bs_ackcust = True) AND (tblbestservice.bs_lackassistance = True) AND (tblbestservice.bs_excellentservice = True) AND (tblbestservice.bs_lengthyonhold = True) AND (tblbestservice.bs_indefinitewait = True)

 

[FancyPrairie]

Your SQL statement is telling me that all 9 check boxes were selected and you called BuildWhere like this: BuildWhere(Me) or BuildWhere(Me, " AND "). Which means that all of the items must be TRUE in order for the record to be printed.

If you want it to match anyone of them, then all 9 check boxes must be selected and the OR clause must be used (BuildWhere(Me, " OR "))

SELECT tblcomplaints.ComplaintID, tblbestservice.acctno, tblbestservice.bs_pooratt, tblbestservice.bs_mislead, tblbestservice.bs_lackinfo, tblbestservice.bs_pooretiquette, tblbestservice.bs_ackcust, tblbestservice.bs_lackassistance, tblbestservice.bs_excellentservice, tblbestservice.bs_lengthyonhold, tblbestservice.bs_indefinitewait FROM tblcomplaints INNER JOIN tblbestservice ON tblcomplaints.ComplaintID = tblbestservice.complaintid WHERE (tblbestservice.bs_pooratt = True) OR (tblbestservice.bs_mislead = True) OR (tblbestservice.bs_lackinfo = True) OR (tblbestservice.bs_pooretiquette = True) OR (tblbestservice.bs_ackcust = True) OR (tblbestservice.bs_lackassistance = True) OR (tblbestservice.bs_excellentservice = True) OR (tblbestservice.bs_lengthyonhold = True) OR (tblbestservice.bs_indefinitewait = True)

As it is, you allow the user to select which items (check boxes) they want to filter the report on. For example, if the user wants the report to just display records where Poor Etiquette is TRUE, then they would only check the bs_pooretiquette check box. However, if you want to give the user the option to select all 9 check boxes, then create a button (or something) that when pressed, will select all 9 check boxes (or unselect).

 

[Vogue]

That's the thing...below is the code behind the " OR " and the " AND "preview report buttons. Both buttons show the BuildWhere value as this:
(tblbestservice.bs_pooratt = True) AND (tblbestservice.bs_mislead = True) AND (tblbestservice.bs_lackinfo = True) AND (tblbestservice.bs_pooretiquette = True) AND (tblbestservice.bs_ackcust = True) AND (tblbestservice.bs_lackassistance = True) AND (tblbestservice.bs_excellentservice = True) AND (tblbestservice.bs_lengthyonhold = True) AND (tblbestservice.bs_indefinitewait = True)

OR button
----------
Private Sub cmdpreview_Click()
On Error GoTo Err_cmdpreview_Click

Dim stDocName As String

stDocName = "rptreportcriteria"
DoCmd.OpenReport stDocName, acPreview
DoCmd.OpenReport stDocName, acPreview, , BuildWhere(Me, " OR ")
'MsgBox BuildWhere(Me, " OR ")
Exit_cmdpreview_Click:
Exit Sub

Err_cmdpreview_Click:
MsgBox Err.Description
Resume Exit_cmdpreview_Click
End Sub


This is the code for the " AND " button:
Private Sub cmdpreview2_Click()


On Error GoTo Err_cmdpreview2_Click

Dim stDocName As String

stDocName = "rptreportcriteria"
DoCmd.OpenReport stDocName, acPreview
DoCmd.OpenReport stDocName, acPreview, , BuildWhere(Me, " AND ")
MsgBox BuildWhere(Me, " AND ")
Exit_cmdpreview2_Click:
Exit Sub

Err_cmdpreview2_Click:
MsgBox Err.Description
Resume Exit_cmdpreview2_Click

End Sub

 

[FancyPrairie]

In a much earlier post I suggested recopying the code from the FAQ FAQ181-5497 (you discovered that tek-tips had truncated some of it.) I would suggest recopying and pasting the code into your database. Because every test I've done works with either option (i.e. " OR " and " AND")

 

[Vogue]

Excellent!!!!!
Everything works as it should now!! Thanks for your patience. I really appreciate.

 

[pbrown99]

From reading the begnning I know that this is something like what I need, but became lost in reading/understanding.

I need to run a delete query to delete holiday dates. After a lot of reading and trial and error, I have made a query similar to what it appears SHart00 was looking for by using a text field in my table. The problem is that I would like this to open a delete query so that Holidays can be delete from a schedule. In a way allowing the user to enter a Between xx/xx/xx and xx/xx/xx or between xx/xx/xx and xx/xxx/xx or xx/xx/xx or xx/xx/xx. I don't need as many ranges as SHart00, but it would not hurt. The problem is that with I use the code and this as the line:
DoCmd.OpenQuery "Delete Files Test", , BuildWhere(Me, "OR")

I get a type mismatch error.


Thank you for any and all help!

 

[FancyPrairie]

BuildWhere assumes Date ranges are structured like this:

Between DateBegin AND DateEnd

Not

Between DateBegin OR DateEnd

However, if you want something like this,

(Between DateBegin1 AND DateEnd1) OR (Between DateBegin2 AND DateEnd2) OR (...)

Then your call should be

DoCmd.OpenQuery "Delete Files Test", , BuildWhere(Me, " OR ")

NOTE that there is a space before and after the OR in the BuildWhere argument. " OR " not "OR".

 

[pbrown99]

I have been able to get the form to work with opening a report therefore I know the range feature is working. The problem is when trying to open a query with the "buildwhere".... If I use
Docmd.openquery "Delete Holidays",,,buildwhere(me," OR ")
the error:
"Wrong # of arguments or invalid property assignment"
appears, but if I try
Docmd.openquery "Delete Holidays",,Buildwhere(me," OR ") the error:
"Run-time error '13':
Type Mismatch
appears.

Any ideas from here?

Thank you for any and all help!