use a value list to pick a query field

[AJS]

Hi All,

How can I use a value list in a form to pick which field will be filtered in a query? FYI, I'm trying to create a form that can be used to generate quick reports.

Thanks, Aaron

[Trevor]

Hey Aaron, how it goin'
check this link out, I think it is exactly what your lookin' for (about 1/2 way down the page)
http://www.fontstuff.com/access/acctut11.htm
you can skip the coding processes he has at the bottom unless you intend it to be a multi select listbox

[AJS]

Hi Trevor,

Going quite well thanks, and thanks for the link. The first thing I figured out from the link you provided is that in order to use QueryDef (which I had seen references to in the access vba help file) I need to set a reference to the Microsoft DAO 3.6 Object library. Unfortunately when I set that reference my other (more important) form stops working - I get a run time error "You cancelled the previous operation". All's well when I remove the reference to the Microsoft DAO 3.6 Object library, which of course means I'm back where I started...

Cheers, Aaron

[Trevor]

ok, here's a crazy thaught(assuming you are only selecting 1 value from the list to input int your query , then in the cretria [NameOFField] in table = forms![formname]!listboxname]
ie

[Name] = forms![Form1]![Listbox]

or you can try passing all your listbox selections( assuming more then 1 to a textbox) using a delimiter ie , or ; (then you may need to deal with the delimer , or you could just have the query pick 1 selection at a time untill done.

[AJS]

Hmm... it looks like it should work, but it doesn't seem to. I've written the following expression under "field" in the query design view:

[Name]=[forms]![DateForm]![RepType]

where reptype is a combo box in my form, which lists all of the different date fields I might want to run a report on. When I select a field and try to generate a report I get the following input box (see attached jpg).

What am I doing wrong?

Cheers, Aaron



file:///C:/DOCUME%7E1/see029/LOCALS%7E1/Temp/moz-screenshot.jpg

[Trevor]

Try removeing your cryteria for the query and placing in your where claws
[Do this in SQL view]
( access likes to play stupid sometimes ), well alot of the time, but whose counting ) :beerchug:

[Name]=[forms]![DateForm]![RepType]