Customizing query fields based on form input

[Rus925]

I'm using a parameter query that derives the parameters from controls on an
unbound form. Now, I want to put controls on the form that let the user
choose exactly which fields to show and by which field to sort the dataset.
I tried using Excel-style IF statements, but I got an "Undefined function"
error. I tried it again with IIF, and it just didn't work; it gave me one
field that wasn't even in the table (something to the tune of "Expr1010".
Any ideas?

 

[Michel Walsh]

Jet supplies an alias to your computed expression when you don't give one.


You can give an alias, in the query designer grid, with the syntax:

AliasYouWant: expression


such as:


Tax: 0.05 * amount


to compute a tax of 5% over an amount, and calling the result, tax.



Vanderghast, Access MVP

 

[Rus925]

Thanks for your response; that's good to know, but what I'm really looking
for is how to change the visibility of fields in the query, not their names.

 

[Michel Walsh]

In a form? have a control for each field (controls are on form, fields are
in table/query) and turn them visible/invisible as required. You may have to
change the left property of the control to recuperate the horizontal space
left by a control turned invisible.


Vanderghast, Access MVP

 

[Rus925]

The form is unbound; it serves only as a nicer UI for entering the parameters
than having an individual dialog box pop up for each one
(http://www.fontstuff.com/access/acctut08.htm). What I want to do is have
some sort of checkbox in the unbound form that would toggle whether or not a
specific field is included in the query. I don't want users to have to go
into design view to customize the query if at all possible. Could such a
thing be written in to the SQL for the query? Could it be done with macro?
I don't understand what you're telling me to do, but I think what you're
saying has to do with hiding controls/fields (correct me if I'm wrong; that's
what I'm confused about) on the form, but I was talking about the fields in
the query's datasheet.

 

[Michel Walsh]

You cannot 'select the fields' to be displayed from a query short of writing
the query dynamically, with a string:

Dim str AS string
str= " SELECT "

if check1 then str=str & " field1,"
if check2 then str = str & " field2,"
....

' remove the extra coma ( I assume at least ONE field will be selected)
str=Left(str, len(str)-1)

'complete the statement
str= str & " FROM tableName ... "

'use the string a record source (form/report) or row source (combo box,
list box)



Indeed, I was proposing to hide controls in a form, which should be the way
end users see the data (rather than seeing it through the Access User
Interface, such as through a table or a query).



Vanderghast, Access MVP

 

[Rus925]

Thanks! I've made the edits and everything to work with my DB. Now all I
need to know is where I'm supposed to put this.

 

[Rus925]

....and by "this", I mean where I put the code in the DB.