SQL statement returning ALL fields in query/report - Query works otherwise.

[zy0n1c]

So, I've got my multi-state query working, I've got my multi-checkbox query working, but when I add a 2nd checkbox to the statement below, it shows a query and/or report with ALL fields showing in a horrible layout/design and the relevent data for this report isn't even listed. I believe the problem is due to SELECT EMployeeDB.* -- the asterisk being a wildcard seems like it's telling the query to select ALL the fields. If I remove this part of the statement, I get prompts for the primary ID, firstname, lastname, etc. When nothing is entered into these prompts, they continue into an error " Microsoft Access can't find the field 'EmployeeDB.[Street Address 1]' referred to in your expression. You may have misspelled the field name, or the field may have been renamed or deleted.

SELECT EmployeeDB.[First Name], EmployeeDB.[Last Name], EmployeeDB.State, EmployeeDB.[Phone Number 1], EmployeeDB.[Phone Number 2], EmployeeDB.[Basic+], EmployeeDB.TWIC, EmployeeDB.HAZWOPER, EmployeeDB.Hydroblasting, EmployeeDB.[Scaffold Builder], EmployeeDB.[Confined Spaces], EmployeeDB.[OSHA 10], EmployeeDB.[OSHA 40], EmployeeDB.[CDL w/VAC], EmployeeDB.[CDL (standard)], EmployeeDB.[Valid Driver's License], EmployeeDB.[Job Types], EmployeeDB.[Site Specific Training 1], EmployeeDB.[Site Specific Training 2], EmployeeDB.[Site Specific Training 3], EmployeeDB.[Site Specific Training 4], EmployeeDB.[Site Specific Training 5], EmployeeDB.[Site Specific Training 6], EmployeeDB.[Eligible for Rehire], EmployeeDB.Status, EmployeeDB.*
FROM EmployeeDB
WHERE (((EmployeeDB.State) Is Null Or (EmployeeDB.State)=[Forms]![EmployeeListMenu]![ComboState1] Or ((EmployeeDB.State) Is Null Or (EmployeeDB.State)=[Forms]![EmployeeListMenu]![ComboState2]) Or ((EmployeeDB.State) Is Null Or (EmployeeDB.State)=[Forms]![EmployeeListMenu]![ComboState3]) Or ((EmployeeDB.State) Is Null Or (EmployeeDB.State)=[Forms]![EmployeeListMenu]![ComboState4])) AND ((EmployeeDB.[Basic+]) Like IIf([forms]![EmployeeListMenu]![CheckBasicPlus],True,"*")) AND ((EmployeeDB.HAZWOPER) Like IIf([forms]![EmployeeListMenu]![CheckHAZWOPER],True,"*")));

[isladogs]

Remove EmployeeDB.* as you already have lots of fields from that table.
Alternatively, remove all the other fields from that table and just use the EmployeeDB.*

[zy0n1c]

Either way, with EmployeeDB.* and with all the fields WITHOUT EmployeeDB.* - I get an output of every field.

[Minty]

There is an option in the query designer properties to "Output All Fields" - I would check that setting

[zy0n1c]

Output All Fields is set to "No"

[Minty]

That's a report view - you would have to explicitly remove the fields from the report for them to disappear from the report view.
Simply removing them from the query won't stop the report asking for the field in the report design controls sources

[zy0n1c]

...I don't know what happened. I have that exact report printed out on my desk from yesterday from before I added the 2nd checkbox. It has only the fields I designed the report to have. I spent an hour making that report look "pretty." yesterday. Now, when I open my database from yesterday (I save a fresh copy each morning and save it all throughout the afternoon), all those fields are showing up in the design view that were NOT there yesterday.

I guess I'm going to start over from scratch and try to work this out from the beginning. I hate recreating these reports....

[davegri]

Watch out for this-- If you have a table or query highlighted in the Nav Pane, and on the ribbon you click Create>Report, a report will be created with ALL the fields in that query or table. If you close the report, Access will ask if you want to save it and give it a name.
If you specify an existing report name or take the default, it will overwrite any existing report with that name. Maybe this gotcha.

[zy0n1c]

I started from scratch and decided to get my query working properly before even bothering with the report. Going to post a new thread with my new wonderful issue!