Text strings in function

[BruceM]

I have a module containing a public function named FaxText that evaluates
vendor data regarding CertificateType, ExpirationDate, UpdateRequested, and
so forth. If a certificate's expiration date is coming due, the code
generates an integer (intStatus) of 1. If an update has been requested and
the expiration date has passed, intStatus is 2. There are maybe ten
possible intStatus numbers.

A Select Case statement assigns text:

Select Case intStatus
Case 1
FaxText = strDueSoon
Case 2
FaxText = strOverdue
End Select

The text strings may be several lines long (on the report that is intended
as a fax to be sent to the vendor), along the lines of:
strDueSoon = "Your " & [CertificateType] & " will expire on " &
[ExpirationDate] & ". Please send an updated blah, blah."

On the report (fax) I have a text box that has the FaxText function as its
control source. It works as intended.

Right now I have the text strings (strDueSoon, etc.) in the function itself.
They will add up to maybe 3000 characters, including spaces.

Is there a better choice than storing the text strings in the function?
Even if this amount of text is reasonable in a function, there may come a
project in which I will need a considerably larger amount.

 

[fredg]

I have a module containing a public function named FaxText that evaluates
vendor data regarding CertificateType, ExpirationDate, UpdateRequested, and
so forth. If a certificate's expiration date is coming due, the code
generates an integer (intStatus) of 1. If an update has been requested and
the expiration date has passed, intStatus is 2. There are maybe ten
possible intStatus numbers.

A Select Case statement assigns text:

Select Case intStatus
Case 1
FaxText = strDueSoon
Case 2
FaxText = strOverdue
End Select

The text strings may be several lines long (on the report that is intended
as a fax to be sent to the vendor), along the lines of:
strDueSoon = "Your " & [CertificateType] & " will expire on " &
[ExpirationDate] & ". Please send an updated blah, blah."

On the report (fax) I have a text box that has the FaxText function as its
control source. It works as intended.

Right now I have the text strings (strDueSoon, etc.) in the function itself.
They will add up to maybe 3000 characters, including spaces.

Is there a better choice than storing the text strings in the function?
Even if this amount of text is reasonable in a function, there may come a
project in which I will need a considerably larger amount.

Why not create a lookup table.
Text changes will be easy, and additional text items can be added
without modifying the code; just add additional records.

FieldNumber Number datatype/ Integer Indexed/No Duplicates
FieldText Text datatype
Table name is 'tblLookUpText'

Then in your report:
="Your " & DLookUp("[FieldText]","tblLookUpText","[FieldNumber] = " &
[IntStatus]) & " will expire on " & "blah blah etc."

No module function needed.

 

[BruceM]

I have a module containing a public function named FaxText that evaluates
vendor data regarding CertificateType, ExpirationDate, UpdateRequested,
and
so forth. If a certificate's expiration date is coming due, the code
generates an integer (intStatus) of 1. If an update has been requested
and
the expiration date has passed, intStatus is 2. There are maybe ten
possible intStatus numbers.

A Select Case statement assigns text:

Select Case intStatus
Case 1
FaxText = strDueSoon
Case 2
FaxText = strOverdue
End Select

The text strings may be several lines long (on the report that is
intended
as a fax to be sent to the vendor), along the lines of:
strDueSoon = "Your " & [CertificateType] & " will expire on " &
[ExpirationDate] & ". Please send an updated blah, blah."

On the report (fax) I have a text box that has the FaxText function as
its
control source. It works as intended.

Right now I have the text strings (strDueSoon, etc.) in the function
itself.
They will add up to maybe 3000 characters, including spaces.

Is there a better choice than storing the text strings in the function?
Even if this amount of text is reasonable in a function, there may come a
project in which I will need a considerably larger amount.

Why not create a lookup table.
Text changes will be easy, and additional text items can be added
without modifying the code; just add additional records.

FieldNumber Number datatype/ Integer Indexed/No Duplicates
FieldText Text datatype
Table name is 'tblLookUpText'

Then in your report:
="Your " & DLookUp("[FieldText]","tblLookUpText","[FieldNumber] = " &
[IntStatus]) & " will expire on " & "blah blah etc."

No module function needed.

I see that I was unclear about some of the details when I posted my original
question.

If a vendor's certificate has expired, and an update has been requested, the
text string will be something like:
= "Your " & [CertificateType] & " expired on " & [ExpirationDate] & _
". I am sending this again in case you overlooked an earlier request
blah, blah"

There are a number of other variants, depending on the details. The idea is
that a customized request for the information is more likely to be noticed
than a generic form letter kind of thing.

Also, there are some text strings that appear within other strings
(boilerplate text about it being important that we have the information,
etc.)

In light of this new information, can the lookup table approach still work?

 

[Robert Morley]

You can still go with the table idea, but then include keywords in the text
that will be replaced by your function, so maybe something like:

"Your ~CertificateType~ will expire on ~ExpirationDate~. Please send an
updated blah, blah."

Then your function would have something to the effect of

strStatusText = Replace(strStatusText, "~CertificateType~",
[CertificateType])
strStatusText = Replace(strStatusText, "~ExpirationDate~",
[ExpirationDate])

etc.

(Or you can get more complex and use Split & Rejoin...depends how much
parsing you expect to have to do.)



Rob

 

[BruceM]

Thanks to both for the replies.
I need ten or so unique text strings. There are some elements in common
between the strings, but they are quite different each from the other. I am
not having a problem inserting the fields into the text strings. That part
is fine. In fact, the function works as expected. My question is whether
there is a better place for the text strings than in the function itself.
For instance, maybe there could be a one-record table, with Text fields (or
Memo fields, if needed) containing different text strings. I could include
that table in the report's Record Source query, and insert table fields as
needed into the strings in the function. Or something like that.
I see how your suggestion and fredg's suggestions would work if a standard
block of text was used in all cases, but I don't see how it would help given
that the text strings vary so widely. For instance, if the CertificateType
is a Survey, those are handled differently than other types of certificates.
Also, if the certificate expires at this time next year, this year we need
to obtain confirmation that the company is still compliant. If the
certificate does not contain an expiration date, that is yet another
situation.
Surveys are handled differently enough that it may be best to handle those
with a separate report, but even so, there are a lot of variants. Am I
compromising the efficiency of the code by storing the text strings in the
function?

 

[Robert Morley]

What I would see is a table where you would have an ID field corresponding
to your status, and then the message that goes with it, with keywords where
necessary. Then within the code of your function, you would look up the
appropriate field (or you may want to have it as a JOIN in your record
source) and substitute in the correct text.

If that doesn't make sense because the strings vary too widely, then the
other possibility is to put your fixed text into a table, then look up the
strings you need and add them to your code (i.e., same as above, but instead
of basing the table on the status, you would have it based on keywords or
whatever makes sense, and construct the string dynamically for each
status...pretty much like you are, but with the strings coming from the
table).

In terms of efficiency, the fastest method is probably to store the strings
directly in the function, since that will avoid table lookups of any kind.
From the perspective of future changes, however, that may or may not turn
out to be the easiest method to maintain; it will vary from one case to
another. Table lookups should be sufficiently fast if you're not doing too
many records at once...say less than a few hundred.

Also, you may find it useful to store the fixed-text portions of the strings
in constants, either within the function, or outside of it if they'll be
used elsewhere. I doubt it'll make much, if any, speed difference, but it
may make it more maintainable.

Finally, as an advanced topic, since you asked about efficiency of
strings...if you're doing tens of thousands of records at once, you'll need
to worry about the method you're using to combine strings: each time you
append one string to another, VB allocates space for the combined string and
each sub-string, and depending how it's written, may de-allocate space for
previous instances of the various strings. There's a much faster method of
combining strings using fixed-length strings and Mid$, but 99% of the time,
the overhead in programming isn't worth the speed trade-off. If you're
curious, ask and I'll either explain it or send you a link about the topic
(if I can find it).



Rob

Thanks to both for the replies.
I need ten or so unique text strings. There are some elements in common
between the strings, but they are quite different each from the other. I
am not having a problem inserting the fields into the text strings. That
part is fine. In fact, the function works as expected. My question is
whether there is a better place for the text strings than in the function
itself. For instance, maybe there could be a one-record table, with Text
fields (or Memo fields, if needed) containing different text strings. I
could include that table in the report's Record Source query, and insert
table fields as needed into the strings in the function. Or something
like that.
I see how your suggestion and fredg's suggestions would work if a standard
block of text was used in all cases, but I don't see how it would help
given that the text strings vary so widely. For instance, if the
CertificateType is a Survey, those are handled differently than other
types of certificates. Also, if the certificate expires at this time next
year, this year we need to obtain confirmation that the company is still
compliant. If the certificate does not contain an expiration date, that
is yet another situation.
Surveys are handled differently enough that it may be best to handle those
with a separate report, but even so, there are a lot of variants. Am I
compromising the efficiency of the code by storing the text strings in the
function?

You can still go with the table idea, but then include keywords in the
text that will be replaced by your function, so maybe something like:

"Your ~CertificateType~ will expire on ~ExpirationDate~. Please send
an updated blah, blah."

Then your function would have something to the effect of

strStatusText = Replace(strStatusText, "~CertificateType~",
[CertificateType])
strStatusText = Replace(strStatusText, "~ExpirationDate~",
[ExpirationDate])

etc.

(Or you can get more complex and use Split & Rejoin...depends how much
parsing you expect to have to do.)



Rob

 

[Marshall Barton]

I would go with a table that has a field for the certificate
type, another for the paragraph (or whatever parts you can
divide things into), and a third for the text.

The primary consideration is to make the text editable in a
form for the Manager of the Letters without you having to
modify the function's code.
--
Marsh
MVP [MS Access]

I need ten or so unique text strings. There are some elements in common
between the strings, but they are quite different each from the other. I am
not having a problem inserting the fields into the text strings. That part
is fine. In fact, the function works as expected. My question is whether
there is a better place for the text strings than in the function itself.
For instance, maybe there could be a one-record table, with Text fields (or
Memo fields, if needed) containing different text strings. I could include
that table in the report's Record Source query, and insert table fields as
needed into the strings in the function. Or something like that.
I see how your suggestion and fredg's suggestions would work if a standard
block of text was used in all cases, but I don't see how it would help given
that the text strings vary so widely. For instance, if the CertificateType
is a Survey, those are handled differently than other types of certificates.
Also, if the certificate expires at this time next year, this year we need
to obtain confirmation that the company is still compliant. If the
certificate does not contain an expiration date, that is yet another
situation.
Surveys are handled differently enough that it may be best to handle those
with a separate report, but even so, there are a lot of variants. Am I
compromising the efficiency of the code by storing the text strings in the
function?

You can still go with the table idea, but then include keywords in the
text that will be replaced by your function, so maybe something like:

"Your ~CertificateType~ will expire on ~ExpirationDate~. Please send
an updated blah, blah."

Then your function would have something to the effect of

strStatusText = Replace(strStatusText, "~CertificateType~",
[CertificateType])
strStatusText = Replace(strStatusText, "~ExpirationDate~",
[ExpirationDate])

etc.

(Or you can get more complex and use Split & Rejoin...depends how much
parsing you expect to have to do.)

 

[BruceM]

There are about 500 records in the master table. In the subset of Approved
Vendors from which I am working are about 140 records. The number is
unlikely to change much. When I run the query (by way of the report) to
find out how many vendor certificates need attention it is unlikely there
will be more than about 20 or 30 records during any given month.
What you say about not having to do table lookups makes sense. For my
purposes it is easy to maintain the text string right there in the function,
but I like the idea of constants, especially if it ever gets to where I am
using the text strings from several different locations. I'm a little
unclear on how to create a user-defined constant. If the topic appears in
Help I cannot find the topic. From what I can tell through searching and
experimentation I can define a Private constant in the form's code module:

Private Const DueSoon As String = " is coming due"

But I am unclear whether that is the whole thing. Also, how would I refer
to it in my code? Would it be:

strDueSoon = "Your " & [CertificateType] & DueSoon

Also, it seems I can define a Public constant in a free-standing code
module, and use it in the same way.

Further, it seems that if I wish to use a constant as a criteria in a query
I need to create a function that returns the value of the constant, and use
the function as the criteria. I'm a little unclear on this, but I don't see
a need for it anytime soon.

Thanks for taking the time to reply, and for pointing me toward constants.
It wasn't what I set out to learn, but it will no doubt prove useful.

Thanks for offering to explain or send a link about "combining strings using
fixed-length strings and Mid$", but while it may be of some interest I doubt
I will be putting it to use anytime soon.

What I would see is a table where you would have an ID field corresponding
to your status, and then the message that goes with it, with keywords
where necessary. Then within the code of your function, you would look up
the appropriate field (or you may want to have it as a JOIN in your record
source) and substitute in the correct text.

If that doesn't make sense because the strings vary too widely, then the
other possibility is to put your fixed text into a table, then look up the
strings you need and add them to your code (i.e., same as above, but
instead of basing the table on the status, you would have it based on
keywords or whatever makes sense, and construct the string dynamically for
each status...pretty much like you are, but with the strings coming from
the table).

In terms of efficiency, the fastest method is probably to store the
strings directly in the function, since that will avoid table lookups of
any kind. From the perspective of future changes, however, that may or may
not turn out to be the easiest method to maintain; it will vary from one
case to another. Table lookups should be sufficiently fast if you're not
doing too many records at once...say less than a few hundred.

Also, you may find it useful to store the fixed-text portions of the
strings in constants, either within the function, or outside of it if
they'll be used elsewhere. I doubt it'll make much, if any, speed
difference, but it may make it more maintainable.

Finally, as an advanced topic, since you asked about efficiency of
strings...if you're doing tens of thousands of records at once, you'll
need to worry about the method you're using to combine strings: each time
you append one string to another, VB allocates space for the combined
string and each sub-string, and depending how it's written, may
de-allocate space for previous instances of the various strings. There's
a much faster method of combining strings using fixed-length strings and
Mid$, but 99% of the time, the overhead in programming isn't worth the
speed trade-off. If you're curious, ask and I'll either explain it or
send you a link about the topic (if I can find it).



Rob

Thanks to both for the replies.
I need ten or so unique text strings. There are some elements in common
between the strings, but they are quite different each from the other. I
am not having a problem inserting the fields into the text strings. That
part is fine. In fact, the function works as expected. My question is
whether there is a better place for the text strings than in the function
itself. For instance, maybe there could be a one-record table, with Text
fields (or Memo fields, if needed) containing different text strings. I
could include that table in the report's Record Source query, and insert
table fields as needed into the strings in the function. Or something
like that.
I see how your suggestion and fredg's suggestions would work if a
standard block of text was used in all cases, but I don't see how it
would help given that the text strings vary so widely. For instance, if
the CertificateType is a Survey, those are handled differently than other
types of certificates. Also, if the certificate expires at this time next
year, this year we need to obtain confirmation that the company is still
compliant. If the certificate does not contain an expiration date, that
is yet another situation.
Surveys are handled differently enough that it may be best to handle
those with a separate report, but even so, there are a lot of variants.
Am I compromising the efficiency of the code by storing the text strings
in the function?

You can still go with the table idea, but then include keywords in the
text that will be replaced by your function, so maybe something like:

"Your ~CertificateType~ will expire on ~ExpirationDate~. Please send
an updated blah, blah."

Then your function would have something to the effect of

strStatusText = Replace(strStatusText, "~CertificateType~",
[CertificateType])
strStatusText = Replace(strStatusText, "~ExpirationDate~",
[ExpirationDate])

etc.

(Or you can get more complex and use Split & Rejoin...depends how much
parsing you expect to have to do.)



Rob

 

[BruceM]

Thanks for turning your attention to my question. I already have a table
with a field for the certificate type, along with expiration date and
whether I have requested an update (I don't want to generate a second notice
if I never sent the first one). For that certificate I may send a request
for an update, a second notice, and a third notice. Also, I may send a
request for confirmation (a year before the expiration date), a second
request for confirmation, and a third request.
I think I can almost see a way to make this work, but I will need a
half-dozen or more snippets of text for each condtion (certificate due,
certificate late, etc.), interspersed with fields. The form to make the
text editable (so that an average person without programming knowledge could
revise the text that is to appear on the fax report) seems to me a
worthwhile but rather complex thing. Of course, it may just be that I don't
undertand what you're saying, and that it's not as complex as I imagine it
to be.

I would go with a table that has a field for the certificate
type, another for the paragraph (or whatever parts you can
divide things into), and a third for the text.

The primary consideration is to make the text editable in a
form for the Manager of the Letters without you having to
modify the function's code.
--
Marsh
MVP [MS Access]

I need ten or so unique text strings. There are some elements in common
between the strings, but they are quite different each from the other. I
am
not having a problem inserting the fields into the text strings. That
part
is fine. In fact, the function works as expected. My question is whether
there is a better place for the text strings than in the function itself.
For instance, maybe there could be a one-record table, with Text fields
(or
Memo fields, if needed) containing different text strings. I could
include
that table in the report's Record Source query, and insert table fields as
needed into the strings in the function. Or something like that.
I see how your suggestion and fredg's suggestions would work if a standard
block of text was used in all cases, but I don't see how it would help
given
that the text strings vary so widely. For instance, if the
CertificateType
is a Survey, those are handled differently than other types of
certificates.
Also, if the certificate expires at this time next year, this year we need
to obtain confirmation that the company is still compliant. If the
certificate does not contain an expiration date, that is yet another
situation.
Surveys are handled differently enough that it may be best to handle those
with a separate report, but even so, there are a lot of variants. Am I
compromising the efficiency of the code by storing the text strings in the
function?

You can still go with the table idea, but then include keywords in the
text that will be replaced by your function, so maybe something like:

"Your ~CertificateType~ will expire on ~ExpirationDate~. Please send
an updated blah, blah."

Then your function would have something to the effect of

strStatusText = Replace(strStatusText, "~CertificateType~",
[CertificateType])
strStatusText = Replace(strStatusText, "~ExpirationDate~",
[ExpirationDate])

etc.

(Or you can get more complex and use Split & Rejoin...depends how much
parsing you expect to have to do.)

 

[Robert Morley]

For someone who couldn't find any documentation, you did a good job of
getting everything right!

You declare and refer to constants exactly as you thought; for public
constants, change the word Private to Public if you want to put it in a
module and have it available everywhere throughout your code. Also, as you
discovered, you can't use either constants or variables in a query, you have
to wrap them in a function.

Did that cover everything?



Rob

 

[Marshall Barton]

I would have to know a lot more details before getting
specific, but I think you could open a recordset filtered to
the letter/category you want and sorted by the section.
Then just loop through the records, using the Replace
function to plug in your values (name,date,etc) and shoving
the mass into a report text box. Sounds simple to me, but
simple is a relative term ;-)
--
Marsh
MVP [MS Access]


BruceM wrote:
[snip]

I think I can almost see a way to make this work, but I will need a
half-dozen or more snippets of text for each condtion (certificate due,
certificate late, etc.), interspersed with fields. The form to make the
text editable (so that an average person without programming knowledge could
revise the text that is to appear on the fax report) seems to me a
worthwhile but rather complex thing. Of course, it may just be that I don't
undertand what you're saying, and that it's not as complex as I imagine it
to be.

I would go with a table that has a field for the certificate
type, another for the paragraph (or whatever parts you can
divide things into), and a third for the text.

The primary consideration is to make the text editable in a
form for the Manager of the Letters without you having to
modify the function's code.

I need ten or so unique text strings. There are some elements in common
between the strings, but they are quite different each from the other. I
am
not having a problem inserting the fields into the text strings. That
part
is fine. In fact, the function works as expected. My question is whether
there is a better place for the text strings than in the function itself.
For instance, maybe there could be a one-record table, with Text fields
(or
Memo fields, if needed) containing different text strings. I could
include
that table in the report's Record Source query, and insert table fields as
needed into the strings in the function. Or something like that.
I see how your suggestion and fredg's suggestions would work if a standard
block of text was used in all cases, but I don't see how it would help
given
that the text strings vary so widely. For instance, if the
CertificateType
is a Survey, those are handled differently than other types of
certificates.
Also, if the certificate expires at this time next year, this year we need
to obtain confirmation that the company is still compliant. If the
certificate does not contain an expiration date, that is yet another
situation.
Surveys are handled differently enough that it may be best to handle those
with a separate report, but even so, there are a lot of variants. Am I
compromising the efficiency of the code by storing the text strings in the
function?

You can still go with the table idea, but then include keywords in the
text that will be replaced by your function, so maybe something like:

"Your ~CertificateType~ will expire on ~ExpirationDate~. Please send
an updated blah, blah."

Then your function would have something to the effect of

strStatusText = Replace(strStatusText, "~CertificateType~",
[CertificateType])
strStatusText = Replace(strStatusText, "~ExpirationDate~",
[ExpirationDate])

etc.

(Or you can get more complex and use Split & Rejoin...depends how much
parsing you expect to have to do.)

 

[BruceM]

It was Access Help that left me stranded, but my real Help files are the
newsgroups. The hard part in both cases can be to find the right
terminology. Google's search capablilties are far above those in Access (or
just about any program, Microsoft or otherwise), plus which people ask
questions using terminology I might use. "User-defined constant" led me to
some postings that steered me in the right direction. Your suggestion in
the first place led me on the search. Sometimes the pieces come together
nicely. Thanks for your assistance.

 

[BruceM]

Somebody else suggested using Replace. I am not very familiar with that
function, but I think it is time to learn more. I will save this thread and
revisit the matter soon. Thanks for your thoughts and suggestions.

I would have to know a lot more details before getting
specific, but I think you could open a recordset filtered to
the letter/category you want and sorted by the section.
Then just loop through the records, using the Replace
function to plug in your values (name,date,etc) and shoving
the mass into a report text box. Sounds simple to me, but
simple is a relative term ;-)
--
Marsh
MVP [MS Access]


BruceM wrote:
[snip]

I think I can almost see a way to make this work, but I will need a
half-dozen or more snippets of text for each condtion (certificate due,
certificate late, etc.), interspersed with fields. The form to make the
text editable (so that an average person without programming knowledge
could
revise the text that is to appear on the fax report) seems to me a
worthwhile but rather complex thing. Of course, it may just be that I
don't
undertand what you're saying, and that it's not as complex as I imagine it
to be.

I would go with a table that has a field for the certificate
type, another for the paragraph (or whatever parts you can
divide things into), and a third for the text.

The primary consideration is to make the text editable in a
form for the Manager of the Letters without you having to
modify the function's code.


BruceM wrote:
I need ten or so unique text strings. There are some elements in common
between the strings, but they are quite different each from the other.
I
am
not having a problem inserting the fields into the text strings. That
part
is fine. In fact, the function works as expected. My question is
whether
there is a better place for the text strings than in the function
itself.
For instance, maybe there could be a one-record table, with Text fields
(or
Memo fields, if needed) containing different text strings. I could
include
that table in the report's Record Source query, and insert table fields
as
needed into the strings in the function. Or something like that.
I see how your suggestion and fredg's suggestions would work if a
standard
block of text was used in all cases, but I don't see how it would help
given
that the text strings vary so widely. For instance, if the
CertificateType
is a Survey, those are handled differently than other types of
certificates.
Also, if the certificate expires at this time next year, this year we
need
to obtain confirmation that the company is still compliant. If the
certificate does not contain an expiration date, that is yet another
situation.
Surveys are handled differently enough that it may be best to handle
those
with a separate report, but even so, there are a lot of variants. Am I
compromising the efficiency of the code by storing the text strings in
the
function?

You can still go with the table idea, but then include keywords in the
text that will be replaced by your function, so maybe something like:

"Your ~CertificateType~ will expire on ~ExpirationDate~. Please
send
an updated blah, blah."

Then your function would have something to the effect of

strStatusText = Replace(strStatusText, "~CertificateType~",
[CertificateType])
strStatusText = Replace(strStatusText, "~ExpirationDate~",
[ExpirationDate])

etc.

(Or you can get more complex and use Split & Rejoin...depends how much
parsing you expect to have to do.)