Combobox to filter report

[pbaldy]

No problemo.

[Sharkun]

I'll try and stop bothering, but I have a follow up.

I was hoping that I would be able to take what you fixed for me in the form search button and apply that to a different search form I'm doing.

Your line of code here that works great:
DoCmd.OpenReport stDocName, acPreview, , "Customer = '" & Me.Combo5.Column(1) & "'"

So I have another form that's using the same report but searching by a different field.

The combo box is named Combo0
Column 4 is the column in the query I'm searching by.
Problem ID is the name of the field in the report.

So I would think that this line would work.

DoCmd.OpenReport stDocName, acPreview, , "Problem ID = '" & Me.Combo0.Column(4) & "'"

But I get a syntax error (missing operator) in query expression '(Problem ID = ")'

Thanks again.

[pbaldy]

The error indicates that it isn't finding a value. Column number 4 is actually the 5th column in the combo, as the Column property is zero based. Perhaps you want 3? Also, if the ID is numeric, you don't want the surrounding single quotes, as noted in my link.

[Sharkun]

Tried different column numbers and still get syntax error. It is a text value, and tried different columns with no luck. Always a syntax error. What about the space in the column name. Should Problem ID have something to say that Problem & ID go together?

Made another search form that I was going to do. And I get a blank report, but no data. This one's difference is the name of the column is all together with no spaces.

DoCmd.OpenReport stDocName, acPreview, , "AssignedTo = '" & Me.Combo4.Column(7) & "'"

To be clear and without bugging you for little things, in this situation, I have my query and it has 8 columns showing. If I want to search by the 7th column in the query it the Column(x) line x would be 6?

[pbaldy]

Because of the inadvisable space, you need to bracket the field name. The 7th column in the query would referred to as 6, yes. In case you modified things after initially building the combo, make sure the column count property is correct (in your example, it would need to be 8).

[Sharkun]

Column Count Property?

I bracketed in the Problem ID text, but no syntax error, but no data on the report.

DoCmd.OpenReport stDocName, acPreview, , "[Problem ID] = '" & Me.Combo0.Column(3) & "'"

The Column I'm searching by in the query is in the 4th column so that would be Column(3)

What am i missing?

[pbaldy]

What is the Column Count property of the combo?

[Sharkun]

1 is the value. I have the problem IDs in another table

[pbaldy]

Like I said, you need to change 1 to the number of fields being returned by the query.

[Sharkun]

I've gotten confused again.

My combobox is linked to a table that has all my problem types. Not linked to the query. Here's examples of my table of problem types.

Hardware
Software
Reports
CC
and so on like that.

If i'm reading your last entries correct, in my combo-box the column count should be the number of columns that are in my query? Tried it, but still not working.

Sorry to be a pain.

[pbaldy]

Can you post the db? With the column count property, you're telling Access how many fields there are. If your column count property is 1, then Access won't recognize when you refer to a column higher than that (actually 1 less than that, due to the zero base).