Query not working

[Fspinelli]

Hi and Happy New Year!

I have a QBF and a big challenge. I can't seem to get it to work...

I have two tables:
Contact
Contact_Type

I have a form:
Query_Window

On that form end users should be able to tick radio buttons (one or many - there are 16 of them) and/or use a text box where they can enter a full or partial word (one of these), and a few drop down boxes (three of these) that give the end user choices.

I have the query:
qrySearchStatus

To test the form, I chose a few radio buttons, a state and a priority, as soon as I click on the command button “Run Query” a window comes up and says:

Forms!Query_Window!TxtCompany
And it does this for each control and finally, no results. No matter what I chose on the form - even if it's just one radio button, or adding a few letters in the text box...

What am I doing wrong? The SQL to the query (qrySearchStatus) is below. I have no clue what to do.

Thank you!!!

SELECT
Contacts.Company,
Contacts.State,
Contacts.Country,
Contacts.Priority,
Contact_Type.BD,
Contact_Type.CO,
Contact_Type.CP,
Contact_Type.EN,
Contact_Type.FoF,
Contact_Type.FAM,
Contact_Type.FC,
Contact_Type.FO,
Contact_Type.GP,
Contact_Type.INS,
Contact_Type.IP,
Contact_Type.NHF,
Contact_Type.PB,
Contact_Type.SM,
Contact_Type.SWF,
Contact_Type.TH

FROM Contact_Type INNER JOIN Contacts ON Contact_Type.ContactID = Contacts.ContactID

WHERE

(((Contacts.Company) Like "*" & [Forms]![Query_Window]![TxtCompany] & "*")
AND
((Contacts.State)=[forms]![query_window]![cboState])
AND ((Contacts.Country)=[forms]![query_window]![cboCountry])
AND ((Contacts.Priority)=[forms]![query_window]![cbPriority])
AND ((IIf([forms]![Query_Window]![cb1],[BD]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb2],[CO]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb3],[CP]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb4],[EN]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb5],[FAM]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb6],[FC]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb7],[FO]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb8],[FoF]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb9],[GP]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb10],[INS]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb11],[IP]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb12],[NHF]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb13],[PB]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb14],[SM]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb15],[SWF]=True,True))=True)
AND ((IIf([forms]![Query_Window]![cb16],[TH]=True,True))=True))
OR (((Contacts.Company) Is Null)
AND ((Contacts.Country) Is Null)
AND ((Contacts.Priority) Is Null))
ORDER BY Contacts.Company;

[beacon]

Hi Fspinelli,

When you say...

To test the form, I chose a few radio buttons, a state and a priority, as soon as I click on the command button “Run Query” a window comes up and says:

Forms!Query_Window!TxtCompany
And it does this for each control and finally, no results. No matter what I chose on the form - even if it's just one radio button, or adding a few letters in the text box...

...is the window an input box asking you to type in data? Your SQL shows references to your form, so if you run the query, the query is going to look to the form for the parameters it needs to compile.

If you were to add a command button on the form and add code to the OnClick event for the command button that says...

DoCmd.OpenQuery "qrySearchStatus"
 

...your query would likely open without the input boxes appearing because you've now passed the parameters to the query. If the form isn't open or filled out when you click Run in the query, the query doesn't know what values to use where you have text like Forms!Query_Window!TxtCompany.

If this doesn't make sense, or if you want a visual to help you along, check out this site, http://www.fontstuff.com/access/acctut08.htm.

Hope this helps,
beacon

[Fspinelli]

no, it's not a query prompting for an end user to enter anything.

This is a form where they can make choices and run the query. The choices are radio buttons (16 of them). One or many could be ticked. Also on that form is a text box in case they want to query companies that have certain letters in them. Then there are also combo boxes. The combo boxes would be state, country and priority.

If the end user ticked a few radio buttons all records with those choices will come up. If the end user enters just a few letters in the text box, just companies with those letters would come up. If they had those radio buttons, the text box with letters AND they chose NY from the state drop down box, and 1 from the priority drop down box, then those records only should show up.

End user does not have to chose one or all, they could just choose to look at companies in NY or only those with a status of FoF, the query should ignore the null values (I have "is null" in the OR portion of the query column).

still not working - I have half a head of hair left...I am using your very nice tutorial, but it's not helping me with this particular inquiry.

Thank you very much, Beacon!

[beacon]

I wasn't talking about the query. You said that when you click Run, a window pops up that says Forms!Query_Window!TxtCompany, right? Is the window that pops up asking you to type anything into it or does it look like an error window?

If it's an input box it will look the attachment I've added below.

[Fspinelli]

your attachment is exactly what it looks like! every single control on the form wants me to do something like that! lol...

I am missing a big thing in my code, I'm sure!

[beacon]

No, you aren't missing anything in your code...You just need to open the form and use the form to open the query instead of running the query by itself.

The query is expecting values from your form, but since the form isn't open, the input boxes pop up asking you for the values instead. So if you open your form in Design View and add a command button, then change the OnClick event to say the following:

DoCmd.OpenQuery "qrySearchStatus"
 

...when you run the form, enter data into all of the fields, and then click the button, the query will open with the data it needs and the input boxes won't appear.

[Fspinelli]

Yea, I tried that code you gave me and I still get those input boxes. Perhaps I have things in the wrong area of the query? In the query I have the 16 options but also, next to them unchecked rows with that code in it.

Example:

BD is from contact_types. cb1 is the name of the radio button for BD on the form. BD shows in my query and checked so we can see it. Next to it is a colum but with this code:
(IIf([forms]![Query_Window]![cb1],[BD]=True,True))

Each one (BD is rb1, CO is rb2, CP is rb3...etc.

So I must be confusing it as much as it is confusing me!



I don't know how to attach else I would give you a picture of what my query looks like.

[Fspinelli]

Beacon - found it!!

attached is a sample of my query. There are 16 "status" options but I just put three down. At least you catch my drift (i hope.)

Thank you Thank you Thank you!!

[beacon]

Can you post pics of the form you're using?

[Fspinelli]

Here's a screen pic of my form.

p.s. I've added a subform (where the results should show up). Hence, why I have not mentioned it...

Thank you for all of your efforts!

[beacon]

I tried to recreate your database and I think I was able to get it to work.

To get the Company to work, keep it as is.

To get the checkboxes to work, I removed all of the...

(IIf([forms]![Query_Window]![cb1],[BD]=True,True)) 
 

...and typed...

[Forms]![Query_Window]![BD]
 

...into the first "Or" Criteria underneath the BD column. I did this for all of the checkboxes (I only used three when I tried to recreate it, but it should work for you, nonetheless).

Just for reference, here's the SQL from my test query:

SELECT CheckBox.TestID, 
       CheckBox.Company, 
       CheckBox.Check1, 
       CheckBox.Check2, 
       CheckBox.Check3
FROM CheckBox
WHERE 
(((CheckBox.Company) Like "*" & [Forms]![frmCheckBox]![Company] & "*") 
OR 
((CheckBox.Check1)=[Forms]![frmCheckBox]![AB]) 
AND 
((CheckBox.Check2)=[Forms]![frmCheckBox]![CD]) 
AND 
((CheckBox.Check3)=[Forms]![frmCheckBox]![EF]));
 

[Fspinelli]

Thank you very much, beacon!

I'll give it a try!