If's in a Report TextBox by using a function

[H. Martins]

In a report I need a TextBox to be filled depending on a couple 'IFs'

In code area of the report I've done this:

Static Function fnIdAluno() As String

fnIdAluno = "Certifica-se que " & [strNome] .... etc, etc, etc.

End Function

This code works OK if placed in the TextBox.ControlSource itself but,
of course, for a single case only.

First I want to be able to get it running for a single case and then I
will add some IFs.

Then I tried to use the function in the TextBox as ...

= fnIdAluno

Didn't work.

I tried inserting an Exp: in the query, I didn't succeed.

Am I doing something fundamentally wrong?

Thanks
Henry

 

[Marshall Barton]

In a report I need a TextBox to be filled depending on a couple 'IFs'

In code area of the report I've done this:

Static Function fnIdAluno() As String

fnIdAluno = "Certifica-se que " & [strNome] .... etc, etc, etc.

End Function

This code works OK if placed in the TextBox.ControlSource itself but,
of course, for a single case only.

First I want to be able to get it running for a single case and then I
will add some IFs.

Then I tried to use the function in the TextBox as ...

= fnIdAluno

Didn't work.

I tried inserting an Exp: in the query, I didn't succeed.

Am I doing something fundamentally wrong?

Unlike VBA, text box and query expressions require the ( )

In a query, if you use a public function that returns
different values, then you must use a query field somewhere
in the function's argument(s). This is because the query
optimizer figures that a function with no or constant
arguments will always return the same result and therefore
only needs to be called once.

If you want a function in a text box expression to return
different values, it should also base the If statements on
the function's argument(s).

 

[Henry]

Marsh,

I can't understand it all.

Looks that in a report, invoking query fields inside a function is not
enough.

I called the function like this:
=fnIdAluno([strDescCertificados])

The function is:
Static Function fnIdAluno(strTipoDoc As String) As String

fnIdAluno = strTipoDoc

End Function

BTW, I use a static function just by inertia. Not sure which type must
I use.

The above function returns the String to the TextBox, but any other
query field produces #error. Looks like the function needs all query
fields as arguments. Is it right? Can I call the function passing the
query as argument?

Thanks for help,
Henry

 

[Marshall Barton]

Looks that in a report, invoking query fields inside a function is not
enough.

I called the function like this:
=fnIdAluno([strDescCertificados])

The function is:
Static Function fnIdAluno(strTipoDoc As String) As String

fnIdAluno = strTipoDoc

End Function

BTW, I use a static function just by inertia. Not sure which type must
I use.

The above function returns the String to the TextBox, but any other
query field produces #error. Looks like the function needs all query
fields as arguments. Is it right? Can I call the function passing the
query as argument?

OK, it works when the text box uses an expression like:
=fnIdAluno(somefield)

Now, what do you mean by "any other query field produces
#error"? I would expect it to return #Error if the query
field has a Null value. Access may be able to automatically
convert a number or date field to a string, but I think it
is better to code it for the appropriate data type of the
field. Note that the only way for the function to deal with
a Null field value is to declare the function:

Function fnIdAluno(x As Variant) As Variant
fnIdAluno = x
End Function

(BTW Static is used to keep the values of a procedure's
local variables from one call to another and is only used in
special situations.)

With that, I would expect you to be able to call that
function from two (or more text boxes using expressions
like:
=fnIdAluno([this field])
=fnIdAluno([that field])
=fnIdAluno([otherfield])

OTOH, you have simplified the function to such a trivial
scenario that I have no idea what your comment about using
If statements will do to it. Maybe you should explain what
you are really trying to accomplish.

 

[Henry]

Marsh,

"OTOH, you have simplified the function to such a trivial
scenario that I have no idea what your comment about using
If statements will do to it. Maybe you should explain what
you are really trying to accomplish. "

You're right. The scenario I had in mind changed after your first
answer.

I am in Portugal and Portuguese is quite a tricky language.

I have to make a report that produces a sort of legal text using
database fields and some other words.

This is the code I have running now inside a text box:

="Terminou ... dia " & Format([dateDataFim];"d \d\e mmmm \d\e yyyy") &
" a acção ... Curso " & [memoDescricaoCurso] & " que ... ocorreu a " &
Format([dateDataInicio];"d \d\e mmmm \d\e yyyy") & "." & Chr$(13) & Chr
$(10) & Chr$(13) & Chr$(10) & " Do processo ... acção." & Chr$(13) &
Chr$(10) & Chr$(13) & Chr$(10) & "Enviamos ... necessários."

(I have replaced some text by "..." to make it shorter.)

There are similar situations in other text boxes.

There was recently introduced what is called the Citizen's Card that
has less personal data but the law still forces the use of the data
contained in old cards. That means, depending on the type of card, I
must produce different sentences and the Portuguese language rules
make it even more complex.

So I must pass the control of this (and other) text boxes to some
functions in order to produce the appropriate and finalized strings to
be shown in report.

That's where the data availability problem in the report VBA showed
up.

As much as I guess, I will have to pass the content of all data fields
needed for each function. I was expecting that current query would be
available within the all report matter (report itself an it's VBA).

Henry

 

[Marshall Barton]

"OTOH, you have simplified the function to such a trivial
scenario that I have no idea what your comment about using
If statements will do to it. Maybe you should explain what
you are really trying to accomplish. "

You're right. The scenario I had in mind changed after your first
answer.

I am in Portugal and Portuguese is quite a tricky language.

I have to make a report that produces a sort of legal text using
database fields and some other words.

This is the code I have running now inside a text box:

="Terminou ... dia " & Format([dateDataFim];"d \d\e mmmm \d\e yyyy") &
" a acção ... Curso " & [memoDescricaoCurso] & " que ... ocorreu a " &
Format([dateDataInicio];"d \d\e mmmm \d\e yyyy") & "." & Chr$(13) & Chr
$(10) & Chr$(13) & Chr$(10) & " Do processo ... acção." & Chr$(13) &
Chr$(10) & Chr$(13) & Chr$(10) & "Enviamos ... necessários."

(I have replaced some text by "..." to make it shorter.)

There are similar situations in other text boxes.

There was recently introduced what is called the Citizen's Card that
has less personal data but the law still forces the use of the data
contained in old cards. That means, depending on the type of card, I
must produce different sentences and the Portuguese language rules
make it even more complex.

So I must pass the control of this (and other) text boxes to some
functions in order to produce the appropriate and finalized strings to
be shown in report.

That's where the data availability problem in the report VBA showed
up.

As much as I guess, I will have to pass the content of all data fields
needed for each function. I was expecting that current query would be
available within the all report matter (report itself an it's VBA).

I can only see three arguments on your example above,
dateDataFim, memoDescricaoCurso and dateDataInicio. That
should make at least this one function fairly
straightforward.

This particular example can be done another way if the text
surrounding the arguments is stored in another table with
two fields: tid and bptext. The bptext field would be in
the style:
---------------
Terminou ... dia " {date1} a acção ... Curso {descr} que ...
ocorreu a " {date2}.

Do processo ... acção.

Enviamos ... necessários.
---------------
then you could imbed the argument values by using code like:

Function fnIdAluno(tid As String, _
datefim As Date, _
dateInico As Date, _
qtmemo As String)
fnIdAluno=DLookup("bptext","texttable","textID=" & tid)
fnIdAluno=Replace(strA,"{date1}",Format(datefim,"..."))
fnIdAluno=Replace(strA,"{descr}",qtmemo)
fnIdAluno=Replace(strA,"{date2}",Format(dateInico,"..."))
End Function

 

[Henry]

Marsh,

I can't see why you see only three arguments.

Several DLookup's also need one argument each. I have to use DLookup
because both tables 'people' and 'place_of_birth' have fields locked
to the 'places' table (and some more DLookups are needed to catch some
wording that vary depending, for example, on places being something
like ... male or female ... that's it!). I would like to avoid these
DLookups but I couldn't find a way to reference the 'place' relative
to (address of) 'people' and 'place' relative to 'place_of_birth'.
There should be something like 'people->places' and 'place_of_birts-

places'.

Anyway, I struggled and got the following code running, still without
the "if's", but that should not be a problem.

The code is, in TextBox:

=fnIdAluno([strNome];[lintNaturConcKSlave];[dateDataNascimento];
[strNacionalidadeB];[strDescCertificados];[strDI];[dateBI-Data-TX];
[strDescEmissao];[lintBiLocalTXKSlave])

In function:

Function fnIdAluno(Nome As String, NaturConcKSlave As Long,
DataNascimento As Date, NacionalidadeB As String, DescCertificados As
String, DI As String, BIDataTX As Date, DescEmissao As String,
BiLocalTXKSlave As Long) As String

fnIdAluno = "Certifica-se que " & Nome & ", natural " & DLookup
("[strD]", "tblLocais", "[lintLocaisKMaster] = " & NaturConcKSlave) &
" " & DLookup("[strLocal]", "tblLocais", "[lintLocaisKMaster] = " &
NaturConcKSlave) & ", nascido a " & Format(DataNascimento, "d\/m\/
yyyy") & ", de nacionalidade " & NacionalidadeB & ", portador " &
DescCertificados & " nº " & DI & " emitido a " & Format(BIDataTX, "d\/m
\/yyyy") & " " & DescEmissao & " " & DLookup("[strD]", "tblLocais",
"[lintLocaisKMaster] = " & BiLocalTXKSlave) & " " & DLookup
("[strLocal]", "tblLocais", "[lintLocaisKMaster] = " &
BiLocalTXKSlave) & ", concluiu o Curso de Formação Profissional de"

End Function

It is a little bit mesy but it works OK.

Still I am surprised that we can't just pass the query as the argument
to the function.

Thanks
Henry

 

[Henry]

Marsh,

I apologize but I found out that I pasted the wrong peace of code in a
previous post.

That's the result of trying to sort out some problem while some other
is holding.

Still, the problem is the same, just not so simple. That's why you
just saw three arguments.

Anyway, my feeling as all this matter (code, code, code) seems not
much "elegant" remains.

Thanks
Henry

 

[Marshall Barton]

I can't see why you see only three arguments.

Your example only had 3 things that are inserted into the
text. I see now that the example was simplified to the
point of being misleading.

Several DLookup's also need one argument each. I have to use DLookup
because both tables 'people' and 'place_of_birth' have fields locked
to the 'places' table (and some more DLookups are needed to catch some
wording that vary depending, for example, on places being something
like ... male or female ... that's it!). I would like to avoid these
DLookups but I couldn't find a way to reference the 'place' relative
to (address of) 'people' and 'place' relative to 'place_of_birth'.
There should be something like 'people->places' and 'place_of_birts-

places'.

Anyway, I struggled and got the following code running, still without
the "if's", but that should not be a problem.

The code is, in TextBox:

=fnIdAluno([strNome];[lintNaturConcKSlave];[dateDataNascimento];
[strNacionalidadeB];[strDescCertificados];[strDI];[dateBI-Data-TX];
[strDescEmissao];[lintBiLocalTXKSlave])

In function:

Function fnIdAluno(Nome As String, NaturConcKSlave As Long,
DataNascimento As Date, NacionalidadeB As String, DescCertificados As
String, DI As String, BIDataTX As Date, DescEmissao As String,
BiLocalTXKSlave As Long) As String

fnIdAluno = "Certifica-se que " & Nome & ", natural " & DLookup
("[strD]", "tblLocais", "[lintLocaisKMaster] = " & NaturConcKSlave) &
" " & DLookup("[strLocal]", "tblLocais", "[lintLocaisKMaster] = " &
NaturConcKSlave) & ", nascido a " & Format(DataNascimento, "d\/m\/
yyyy") & ", de nacionalidade " & NacionalidadeB & ", portador " &
DescCertificados & " nº " & DI & " emitido a " & Format(BIDataTX, "d\/m
\/yyyy") & " " & DescEmissao & " " & DLookup("[strD]", "tblLocais",
"[lintLocaisKMaster] = " & BiLocalTXKSlave) & " " & DLookup
("[strLocal]", "tblLocais", "[lintLocaisKMaster] = " &
BiLocalTXKSlave) & ", concluiu o Curso de Formação Profissional de"

End Function

It is a little bit mesy but it works OK.

Right on both counts, but I am concerned that it may become
even messier when you add in the If conditions and whatever
it takes to get to a final version.

Still I am surprised that we can't just pass the query as the argument
to the function.

Unfortunately, unlike forms, report's do not make their
Recordset property available. You could open a recordset to
get to tblLocais instead of using a bunch of Dlookups. This
would be more efficient, but may(?) be more or less messy
depending on your code and your definition of messy ;-)

Regardless, it sure looks like it will be tedious. I would
probably break up the function's humongous concatenation
expression into several shorter statements with all the
DLookups (or recordset code) in separate statements at the
top of the function. Remember that readability becomes more
and more important as time passes.