Queries and functions/expressions and calculated fields

[Cheese_whiz]

Hiyas,

I often use select queries in the rowsource for combo boxes to display
people's names. That happens often in most of the apps I build as I'd think
would be fairly common.

Anyway, mostly I get the names in the combo box by typing an expression
within the select query based on the table where those records are stored.
For example:

Select EntityID, Trim([LastName] & ", " & [FirstName] & " " & [MiddleName])
from EntityTable Where ... Order by....

If I put that expression in a query, could I use it for ANY combo box in the
application for the rowsource? Do developers just make a seperate query for
something like this or just use an existing query even if it has tons of
fields as long as it has the ones you need for the expression?

I have the same issues/question about calculated fields. Often, I just type
code into vba or into the default value of a control on a form to display a
calculated field. Would it be better to just add an expression to a query
being used as the recordsource for the form where the calculated field will
be displayed, or even MAKE a query that would include just the table that's
currently used as the recordsource PLUS the expression needed for the
calculated field?

I'm just thinking I'm wasting time retyping some of this
stuff...particularly the combo box select queries where I think I could
substitute the name of the expression from a query for the expression
itself...

Does that make sense? Any feedback?

Thanks,
CW

 

[John Spencer]

My responses are in line. They are my way of doing things, but I think your
questions go mainly to style of coding and not necessarily to the "BEST"
method of doing something.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hiyas,

I often use select queries in the rowsource for combo boxes to display
people's names. That happens often in most of the apps I build as I'd
think
would be fairly common.

Anyway, mostly I get the names in the combo box by typing an expression
within the select query based on the table where those records are stored.
For example:

Select EntityID, Trim([LastName] & ", " & [FirstName] & " " &
[MiddleName])
from EntityTable Where ... Order by....

If I put that expression in a query, could I use it for ANY combo box in
the
application for the rowsource? Do developers just make a seperate query
for
something like this or just use an existing query even if it has tons of
fields as long as it has the ones you need for the expression?

'=======================================
IF you want to use the same exact query in multiple places there is no
problem. However it is a good idea to (1) return as little data as possible
and (2) make sure that the columns and column counts match up between the
query and the combobox. I normally enter the expression directly, but
sometimes I have a complex query that will not fit within the size limits
and then I use a stored query.

'=======================================

I have the same issues/question about calculated fields. Often, I just
type
code into vba or into the default value of a control on a form to display
a
calculated field. Would it be better to just add an expression to a query
being used as the recordsource for the form where the calculated field
will
be displayed, or even MAKE a query that would include just the table
that's
currently used as the recordsource PLUS the expression needed for the
calculated field?

'=======================================
I think this is a matter of style. But I prefer to calculate my fields in
the query unless I need the individual components in my report or on my
form. Again, returning as few columns as possible over the network.
'=======================================

I'm just thinking I'm wasting time retyping some of this
stuff...particularly the combo box select queries where I think I could
substitute the name of the expression from a query for the expression
itself...

'======================================
The advantage of having something in one place is that you can change it in
one place. The disadvantage is that you must be aware of every place you
are using a stored query - since a change in one place can affect the
behavior in many places.
'======================================

 

[Cheese_whiz]

Thanks for the reply, John,

You're probably right about the "stylistic" nature of the questions. Access
just seems to have enough different ways to accomplish things that sometimes
it's hard to get organized, and sometimes you start wondering if you are
'missing out' on something convenient just because once you figured out how
to do something you just keep doing it that way.

Plus, I still wouldn't confuse myself with a query expert. I'm sure there
are times when I could benefit from using a query but I don't, and other
times when I don't really need one and I use one anyway

Guess I'm just trying to fill in some holes in what I've learned!

Thanks again,
CW

My responses are in line. They are my way of doing things, but I think your
questions go mainly to style of coding and not necessarily to the "BEST"
method of doing something.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hiyas,

I often use select queries in the rowsource for combo boxes to display
people's names. That happens often in most of the apps I build as I'd
think
would be fairly common.

Anyway, mostly I get the names in the combo box by typing an expression
within the select query based on the table where those records are stored.
For example:

Select EntityID, Trim([LastName] & ", " & [FirstName] & " " &
[MiddleName])
from EntityTable Where ... Order by....

If I put that expression in a query, could I use it for ANY combo box in
the
application for the rowsource? Do developers just make a seperate query
for
something like this or just use an existing query even if it has tons of
fields as long as it has the ones you need for the expression?

'=======================================
IF you want to use the same exact query in multiple places there is no
problem. However it is a good idea to (1) return as little data as possible
and (2) make sure that the columns and column counts match up between the
query and the combobox. I normally enter the expression directly, but
sometimes I have a complex query that will not fit within the size limits
and then I use a stored query.

'=======================================

I have the same issues/question about calculated fields. Often, I just
type
code into vba or into the default value of a control on a form to display
a
calculated field. Would it be better to just add an expression to a query
being used as the recordsource for the form where the calculated field
will
be displayed, or even MAKE a query that would include just the table
that's
currently used as the recordsource PLUS the expression needed for the
calculated field?

'=======================================
I think this is a matter of style. But I prefer to calculate my fields in
the query unless I need the individual components in my report or on my
form. Again, returning as few columns as possible over the network.
'=======================================

I'm just thinking I'm wasting time retyping some of this
stuff...particularly the combo box select queries where I think I could
substitute the name of the expression from a query for the expression
itself...

'======================================
The advantage of having something in one place is that you can change it in
one place. The disadvantage is that you must be aware of every place you
are using a stored query - since a change in one place can affect the
behavior in many places.
'======================================

Does that make sense? Any feedback?

Thanks,
CW