Access User Defined Queries.

[gk759]

Hi,

I need help with defining User defined queries where the user can simply use a combo box to select his criteria and generate his own report. I have 4 tables Inventory Receipt table, Vendor table, Parts Table and Status Table.

i have to design it in such a way that anyone can chose a criteria from any one of the tables e.g byvendor by status or by status by part. To generate a report.

So far I have been able to create parameter queries but if I could acheive the user defined method , it would be really helpful.

If anyone has a suggestion on how this can be accomplished please let me know, I would really appreciate it,

Thanks,

Gary

 

[WildHare]

This can be done a couple of ways. One way would be to save the choices in variables, and then use them to concatenate with other strings to make an SQL statement that you then Execute with the "Docmd.RUNSQL " method.

Given enough flexibility, you could build in WHERE clauses, HAVING clauses and ORDER BYs as well.

Another way would be to table-drive the process, using stored string fields that get retrieved and glued together at run time. I did one of these a few years ago for an ex-employer - you could select any of about 15 fields on a form and have it build a SQL SELECT statement then run a report against it.

It was a little harder with the report, since it needs to have static fields in it - if you just want to view a dumb old datasheet, it's a breeze.

Let me know if you want more details.

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[jfgambit]

I have a sample database that you can see if you are interested which rewrites the querydef based on what is intered into multiple textboxes. You can use this to feed your report. Let me know if you are interested and i will send you a copy.


"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[gk759]

If you could Mr.Gambit I would appreciate getting the sample database,

please email it to the address below,

Merci beaucoup!,

Gary
(gk759@yahoo.com)