Convert FoxPro 2.6 Code to MsAccess 2003

[RNUSZ@OKDPS]

Old FoxPro 2.6 Form Letter Code follows:

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registration(s) of (licensee (lic_f_name, lic_m_ini, lic_l_name, lic_subt)),
(if lic_dl_sta="n") shall remain suspended unless (if security > " " ) said
security in the amount of (security) is posted and (eif) proof of current
insurance in your name is filed with this Department along with a $100.00
reinstatement fee in the form of a cashier's check or money order made
payable to the Department of Public Safety.(els) are hereby suspended unless
(if security > " " ) said security in the amount of (security) is posted and
(eif) proof of current insurance in your name is file with this Department
before (revocation date).

There are (4) possible outcomes of the above paragraph.

1). (conditional values of lic_dl_sta = n, and security not 1null) creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registrations of Robert Roberts shall remained suspended unless said security
in the amount of $300.00 is posted and proof of current insurance in your
name is filed with this Department along with a $100.00 reinstatement fee in
the form of a cashier's check or money order made payable to the Department
of Public Safety.

or

2. (conditional values of lic_dl_sta = n, and security is null) creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registrations of Robert Roberts shall remained suspended unless proof of
current insurance in your name is filed with this Department along with a
$100.00 reinstatement fee in the form of a cashier's check or money order
made payable to the Department of Public Safety.

or

3. (conditional values of lic_dl_sta = s, and security not null) creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registrations of Robert Roberts are hereby suspended unless said security in
the amount of $300.00 is posted and proof of current insurance in your name
is filed with this Department before 1 June 2005.

or

4. (conditional values of lic_dl_sta = s, and security is null) creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registrations of Robert Roberts are hereby suspended unless proof of current
insurance in your name is filed with this Department before 1 June 2005.

I need to be able to use this logic in an unbound text box on a report. Any
suggestions, everything that I have tried results with errors like

The expression you entered is missing a closing parenthesis ), bracket }, or
vertical bar. or similar error.

Thanks in advance....

 

[Larry Linson]

Without details, it's difficult or impossible to offer specific suggestions,
and, alas, details of your data layout and structure, and the code that
doesn't work are likely to be too much for a newsgroup exchange.

I suspect I might create an Access function in a standard module, accepting
the conditions as input, and returning the paragraphs you describe. This
might be either as Control Source for a calculated Control on your Report,
or, perhaps even better, a calculated Field in the Query you use as Record
Source for the Report.

Larry Linson
Microsoft Access MVP

 

[RNUSZ@OKDPS]

Larry, Thanks for responding.

This is the code that works on a partial comparison:

=IIf([LIC_DL_STAY_CDE]="N","It is further ordered, pursuant to 47 o.s.
§7-206, that the license and registration(s) of " & [LIC_FIRST_NME]+" " &
[LIC_MIDDLE_NME]+" " & [LIC_LAST_NME] & IIf(IsNull([LIC_SUBT_TXT]),""," " &
[LIC_SUBT_TXT]) & " shall remain suspended unless said security in the amount
of " & FormatCurrency([SECURITY_AMT]) & " is posted and proof of current
insuarnce in your name is filed with this Department along with a $100.00
reinstatement fee in the form of a cashier's check or money order made
payable to the Department of Public Safety."," It is further ordered,
pursuant to 47 o.s. §7-206, that the license and registration(s) of " &
[LIC_FIRST_NME]+" " & [LIC_MIDDLE_NME]+" " & [LIC_LAST_NME] &
IIf(IsNull([LIC_SUBT_TXT]),""," " & [LIC_SUBT_TXT]) & " are hereby suspended
unless said security in the amount of " & FormatCurrency([SECURITY_AMT]) & "
is posted and proof of current insurance in your name is filed with this
Department before " & Format([REVO_DATE],"dd mmmm"", ""yyyy") & ".")

The above statement works to select two conditional outputs based on the
field named [LIC_DL_STAY_CDE] = N or the same field having another value.
The only valid values on this field is either N or S. Value of N in this
field prints the longer statement, value of S prints the shorter statement.
I need to check for a combined value of LIC_DL_STAY_CDE = N and
SECURITY_AMT > 0, print one statement, or LIC_DL_STAY_CDE = N and
SECURITY_AMT < 0, print another statement, or
LIC_DL_STAY_CDE = S and SECURITY_AMT > 0, print another statement or
LIC_DL_STAY_CDE = S and SECURITY_AMT < 0, print final statement.

Hope this helps.....

Thanks, Robert

Without details, it's difficult or impossible to offer specific suggestions,
and, alas, details of your data layout and structure, and the code that
doesn't work are likely to be too much for a newsgroup exchange.

I suspect I might create an Access function in a standard module, accepting
the conditions as input, and returning the paragraphs you describe. This
might be either as Control Source for a calculated Control on your Report,
or, perhaps even better, a calculated Field in the Query you use as Record
Source for the Report.

Larry Linson
Microsoft Access MVP

 

[Larry Linson]

The length of that expression is why I suggested a user-defined-function --
it can be structured and indented to be far more readable and more readable
implies more easily debuggable.

It can also be called from the Immediate Window with sample inputs, also
expediting debugging.

Larry Linson
Microsoft Access MVP

Larry, Thanks for responding.

This is the code that works on a partial comparison:

=IIf([LIC_DL_STAY_CDE]="N","It is further ordered, pursuant to 47 o.s.
§7-206, that the license and registration(s) of " & [LIC_FIRST_NME]+" " &
[LIC_MIDDLE_NME]+" " & [LIC_LAST_NME] & IIf(IsNull([LIC_SUBT_TXT]),""," " &
[LIC_SUBT_TXT]) & " shall remain suspended unless said security in the amount
of " & FormatCurrency([SECURITY_AMT]) & " is posted and proof of current
insuarnce in your name is filed with this Department along with a $100.00
reinstatement fee in the form of a cashier's check or money order made
payable to the Department of Public Safety."," It is further ordered,
pursuant to 47 o.s. §7-206, that the license and registration(s) of " &
[LIC_FIRST_NME]+" " & [LIC_MIDDLE_NME]+" " & [LIC_LAST_NME] &
IIf(IsNull([LIC_SUBT_TXT]),""," " & [LIC_SUBT_TXT]) & " are hereby suspended
unless said security in the amount of " & FormatCurrency([SECURITY_AMT]) & "
is posted and proof of current insurance in your name is filed with this
Department before " & Format([REVO_DATE],"dd mmmm"", ""yyyy") & ".")

The above statement works to select two conditional outputs based on the
field named [LIC_DL_STAY_CDE] = N or the same field having another value.
The only valid values on this field is either N or S. Value of N in this
field prints the longer statement, value of S prints the shorter statement.
I need to check for a combined value of LIC_DL_STAY_CDE = N and
SECURITY_AMT > 0, print one statement, or LIC_DL_STAY_CDE = N and
SECURITY_AMT < 0, print another statement, or
LIC_DL_STAY_CDE = S and SECURITY_AMT > 0, print another statement or
LIC_DL_STAY_CDE = S and SECURITY_AMT < 0, print final statement.

Hope this helps.....

Thanks, Robert

 

[RNUSZ@OKDPS]

I was unaware that Microsoft Access would have text manipulating functions.
Is there a resource for text functions that I could find on the internet. My
project is near the end and already past its expected finish date, so finding
an example of coding for text manipulating functions would allow me to finish
up quicker. I appreciate your response as well as the others whom have
assisted via this site. I've learned a lot in the past 6 months, this is
just another learning curve ball being thrown...

Thanks

Robert Nusz

The length of that expression is why I suggested a user-defined-function --
it can be structured and indented to be far more readable and more readable
implies more easily debuggable.

It can also be called from the Immediate Window with sample inputs, also
expediting debugging.

Larry Linson
Microsoft Access MVP

Larry, Thanks for responding.

This is the code that works on a partial comparison:

=IIf([LIC_DL_STAY_CDE]="N","It is further ordered, pursuant to 47 o.s.
§7-206, that the license and registration(s) of " & [LIC_FIRST_NME]+" " &
[LIC_MIDDLE_NME]+" " & [LIC_LAST_NME] & IIf(IsNull([LIC_SUBT_TXT]),""," " &
[LIC_SUBT_TXT]) & " shall remain suspended unless said security in the amount
of " & FormatCurrency([SECURITY_AMT]) & " is posted and proof of current
insuarnce in your name is filed with this Department along with a $100.00
reinstatement fee in the form of a cashier's check or money order made
payable to the Department of Public Safety."," It is further ordered,
pursuant to 47 o.s. §7-206, that the license and registration(s) of " &
[LIC_FIRST_NME]+" " & [LIC_MIDDLE_NME]+" " & [LIC_LAST_NME] &
IIf(IsNull([LIC_SUBT_TXT]),""," " & [LIC_SUBT_TXT]) & " are hereby suspended
unless said security in the amount of " & FormatCurrency([SECURITY_AMT]) & "
is posted and proof of current insurance in your name is filed with this
Department before " & Format([REVO_DATE],"dd mmmm"", ""yyyy") & ".")

The above statement works to select two conditional outputs based on the
field named [LIC_DL_STAY_CDE] = N or the same field having another value.
The only valid values on this field is either N or S. Value of N in this
field prints the longer statement, value of S prints the shorter statement.
I need to check for a combined value of LIC_DL_STAY_CDE = N and
SECURITY_AMT > 0, print one statement, or LIC_DL_STAY_CDE = N and
SECURITY_AMT < 0, print another statement, or
LIC_DL_STAY_CDE = S and SECURITY_AMT > 0, print another statement or
LIC_DL_STAY_CDE = S and SECURITY_AMT < 0, print final statement.

Hope this helps.....

Thanks, Robert

Without details, it's difficult or impossible to offer specific suggestions,
and, alas, details of your data layout and structure, and the code that
doesn't work are likely to be too much for a newsgroup exchange.

I suspect I might create an Access function in a standard module, accepting
the conditions as input, and returning the paragraphs you describe. This
might be either as Control Source for a calculated Control on your Report,
or, perhaps even better, a calculated Field in the Query you use as Record
Source for the Report.

Larry Linson
Microsoft Access MVP


Old FoxPro 2.6 Form Letter Code follows:

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registration(s) of (licensee (lic_f_name, lic_m_ini, lic_l_name,
lic_subt)),
(if lic_dl_sta="n") shall remain suspended unless (if security > " " )
said
security in the amount of (security) is posted and (eif) proof of current
insurance in your name is filed with this Department along with a $100.00
reinstatement fee in the form of a cashier's check or money order made
payable to the Department of Public Safety.(els) are hereby suspended
unless
(if security > " " ) said security in the amount of (security) is posted
and
(eif) proof of current insurance in your name is file with this Department
before (revocation date).

There are (4) possible outcomes of the above paragraph.

1). (conditional values of lic_dl_sta = n, and security not 1null)
creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registrations of Robert Roberts shall remained suspended unless said
security
in the amount of $300.00 is posted and proof of current insurance in your
name is filed with this Department along with a $100.00 reinstatement fee
in
the form of a cashier's check or money order made payable to the
Department
of Public Safety.

or

2. (conditional values of lic_dl_sta = n, and security is null) creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registrations of Robert Roberts shall remained suspended unless proof of
current insurance in your name is filed with this Department along with a
$100.00 reinstatement fee in the form of a cashier's check or money order
made payable to the Department of Public Safety.

or

3. (conditional values of lic_dl_sta = s, and security not null) creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registrations of Robert Roberts are hereby suspended unless said security
in
the amount of $300.00 is posted and proof of current insurance in your
name
is filed with this Department before 1 June 2005.

or

4. (conditional values of lic_dl_sta = s, and security is null) creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registrations of Robert Roberts are hereby suspended unless proof of
current
insurance in your name is filed with this Department before 1 June 2005.

I need to be able to use this logic in an unbound text box on a report.
Any
suggestions, everything that I have tried results with errors like

The expression you entered is missing a closing parenthesis ), bracket },
or
vertical bar. or similar error.

Thanks in advance....

 

[RNUSZ@OKDPS]

Larry,

I've attempted to write the VB code below and stored it in a module in the
database called FRC16FU1, code follows:

Option Compare Database

Function FRC16FU1(pstrFRC16FU1 As String, _
Optional pstrDelim As String = ", ", _
Optional pstrDelim2 As String = " - ") _
As String
Dim strprt1, strprt2, strprt3, strprt4, _
strprt5, strprt6, strprt7, strprt8, _
strprt9, strprt10 As String
Dim strLicensee As String ' build returned Licensee Name
Dim strSecurity As Integer ' build returned Security_Amt
Dim paraType As Integer
Dim strRevoDate As Integer ' build return Revo_Date
Dim strConcat As String ' build return string
' & Format([HRG_DATE],"dd mmmm"", ""yyyy") & "
strSecurity = FormatCurrency([SECURITY_AMT]) 'format SECURITY_AMT
strRevoDate = Format([REVO_DATE], "dd mmmm"", ""yyyy") ' load
record REVO_DATE for stringed value
strLicensee = [LIC_FIRST_NME] & " " & [LIC_MIDDLE_NME] & " " &
[LIC_LAST_NME] & IIf(IsNull([LIC_SUBT_TXT]), "", " " & [LIC_SUBT_TXT]) & ""
MsgBox ' strLicensee = ' & strLicensee

strprt1 = " It is further ordered, pursuant to 47 o.s. §7-206, "
strprt2 = " that the license and registration(s) of "
strprt3 = " shall remain suspended unless said security in the
amount of "
strprt4 = " is posted and proof of current insurance in your name is
filed "
strprt5 = " with this Department along with a $100.00 resinstatement
fee in the "
strprt6 = " form of a cashier's check or money order made payable to
the "
strprt7 = " Department of Public Safety."
strprt8 = " are hereby suspended unless said security in the amount
of "
strprt9 = " is posted and "
strprt10 = " proof of current insurance in your name is filed with
this Department before "

If LIC_DL_STAY_CDE = "N" And SECURITY_AMT > 0 Then
paraType = 1
Else
If LIC_DL_STAY_CDE = "N" And SECURITY_AMT < 0 Then
paraType = 2
Else
If LIC_DL_STAY_CDE = "S" And SECURITY_AMT > 0 Then
paraType = 3
Else
paraType = 4
End If

Select Case paraType
Case 1 ' LIC_DL_STAY_CDE = N & SECURITY_AMT is Not Null
strConcat = strprt1 & _
strprt2 & _
strLicensee & _
strprt3 & _
strSecurity & _
strprt4 & _
strprt5 & _
strprt6 & _
strprt7
Case 2 ' LIC_DL_STAY_CDE = N & SECURITY_AMT is Null
strConcat = strprt1 & _
strprt2 & _
strLicensee & _
strprt3 & _
strSecurity & _
strprt4 & _
strprt5 & _
strprt6 & _
strprt7
Case 3 ' LIC_DL_STAY_CDE = S & SECURITY_AMT is Not Null
strConcat = strprt1 & _
strprt2 & _
strLicensee & _
strprt8 & _
strSecurity & _
strprt9 & _
strprt10
Case 4 ' LIC_DL_STAY_CDE = S & SECURITY_AMT is Null
strConcat = strprt1 & _
strprt2 & _
strLicensee & _
strprt8 & _
strprt10
End Select

FRC16FU1.Text = strConcat

End Function

I was hoping to concatentate multiple strings to form the paragraph, after
extraction of record fields to format the string for strLicensee and to check
the values of LIC_DL_STAY_CDE for either an N or an S value, changing the
printed statement based upon these values and the value of the integer field
SECURITY_AMT being null or having a value. REVO_DATE is a numerical date
field, that is being reformated to day, month, year. SECURITY_AMT needs to
be formated to $9,999.99. and attempted to use SELECT & CASE for final
results.

Can this code be reviewed, and determined if there is a better way or if
this would work, then, my next question is. How do I get this unbound text
field on a report to utilize the function for the results. I changed the
control source property of this field to =[FRC16FU1] but when you attempt to
open the report, it prompts you for a value for FRC16FU1, if you press enter
to bypass request, it opens form in view form, but the area for this needed
paragraph is blank.

Any suggestions, other than give up my day job..... I am trying to
understand VB functions and it use with Access...

Any thoughts would be greatly appreciated.


Thanks in advance..

Robert Nusz

The length of that expression is why I suggested a user-defined-function --
it can be structured and indented to be far more readable and more readable
implies more easily debuggable.

It can also be called from the Immediate Window with sample inputs, also
expediting debugging.

Larry Linson
Microsoft Access MVP

Larry, Thanks for responding.

This is the code that works on a partial comparison:

=IIf([LIC_DL_STAY_CDE]="N","It is further ordered, pursuant to 47 o.s.
§7-206, that the license and registration(s) of " & [LIC_FIRST_NME]+" " &
[LIC_MIDDLE_NME]+" " & [LIC_LAST_NME] & IIf(IsNull([LIC_SUBT_TXT]),""," " &
[LIC_SUBT_TXT]) & " shall remain suspended unless said security in the amount
of " & FormatCurrency([SECURITY_AMT]) & " is posted and proof of current
insuarnce in your name is filed with this Department along with a $100.00
reinstatement fee in the form of a cashier's check or money order made
payable to the Department of Public Safety."," It is further ordered,
pursuant to 47 o.s. §7-206, that the license and registration(s) of " &
[LIC_FIRST_NME]+" " & [LIC_MIDDLE_NME]+" " & [LIC_LAST_NME] &
IIf(IsNull([LIC_SUBT_TXT]),""," " & [LIC_SUBT_TXT]) & " are hereby suspended
unless said security in the amount of " & FormatCurrency([SECURITY_AMT]) & "
is posted and proof of current insurance in your name is filed with this
Department before " & Format([REVO_DATE],"dd mmmm"", ""yyyy") & ".")

The above statement works to select two conditional outputs based on the
field named [LIC_DL_STAY_CDE] = N or the same field having another value.
The only valid values on this field is either N or S. Value of N in this
field prints the longer statement, value of S prints the shorter statement.
I need to check for a combined value of LIC_DL_STAY_CDE = N and
SECURITY_AMT > 0, print one statement, or LIC_DL_STAY_CDE = N and
SECURITY_AMT < 0, print another statement, or
LIC_DL_STAY_CDE = S and SECURITY_AMT > 0, print another statement or
LIC_DL_STAY_CDE = S and SECURITY_AMT < 0, print final statement.

Hope this helps.....

Thanks, Robert

Without details, it's difficult or impossible to offer specific suggestions,
and, alas, details of your data layout and structure, and the code that
doesn't work are likely to be too much for a newsgroup exchange.

I suspect I might create an Access function in a standard module, accepting
the conditions as input, and returning the paragraphs you describe. This
might be either as Control Source for a calculated Control on your Report,
or, perhaps even better, a calculated Field in the Query you use as Record
Source for the Report.

Larry Linson
Microsoft Access MVP


Old FoxPro 2.6 Form Letter Code follows:

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registration(s) of (licensee (lic_f_name, lic_m_ini, lic_l_name,
lic_subt)),
(if lic_dl_sta="n") shall remain suspended unless (if security > " " )
said
security in the amount of (security) is posted and (eif) proof of current
insurance in your name is filed with this Department along with a $100.00
reinstatement fee in the form of a cashier's check or money order made
payable to the Department of Public Safety.(els) are hereby suspended
unless
(if security > " " ) said security in the amount of (security) is posted
and
(eif) proof of current insurance in your name is file with this Department
before (revocation date).

There are (4) possible outcomes of the above paragraph.

1). (conditional values of lic_dl_sta = n, and security not 1null)
creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registrations of Robert Roberts shall remained suspended unless said
security
in the amount of $300.00 is posted and proof of current insurance in your
name is filed with this Department along with a $100.00 reinstatement fee
in
the form of a cashier's check or money order made payable to the
Department
of Public Safety.

or

2. (conditional values of lic_dl_sta = n, and security is null) creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registrations of Robert Roberts shall remained suspended unless proof of
current insurance in your name is filed with this Department along with a
$100.00 reinstatement fee in the form of a cashier's check or money order
made payable to the Department of Public Safety.

or

3. (conditional values of lic_dl_sta = s, and security not null) creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registrations of Robert Roberts are hereby suspended unless said security
in
the amount of $300.00 is posted and proof of current insurance in your
name
is filed with this Department before 1 June 2005.

or

4. (conditional values of lic_dl_sta = s, and security is null) creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license and
registrations of Robert Roberts are hereby suspended unless proof of
current
insurance in your name is filed with this Department before 1 June 2005.

I need to be able to use this logic in an unbound text box on a report.
Any
suggestions, everything that I have tried results with errors like

The expression you entered is missing a closing parenthesis ), bracket },
or
vertical bar. or similar error.

Thanks in advance....

 

[SA]

Robert:

Dim strprt1, strprt2, strprt3, strprt4, _

strprt5, strprt6, strprt7, strprt8, _
strprt9, strprt10 As String

will result in strprt1 and strprt2 etc being dimensions as variants. If you
want them all as strings you've got to be explicit e.g.

Dim strprt1 as string, strprt2 as string etc.

In your code's case this isn't a problem because var and strings use the
same value and it all works, but in other code this might cause you an
issue.

To get the result into the text box, as you've found you can't set the
control source = to the name of the function. If you haven't found the
answer by now, what you do is that within the On Print event of the report
section where you want this text to display, you add code like this:

Me.MyTextBoxName.Value = FRC16FU1(....)

Make sure the text box has not control source set (i.e. its unbound)
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Larry,

I've attempted to write the VB code below and stored it in a module in the
database called FRC16FU1, code follows:

Option Compare Database

Function FRC16FU1(pstrFRC16FU1 As String, _
Optional pstrDelim As String = ", ", _
Optional pstrDelim2 As String = " - ") _
As String
Dim strprt1, strprt2, strprt3, strprt4, _
strprt5, strprt6, strprt7, strprt8, _
strprt9, strprt10 As String
Dim strLicensee As String ' build returned Licensee Name
Dim strSecurity As Integer ' build returned Security_Amt
Dim paraType As Integer
Dim strRevoDate As Integer ' build return Revo_Date
Dim strConcat As String ' build return string
' & Format([HRG_DATE],"dd mmmm"", ""yyyy") & "
strSecurity = FormatCurrency([SECURITY_AMT]) 'format
SECURITY_AMT
strRevoDate = Format([REVO_DATE], "dd mmmm"", ""yyyy") '
load
record REVO_DATE for stringed value
strLicensee = [LIC_FIRST_NME] & " " & [LIC_MIDDLE_NME] & " " &
[LIC_LAST_NME] & IIf(IsNull([LIC_SUBT_TXT]), "", " " & [LIC_SUBT_TXT]) &
""
MsgBox ' strLicensee = ' & strLicensee

strprt1 = " It is further ordered, pursuant to 47 o.s. §7-206,
"
strprt2 = " that the license and registration(s) of "
strprt3 = " shall remain suspended unless said security in the
amount of "
strprt4 = " is posted and proof of current insurance in your name
is
filed "
strprt5 = " with this Department along with a $100.00
resinstatement
fee in the "
strprt6 = " form of a cashier's check or money order made payable
to
the "
strprt7 = " Department of Public Safety."
strprt8 = " are hereby suspended unless said security in the amount
of "
strprt9 = " is posted and "
strprt10 = " proof of current insurance in your name is filed with
this Department before "

If LIC_DL_STAY_CDE = "N" And SECURITY_AMT > 0 Then
paraType = 1
Else
If LIC_DL_STAY_CDE = "N" And SECURITY_AMT < 0 Then
paraType = 2
Else
If LIC_DL_STAY_CDE = "S" And SECURITY_AMT > 0 Then
paraType = 3
Else
paraType = 4
End If

Select Case paraType
Case 1 ' LIC_DL_STAY_CDE = N & SECURITY_AMT is Not
Null
strConcat = strprt1 & _
strprt2 & _
strLicensee & _
strprt3 & _
strSecurity & _
strprt4 & _
strprt5 & _
strprt6 & _
strprt7
Case 2 ' LIC_DL_STAY_CDE = N & SECURITY_AMT is Null
strConcat = strprt1 & _
strprt2 & _
strLicensee & _
strprt3 & _
strSecurity & _
strprt4 & _
strprt5 & _
strprt6 & _
strprt7
Case 3 ' LIC_DL_STAY_CDE = S & SECURITY_AMT is Not
Null
strConcat = strprt1 & _
strprt2 & _
strLicensee & _
strprt8 & _
strSecurity & _
strprt9 & _
strprt10
Case 4 ' LIC_DL_STAY_CDE = S & SECURITY_AMT is Null
strConcat = strprt1 & _
strprt2 & _
strLicensee & _
strprt8 & _
strprt10
End Select

FRC16FU1.Text = strConcat

End Function

I was hoping to concatentate multiple strings to form the paragraph, after
extraction of record fields to format the string for strLicensee and to
check
the values of LIC_DL_STAY_CDE for either an N or an S value, changing the
printed statement based upon these values and the value of the integer
field
SECURITY_AMT being null or having a value. REVO_DATE is a numerical date
field, that is being reformated to day, month, year. SECURITY_AMT needs
to
be formated to $9,999.99. and attempted to use SELECT & CASE for final
results.

Can this code be reviewed, and determined if there is a better way or if
this would work, then, my next question is. How do I get this unbound
text
field on a report to utilize the function for the results. I changed the
control source property of this field to =[FRC16FU1] but when you attempt
to
open the report, it prompts you for a value for FRC16FU1, if you press
enter
to bypass request, it opens form in view form, but the area for this
needed
paragraph is blank.

Any suggestions, other than give up my day job..... I am trying to
understand VB functions and it use with Access...

Any thoughts would be greatly appreciated.


Thanks in advance..

Robert Nusz

The length of that expression is why I suggested a
user-defined-function --
it can be structured and indented to be far more readable and more
readable
implies more easily debuggable.

It can also be called from the Immediate Window with sample inputs, also
expediting debugging.

Larry Linson
Microsoft Access MVP

Larry, Thanks for responding.

This is the code that works on a partial comparison:

=IIf([LIC_DL_STAY_CDE]="N","It is further ordered, pursuant to 47 o.s.
§7-206, that the license and registration(s) of " & [LIC_FIRST_NME]+" "
&
[LIC_MIDDLE_NME]+" " & [LIC_LAST_NME] & IIf(IsNull([LIC_SUBT_TXT]),"","
" &
[LIC_SUBT_TXT]) & " shall remain suspended unless said security in the amount
of " & FormatCurrency([SECURITY_AMT]) & " is posted and proof of
current
insuarnce in your name is filed with this Department along with a
$100.00
reinstatement fee in the form of a cashier's check or money order made
payable to the Department of Public Safety."," It is further ordered,
pursuant to 47 o.s. §7-206, that the license and registration(s) of " &
[LIC_FIRST_NME]+" " & [LIC_MIDDLE_NME]+" " & [LIC_LAST_NME] &
IIf(IsNull([LIC_SUBT_TXT]),""," " & [LIC_SUBT_TXT]) & " are hereby suspended
unless said security in the amount of " &
FormatCurrency([SECURITY_AMT]) & "
is posted and proof of current insurance in your name is filed with
this
Department before " & Format([REVO_DATE],"dd mmmm"", ""yyyy") & ".")

The above statement works to select two conditional outputs based on
the
field named [LIC_DL_STAY_CDE] = N or the same field having another
value.
The only valid values on this field is either N or S. Value of N in
this
field prints the longer statement, value of S prints the shorter statement.
I need to check for a combined value of LIC_DL_STAY_CDE = N and
SECURITY_AMT > 0, print one statement, or LIC_DL_STAY_CDE = N and
SECURITY_AMT < 0, print another statement, or
LIC_DL_STAY_CDE = S and SECURITY_AMT > 0, print another statement or
LIC_DL_STAY_CDE = S and SECURITY_AMT < 0, print final statement.

Hope this helps.....

Thanks, Robert
:

Without details, it's difficult or impossible to offer specific suggestions,
and, alas, details of your data layout and structure, and the code
that
doesn't work are likely to be too much for a newsgroup exchange.

I suspect I might create an Access function in a standard module, accepting
the conditions as input, and returning the paragraphs you describe.
This
might be either as Control Source for a calculated Control on your Report,
or, perhaps even better, a calculated Field in the Query you use as Record
Source for the Report.

Larry Linson
Microsoft Access MVP


Old FoxPro 2.6 Form Letter Code follows:

It is further ordered, pursuant to 47 o.s. 7-206, that the license
and
registration(s) of (licensee (lic_f_name, lic_m_ini, lic_l_name,
lic_subt)),
(if lic_dl_sta="n") shall remain suspended unless (if security > "
" )
said
security in the amount of (security) is posted and (eif) proof of current
insurance in your name is filed with this Department along with a $100.00
reinstatement fee in the form of a cashier's check or money order
made
payable to the Department of Public Safety.(els) are hereby
suspended
unless
(if security > " " ) said security in the amount of (security) is posted
and
(eif) proof of current insurance in your name is file with this Department
before (revocation date).

There are (4) possible outcomes of the above paragraph.

1). (conditional values of lic_dl_sta = n, and security not
1null)
creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license
and
registrations of Robert Roberts shall remained suspended unless
said
security
in the amount of $300.00 is posted and proof of current insurance
in your
name is filed with this Department along with a $100.00
reinstatement fee
in
the form of a cashier's check or money order made payable to the
Department
of Public Safety.

or

2. (conditional values of lic_dl_sta = n, and security is null) creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license
and
registrations of Robert Roberts shall remained suspended unless
proof of
current insurance in your name is filed with this Department along with a
$100.00 reinstatement fee in the form of a cashier's check or money order
made payable to the Department of Public Safety.

or

3. (conditional values of lic_dl_sta = s, and security not null) creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license
and
registrations of Robert Roberts are hereby suspended unless said security
in
the amount of $300.00 is posted and proof of current insurance in your
name
is filed with this Department before 1 June 2005.

or

4. (conditional values of lic_dl_sta = s, and security is null) creates

It is further ordered, pursuant to 47 o.s. 7-206, that the license
and
registrations of Robert Roberts are hereby suspended unless proof
of
current
insurance in your name is filed with this Department before 1 June 2005.

I need to be able to use this logic in an unbound text box on a report.
Any
suggestions, everything that I have tried results with errors like

The expression you entered is missing a closing parenthesis ),
bracket },
or
vertical bar. or similar error.

Thanks in advance....