Customizing query fields based on form input

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?

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" wrote in message
...
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?

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" wrote:

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" wrote in message
...
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?

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" wrote in message
...
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" wrote:

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" wrote in message
...
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?

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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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?

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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?

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.

"Michel Walsh" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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?

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

"Michel Walsh" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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?