Grid Pane Field Expressions

[Bill]

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

 

[Rob Parker]

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

 

[Bill]

"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,

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
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