Filter Report Record Source (Text to Object troubles)

[timwcpumps]

Hello! First post at these forums.

I'm trying to open a Report and filter it's record source. I've had success with the following:

SELECT *
FROM TestTable
WHERE (((TestTable.[Column]) = True));

These achieves most of my goal, filtering the source to where the column named "Column" is checked.
My hope is to change this so it will find the column's name from a text box in a form. I want to do this so I can dynamically select which column the filtering is based on rather than having to go in and manually change it.
This doesn't work, but I'm thinking something along the lines of:

SELECT *
FROM TestTable
WHERE (((TestTable.[Forms!TestForm!Textbox.Text]) = True));

Does this make sense? I image it is possible in some way or another, however I'm pretty unfamiliar with Access syntax.


Thanks!
-Tim

[Bulzie]

Go into your report design, go to Data tab in properties, go to RecordSource and select the button with the "..." on it to get to the query designer). Now select that table if it is not already there, add the columns if not already there and put in your criteria. In the criteria box of that COlumn field, right click and choose Build, then click on the form and find the field you want to reference and double click it. This will give you the correct syntax as needed. Once you have it like you think, run it and see if it returns the data you want. If not, go back to design and see what is off. The query designer is a powerful visual tool that can help you figure out what you need.

[pbaldy]

The question implies a normalization problem. I don't think you can do what you want without code. You'd have to build the SQL in code:

VariableName = "SELECT * FROM TestTable WHERE " & Forms!TestForm!Textbox & " = True"

then use that variable as the source of a form/report/whatever.

[timwcpumps]

Go into your report design, go to Data tab in properties, go to RecordSource and select the button with the "..." on it to get to the query designer). Now select that table if it is not already there, add the columns if not already there and put in your criteria. In the criteria box of that COlumn field, right click and choose Build, then click on the form and find the field you want to reference and double click it. This will give you the correct syntax as needed. Once you have it like you think, run it and see if it returns the data you want. If not, go back to design and see what is off. The query designer is a powerful visual tool that can help you figure out what you need.

Bulzie, this is what I did to get the code I started with. I'm able place criteria on specific columns if I name them in advance (through builder or otherwise) but I'm wanting to have the name of the column be variable.

The question implies a normalization problem. I don't think you can do what you want without code. You'd have to build the SQL in code:

VariableName = "SELECT * FROM TestTable WHERE " & Forms!TestForm!Textbox & " = True"

then use that variable as the source of a form/report/whatever.

Pbaldy, how do I go about building the SQL code? Is it an object I put in my database?

Thank you both for fast responses.
-Tim

[pbaldy]

Pbaldy, how do I go about building the SQL code? Is it an object I put in my database?

What are you trying to do with the SQL? The final goal will help determine the best method to get there.

[timwcpumps]

What are you trying to do with the SQL? The final goal will help determine the best method to get there.

It may seem convoluted, but I'm trying to make a system that uses a form print specification sheets for product models. I have a large table with spec data in the first few columns and the columns following are True/False values that indicate if they apply to the product model.
It works if I specify the product model (name of column) in advance, but I'd prefer it to pull the name of which column to use from the form when the report is opened.

My current solution is putting a lot of IF statements in the button that opens the report, which go on like "If model = XZ.2 then open report where 'XZ.2 = true', elseif model = YL.3 then open report where 'YL.3 = true', elseif...." and so on.

If it's getting confusing, I think I explain it best in the original post.

[pbaldy]

As I mentioned in my first post, the design is suspect. I'd say flawed actually. Product models that apply should be records in a related table, not fields. You don't want to have to change the design of tables/forms/reports just because you get a new model. More here:

http://www.r937.com/Relational.html

You could create code to handle your fields and open a report, but in the long run I think you're better off fixing the design.

[timwcpumps]

As I mentioned in my first post, the design is suspect. I'd say flawed actually. Product models that apply should be records in a related table, not fields. You don't want to have to change the design of tables/forms/reports just because you get a new model. More here:
http://www.r937.com/Relational.html
You could create code to handle your fields and open a report, but in the long run I think you're better off fixing the design.

I was not familiar with normalization, I'll look into it. Thank you for the resource.
I had assumed creating new columns in my table would be as trivial as adding new records but hadn't looked into it yet, so I figured the design was a preferred solution.
I suppose the end goals really are to allow users to add/remove product models, assign specs to the models and that the system would have only one report that it's all pushed through for the sake of only having to format once (as opposed to creating a new report for every product model).
Thanks!

[pbaldy]

No problem, post back if you get stuck. Your app will be much more dynamic if normalized. If you get a new model, users just add it to a "models" table via an interface you build. It flows through everywhere from that, you the developer don't do anything. As you have it now, when they get a new model you not only add a new field to the table, which is certainly easy, but you also have to change forms and reports. Getting a new model shouldn't require the developer to do anything. We're lazy, we don't want all that extra work.

[timwcpumps]

Glad to hear it! Thanks for assisting me in my path towards Access enlightenment.
I'll definitely bounce back here whenever I have more questions. Thanks!

[pbaldy]

Oh, and another potential problem you'd have is the limit on the number of fields in an Access table, 255. Normalized, you can have a virtually unlimited number of models.

[timwcpumps]

I think I need some help understanding the best way to organize some of this data.
The current design actually has two tables, one for the product models and one for the specifications that can be applied to different models. I made a little graphic.

I'm struggling to figure out what to do with the data circled there if it is not in true/false columns for each model. The initial thinking was to create a Query for each model filtering based on each column, and then creating a Report for each model connected to each Query. (Not the greatest solution...)
I don't quite grasp the concept of relational tables just yet. It seems that there would be a third table generated from the first two in some way, and then I'm still unsure of how to filter the data at that point.

Thanks for your time.

[pbaldy]

Presumably you only care about models a spec applies to. The third table would contain 3 fields: it's own autonumber primary key, a field for the spec ID and a field for the model ID, see below with your sample data. To find the specs that apply to a model, you simply use a parameter query where you input the model into a form.

SELECT Spec FROM NewTable WHERE Model = Forms!FormName.TextboxName

Auto	Spec	Model
1	1	1
2	1	2
3	1	3
4	2	2
5	2	3
6	3	1
7	4	1
8	4	2
9	4	3

[Micron]

My take on a possible design involves a table for each entity and a linking table:

tblSpecs
SpecID	Type	Version	Desc	Rev	RevDate
1	body	1		0
2	paint	1		0
3	paint	2		0
4	power	1		0
				.
tblModels
ModID	Model	Desc	DocRev	RevDate	DocID
1			0	3/15	D057
2			0	3/15	D038
3			1	3/15	D099

Using your chart, record which specifications apply to each model.

tblModelSpecs
msID	ModID_FK	SpecID_FK
1	1	1
2	1	3
3	1	4
4	2	1
5	2	2
6	2	4
7	3	1
8	3	2
9	3	4

You may need to review normalization before you can fully grasp this. While you're at it, I'd suggest other reading before you invest too much time in something that can be fraught with design issues. More or less, in order of importance:

One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp

And research multi value fields before you decide to use them. Us diehards would say DON'T but then we're more adept at getting the same results in a way that follows normalization rules. I'd call them a crutch, which sometimes, is something that a person needs. But like a crutch, better if we never need it.

[timwcpumps]

Well how about that. Thanks for the new direction!

Right now I have a query that's showing a list of SpecID numbers, so that looks great. How do I use this now to tell the report which specs to pull?