querie based on fields from table, please help

[livin'life]

I have a form with about 40 buttons on it, and each one opens a report based
on a query. What I am trying to do is code the buttons so that there is only
one query and one report (since the format is the same) that they pull from,
as opposed to 40 different reports and queries so I dont have so much to
update when something changes to a report format. The queries are based off
different fields in the table. (cmd1 opens rpt1 based on qry1, cmd2 opens
rpt2 based on qry2, etc.. qry1 is based on fld1 of tbl1, qry2 is based on
fld2 of tbl1, etc. .) I was thinking of something like onclick, the button
would run a query based on the field i tell it, then feed that data into the
report and every button would use the same query and report format. I dont
know if this is possible but any help would be appreciated. Thanks

 

[Allen Browne]

I take it that all these queries use the same tables, and return the same
fields, so the only difference is the criteria?

If so, you could do this with just one report and one query. You leave the
criteria out of the query; instead your form uses OpenReport with a
WhereCondition to filter it the way you want. You build the WhereCondition
string from whatever the user wants when they run the report.

Here's a couple of simple examples of opening a report with a
WhereCondition:
http://allenbrowne.com/casu-15.html
http://allenbrowne.com/casu-08.html

Here's an example of how to build the filter string from lots of boxes where
the user can enter their critieria:
http://allenbrowne.com/ser-62.html
That example applies the critiera to the form, but it's exactly the same to
apply it to a report.

 

[ryan.fitzpatrick3]

Allen,

I think from reading the Dlookup function on your web site that it
might be what I'm looking for. I have this situation for a query. I
download a list of information onto a table, and I'd like to make a
search on that item. I put in "*" & [Search Item] & "*" into the
item field with the wild cards just in case. This works fine. Now lets
say I'm looking up cars, so in the search input box that pops up when
I run the query I type in dodge (cars) and that brings backs dodge
matches. If I want to look up red dodges do I put LIKE "red" and "*" &
[Search Item] & "*" or "red" &"*" & [Search Item] & "*" ?

I've tried both and it works only if red dodge is next to each other
but if it's red white dodge, it wouldn't pick that item up. Is there
away to program another search word into the search field where it
would bring back all red dodges even though the words are not next to
each other?

Also can I type in two colors like "red" or "yellow" and "*" & [Search
Item] & "*" and lookup multiple colors? Thank you

Ryan

 

[Allen Browne]

I think you have posted this as a follow up to another thread
unintentionally?

Please post a new thread, without addressing to a particular person, and see
what answers you receive.

I'm not sure I understand the question. If you have "red" and "dodge" in the
same field, that doesn't sound right. Each field should contain only one
thing not several things, i.e. each one should be atomic.

 

[ryan.fitzpatrick3]

I figured it out, basically I was trying to bring up results with 2
words that I want to look for. this worked for me.

(like "*" & "item1" & "*" or like "*" & "item2" & "*") and (like "*" &
[Item Desc] & "*")

on a query field lets say for an item #, in the criteria can I put
like "123456" or "123457" etc for as many items as I want, either 1 or
100?

I think you have posted this as a follow up to another thread
unintentionally?

Please post a new thread, without addressing to a particular person, and see
what answers you receive.

I'm not sure I understand the question. If you have "red" and "dodge" in the
same field, that doesn't sound right. Each field should contain only one
thing not several things, i.e. each one should be atomic.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I think from reading the Dlookup function on your web site that it
might be what I'm looking for. I have this situation for a query. I
download a list of information onto a table, and I'd like to make a
search on that item. I put in "*" & [Search Item] & "*" into the
item field with the wild cards just in case. This works fine. Now lets
say I'm looking up cars, so in the search input box that pops up when
I run the query I type in dodge (cars) and that brings backs dodge
matches. If I want to look up red dodges do I put LIKE "red" and "*" &
[Search Item] & "*" or "red" &"*" & [Search Item] & "*" ?

I've tried both and it works only if red dodge is next to each other
but if it's red white dodge, it wouldn't pick that item up. Is there
away to program another search word into the search field where it
would bring back all red dodges even though the words are not next to
each other?

Also can I type in two colors like "red" or "yellow" and "*" & [Search
Item] & "*" and lookup multiple colors? Thank you

Ryan

 

[livin''''life]

Sorry for the long response time. My original question was poorly written so
I probably caused myself more confusion than I needed but I did end up
solving the dilema I had.

The queries all use the same table, but what I wanted to change was one of
the fields and the criteria involved. Not too sure if it was the best thing
to do, but I ended up coding each button's OnClick feature to:

1. Create a temp query using SQL with the needed fields.
2. Send the query to a report.
3. Open the report.
4. Delete the query.

Thanks for the help with the original answer, even though I wrote it so
poorly, lol. Cheers