Grid Pane Field Expressions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill - 10 Nov 2007 23:22 GMT

Ordinarily, I would put field expression code in the
OnFormat_Detail event section. However, if it's possible
to build the expression I want directly into a query
grid pane while in grid design, I would prefer to do
that in the current instance.

Current case: I have a field "Title" (happens to be
the title of a classical music piece.) and I also have
the cataloging system and values in separate fields,
CatSystem and CatSystemVal, e.g., "Op.", "78".
So, if the current piece has a chronological catalog
value, I want the query to return it appended to the
title of the piece.

Example:
Title = Symphony
CatSystem = Op.
CatSystemVal = 74

resulting expression from query: Symphony No. 6, Op.74

In VBA OnFormat_Detail, I would code something like
Dim strTitle as string

strTitleExp = Me.Title 'Where strTitleExp is what is to be displayed.
if len(CatSystemVal & "") > 0 then strTitleExp = strTitleExp & ", " &
CatSystem & CatSystemVal

Thanks,
Bill

Reply to this Message

Rob Parker - 10 Nov 2007 23:43 GMT

Hi Bill,

You can use a calculated expression in the query grid. This should work
fine:
TitleExp: iif(len(CatSystemVal & "") > 0,[Title] & ", " & [CatSystem] &
[CatSystemVal],[Title])

You can also enter this expression (preceded by "=") directly into a textbox
control on a form or report. If you do that, you must ensure that the name
of the textbox control itself is not the same as the name of any of the
fields in the form's/report's recordsource.

HTH,

Rob

> Ordinarily, I would put field expression code in the
> OnFormat_Detail event section. However, if it's possible
[quoted text clipped - 26 lines]
> Thanks,
> Bill

Reply to this Message

Bill - 11 Nov 2007 00:12 GMT

"If you do that, you must ensure that the name of the textbox
control itself is not the same as the name of any of the fields
in the form's/report's recordsource."

AMEN, I've been there before.

TitleExp: iif(len(CatSystemVal & "") > 0,[Title] & ", " & [CatSystem] &
[CatSystemVal],[Title])

worked great. I see in the results that some of the "Title" fields have some
extraneous blanks on the end of the field, so I modified your code to:

TitleExp: iif(len(CatSystemVal & "") > 0,Trim([Title]) & ", " & [CatSystem]
& [CatSystemVal],[Title])

Thanks,

Bill

> Hi Bill,
>
[quoted text clipped - 42 lines]
>> Thanks,
>> Bill

Reply to this Message