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?

 

[Vogue]

When I click on the button I'm getting this error: "Compile Error. Can't find project or library" and it's highlighting the first Left$ in the Build Range part of the function.
If I change the code to Msg BuildWhere (Me, "OR") I get the same error.

 

[FancyPrairie]

Not sure why you are getting the Compile error. So let's start over.

Start by deleting the module that contains all of the code from this post. Two reasons for this:
1. I have since written a FAQ that contains these routines. And it is more up-to-date.
2. There is at least one bug in this routine. And since I have a FAQ with basically the same code, I don't want to attempt to fix bugs in 2 places (here and the FAQ)

Now, create a new module and copy and paste the code from this FAQ faq181-5497 and try again. It should work.

 

[Vogue]

I replaced the code like you advised. Different compile error this time: "Sub or Function not defined" at the BuildWhere_ControlType(frm,ctl)function call in the Create Where Clause for current control section.

 

[FancyPrairie]

WOW! I messed up. I put the extra code in to handle your OR specification within the code in the FAQ. However, after testing it, I copied and pasted it from my module into the FAQ. But I had turned Full Module View off. So when I copied it from my module, I only got the first function, not the rest of them. I'm sure glad you picked up on it, while I still had my copy in my database. Otherwise, my FAQ would be worthless.

Anyway, delete your module again and, again, copy and paste the functionS from the FAQ. It should work this time.

Sorry 'bout that. But, again, I'm sure glad you caught it.

 

[Vogue]

I think the bottom of your FAQ got truncated, so I copied the remainder from this thread.

If I use DoCmd.OpenReport "rptcomplaints", acViewPreview, , BuildWhere(Me) to run the report it prompts for the value of the checkbox I have selected. If I type in true it then displays the report but it contains results that haven't been checked on the form.

If I use DoCmd.OpenReport "rptcomplaints", acViewPreview, , BuildWhere(Me, "OR") the routine stops at this line of the BuildWhere function "Err.Raise Err.Number, "BuildWhere (" & strCtlType & ");" & Err.Source, Err.Description" with the following error: Runtime error. Object required.

 

[FancyPrairie]

Sorry you're having so many problems. You're right. My code was truncated. When I went into the FAQ to fix it, it's all there. Must be a problem or limitation of tek-tips. I deleted some comments and it all shows up now. Then I couldn't get back into tek-tips til now.

Back to your problem. Let's get it resolved now.

Post what you entered for the tag property of the check box.

Also, note that "OR" in BuildWhere(Me,"OR") show be surrounded by spaces (i.e. " OR ").

 

[Vogue]

I changed the "OR" to " OR " and it does generate the report without any prompts or other errors

Thing is though, the report contains more info that I specified on the form. Maybe I created the report wrong??

This is my tag statement for the checkbox: Where=tblbestservice.chkbs_pooratt,Boolean,=,True;

 

[FancyPrairie]

Assuming that your report's recordsource is based on a query. Therefore, open that query in design view and add your where clause to it and see if it produces the correct results. The Where clause you build should be the one generated by BuildWhere. That's why I suggested instead of opening your report with BuildWhere, just display the results in a message box. Something like this;

' Docmd.openreport .....
Msgbox BuildWhere (Me, " OR ")

Your query should look something like this;

Select * from tblBestService Where tblBestService.chkbs_pooratt = true;

 

[Vogue]

If I display the results in a message box this is what I get: (tblbestservice.chkbs_pooratt = True)

 

[FancyPrairie]

Exactly what I would expect. Now, using the Query Builder, open, in design view, the query your report is using.

Switch from design view to SQL view and at the end of your SQL statement (prior to the semicolon () enter Where (tblbestservice.chkbs_pooratt = True). (At this point you can switch back to design view).

Now run your query and see it it gives you what you're expecting.

 

[Vogue]

No Luck
This is the SQL view of my statement
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 Where (tblbestservice.chkbs_pooratt = True);

With the Where statement attached at the end I get a prompt to enter the value of tblbestservice.chkbs_pooratt

I think I see the problem....there's a form which is used to collect the categories of customer complaints. This form is called frmcccategories and it's record source is a table called tblbestservice. The field which stores the value of chkbs_pooratt is called bs_pooratt.
There is another form called frmreport which is used to select the criteria to run the report.
Somehow the report is returning the true result for the form frmreport even though the bs_pooratt field of the table tblbestservice is showing false.

 

[FancyPrairie]

Here's your query without the Where clause.

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

Go into the Query Builder design view and make the query work like you want it to work with a where clause. Once it's working then swith to SQL view and view the where clause. Now you should see how it should be constructed for the Tag property.

 

[Vogue]

Ok Fancy Prairie
It worked once! When I changed the Where statement of the checkbox it ran the report perfectly!!!
Then I decided to enter the Tag field for the rest of the checkboxes on the form and that's when I ran into problems.
When I try to execute the report now I'm getting this error: "Run-time error '438': Object doesn't support this property or method" and the debug highlights this line of the BuildWhere function: Err.Raise Err.Number, "BuildWhere (" & strCtlType & ");" & Err.Source, Err.Description

 

[FancyPrairie]

I just copied and pasted the functions from the FAQ into a module and then created a form with 4 checkboxes. I set the tag properties and ran it and it worked fine. You must have a syntax error in your tag property. Post all of them.

 

[Vogue]

Tags are as follows:
Where=tblbestservice.bs_pooratt,Boolean,=,True;
Where=tblbestservice.bs_mislead,Boolean,=,True;
Where=tblbestservice.bs_lackinfo,Boolean,=,True;
Where=tblbestservice.pooretiquette,Boolean,=,True;
Where=tblbestservice.bs_ackcust,Boolean,=,True;
Where=tblbestservice.bs_lackassistance,Boolean,=,True;
Where=tblbestservice.bs_excellentservice,Boolean,=,True;
Where=tblbestservice.bs_lengthyonhold,Boolean,=,True;
Where=tblbestservice.bs_indefinitewait,Boolean,=,True;

 

[FancyPrairie]

It worked for me. I created 9 checkboxes and copied and pasted your tag properties into mine. Everything works fine. I'm wondering if, when you copied the FAQ and it was truncated, you didn't insert some bad data. You might try copying and pasting it again, just to make sure. That's what I just did to verify that the functions work.

 

[Vogue]

Well I tried that and still nothing...

 

[Vogue]

Is there some reference that I need to enable perhaps?

 

[FancyPrairie]

No references needed. I was wondering last night, did you accidently place the where information in the tag property of the label rather than the check box itself?

Also, try this. Disable all 9 check boxes. Then enable them one at a time. Each time you enable one, run your code again (i.e. MsgBox BuildWhere(Me, " OR ")) That should tell you which one is wrong.

 

[Vogue]

Tek-tips site seems to be having some problems today so I was only recently able to get back on.
I did what you advised but that didn't work either.
What I had to do was recreate the form and that seems to have rectified the problem. Not sure what was wrong with the form.
One thing I'm noticing though, if I keep changing the checkboxes on the form and execute the report eventually it displays no results. I then have to close the form and the query and re-run the report for it to show the correct results.

On another note...this displays results that match ALL the criteria selected in the checkboxes.
Is there a way to display results once it matches any one of the criteria selected in the checkboxes?