Query different column based on textbox

[Montez659]

I am sure that this a topic that has been covered a hundred times, but I am having trouble getting the syntax correct. I watched the recommended video and got a good idea how to do what I want, but I am having trouble taking it all the way. The video was http://www.datapigtechnologies.com/flashfiles/searchform.html

Basically, I need to query a different column based on the value of a textbox. The textbox value is the name of the column that needs to be queried. I can't quite figure out how to do it. I tried to put the expression in the "field" portion of the query but it doesn't seem to like it. Basically, the query needs to run in the appropriate column based off of [forms]![welcome].[txtHiddenReportSelect]. Does this go in the field? Do I need to concatenate "Contacts" onto the hidden field and return the whole string as the "field"? Or is there a way to use the "criteria" field to do this?

Thanks in advance, and let me know if I am not clear enough.

 

[jackd]

Perhaps you should give a complete example of what you are considering/wanting.

The Datapig example dealt with a single table and offered
5 items that could be searched.
In fact there were 5 text boxes to allow an entry to be searched.
Each entry referred to searching a specific column/field.
You could fill in 0 to 5 criteria.

 

[Ziggy]

What you want to do is this.....

Like([forms]![welcome].[txtHiddenReportSelect]) & "*"

Put this in the criteria of the column it relates to, you can do this for multiple text boxes ( I prefer to use combo lookups).

Now the final trick is go to the properties of each control and set the Default value to be *

It's a wildcard character, it will allow you to use multiple criteria, or any one of them.

 

[Montez659]

I'll lay out the example for you. I have a combobox select from a value list which when changed updates a hidden textbox. The textbox value will change to exclAssign, exclTF, exclW9, exclSiteVisit. Based off of this textbox value, the query needs to search that particular column in the table Contacts. Each of those columns returns a Yes/No value.

My basic setup is that I have Contacts that need to be excluded from certain reports. The way the user interfaces with this is via two list boxes that are populate by two queries IncludedForReports and ExcludedForReports. The user can click on an individual name and that name will be moved back and forth between lists.

This setup worked fine when there was only one report to be excluded from, but we have now added three more reports. Instead of writing eight queries to deal with this, I figured there must be a way to use the two existing and change the column being searched to return the correct set of Yes/No values.

Hope this makes more sense now. Ziggy, I tried playing around with your code but it didn't seem to work. Thanks guys.

 

[Norie]

As far as I can see the only way you can do what you want is to use code.

Perhaps it would be easier to create multiple queries.

Surely all you would need to do for that is copy the existing query and change a couple of things.

 

[Montez659]

Thanks for the answer Norie. Then that is what I will do - just create the extra queries. It won't take much to do that like you said, but I was just wondering if there was a different way. Appreciate the help!

EDIT: Unless...I don't mind doing it in VBA, since I can copy the SQL string from the query itself and change what I need. But can I make it dump back into an existing query name, i.e., ExcludedForReports? If so, how do I do this?

 

[Norie]

Well you could actually do it without creating any queries.

That would involve setting the record source for the report with code which might be tricky.

It would also kind of make the reports useless on their own.

No record source - no data - no report.

 

[Montez659]

Well, its not actually for a report. It is for the two listboxes on a form. I'm beginning to think that there is an easier way to display the info that I need in those two boxes. I just used queries because that was how it played out in my mind. There is already some VBA associated with the listboxes - on double click it finds record and updates it, then requeries the Excluded and Included queries and displays the updated info in the listboxes.

When the report is running, it filters out the Yes/No field in the table, so I have no use for the two queries except to fill my two listboxes.

 

[Norie]

Oops, my bad - for some reason I thought it was for a report.

Same answer though - you could set the record source for the listboxes or whatever using code.

Though I'm starting to wondering if using the same listbox for what sounds like quite different record sources is a good idea.

If that's what you are doing of course, and I haven't got the wrong end of the stick again.

 

[Montez659]

That depends on what record source means. If it means table level, then the rs is the same, meaning all the data is within the same table. However if it is field level, then there are different fields for the four different types of reports to be generated.

I am going to try a bit with the extra queries to see if I can get that to work. That seems to be the easiest route at this point.